maria-discuss team mailing list archive

[Daniel Bartholomew <dbart@xxxxxxxxxxxxxxxx>]

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.

-- 
Daniel Bartholomew
  Google+ - http://gplus.to/dbart
  Twitter - http://twitter.com/daniel_bart

MariaDB: An Enhanced Drop-in Replacement for MySQL
        Website - http://mariadb.org
        Twitter - http://twitter.com/mariadb
        Google+ - http://gplus.to/mariadb
       Facebook - http://fb.com/MariaDB.dbms
  Knowledgebase - http://kb.askmonty.org
  Monty Program - http://montyprogram.com