The following question has been posted to the Knowledgebase:

https://kb.askmonty.org/en/group-by-trick-has-been-optimized-away/

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - GROUP BY trick has been optimized away

Group-by trick example: Find the most populous city in each state:

SELECT state, city, population, COUNT(*) AS num_cities FROM ( SELECT state, city, population FROM us ORDER BY state, population DESC ) p GROUP BY state ORDER BY state; +-------+-------------+------------+------------+ | state | city | population | num_cities | +-------+-------------+------------+------------+ | AK | Anchorage | 276263 | 16 | | AL | Birmingham | 231621 | 58 | | AR | Little Rock | 184217 | 40 | | AZ | Phoenix | 1428509 | 51 | | CA | Los Angeles | 3877129 | 447 | ... That was the output in MySQL 5.1. But with MariaDB 5.5.23, I get: +-------+-------------------+------------+------------+ | state | city | population | num_cities | +-------+-------------------+------------+------------+ | AK | Anchorage | 276263 | 16 | | AL | Alabaster | 26738 | 58 | | AR | Arkadelphia | 11062 | 40 | | AZ | Apache Junction | 34904 | 51 | | CA | Adelanto | 21955 | 447 | ... The EXPLAIN plan do longer shows a subquery, as if the inner ORDER BY has been thrown away.

Granted, there is nothing in the definition of MySQL (much less in the SQL standard) that requires that Los Angeles should be bigger than Adelanto. But the replacement code for this 'trick' is quite messy.

Am I correct in deducing (from the outside, looking in) that MariaDB's optimizations are the cause of the change? - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Thanks.