maria-discuss team mailing list archive

[Federico Razzoli <federico_raz@xxxxxxxx>]

Hi!
>From MySQL documentation:
http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html

"MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. (...) However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."

This is the reason why I think that the ONLY_FULL_GROUP_BY sql_mode flag should always be on.

In your specific case, I note that album_id is not in GROUP BY and is not passed to any aggregating function (such as MAX()).

Regards
Federico


--------------------------------------------
El vie, 23/5/14, Charles Cazabon <charlesc-web-register-launchpad.net@xxxxxxxxxx> escribió:

 Asunto: [Maria-discuss] query result inconsistency between MariaDB 10.0.x and Oracle MySQL 5.1.x
 Para: "MariaDB discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
 Fecha: viernes, 23 de mayo, 2014 20:18
 
 Greetings,
 
 I recently upgraded the db server behind an application from
 MySQL 5.1.73 (as
 shipped in Ubuntu 10.04 "Lucid") to MariaDB 10.0.11 (from
 the MariaDB repo).
 
 A colleague of mine found an inconsistency between the
 results produced by the
 two servers for a given query.  What we don't know is,
 is this a bug (I gather
 Maria is aiming at 100% compatibility), or is this somehow
 due to the query
 relying on unspecified behaviour (that the two db servers
 are therefore free
 to optimize differently)?
 
 The query is:
 
   SELECT t1.id, t2.album_id
   FROM t1
     LEFT OUTER JOIN t2
       ON t1.data_id = t2.id
       AND t1.event_type IN (1002, 1001,
 1000)
   WHERE
     t1.event_type IN (1000, 1001, 1002, 1200,
 1201, 1202, 1203)
   GROUP BY t1.id
   ORDER BY t1.id DESC
   LIMIT 0, 20;
 
 The MariaDB result looks like this:
 
   +-----+----------+
   | id  | album_id |
   +-----+----------+
   | 623 |     NULL |
   | 622 |     NULL |
   | 621 |     NULL |
   | 620 |     NULL |
   | 619 |     NULL |
   | 618 |     NULL |
   | 617 |     NULL |
   | 616 |     NULL |
   | 615 |     NULL |
   | 614 |     NULL |
   | 613 |     NULL |
   | 612 |      194 |
   | 611 |     NULL |
   | 610 |     NULL |
   | 609 |     NULL |
   | 608 |      193 |
   | 607 |     NULL |
   | 606 |     NULL |
   | 605 |     NULL |
   | 604 |     NULL |
   +-----+----------+
 
 And the Oracle MySQL result looks like this:
 
   +-----+----------+
   | id  | album_id |
   +-----+----------+
   | 623 |     NULL |
   | 622 |     NULL |
   | 621 |     NULL |
   | 620 |     NULL |
   | 619 |     NULL |
   | 618 |     NULL |
   | 617 |     NULL |
   | 616 |      196 |<-- different
   | 615 |     NULL |
   | 614 |     NULL |
   | 613 |     NULL |
   | 612 |      194 |
   | 611 |      194 |<-- different
   | 610 |     NULL |
   | 609 |     NULL |
   | 608 |      193 |
   | 607 |      193 |<-- different
   | 606 |     NULL |
   | 605 |     NULL |
   | 604 |     NULL |
   +-----+----------+
 
 My colleague pointed out that if you EXPLAIN the queries,
 you can see that the
 two databases are interpreting the query differently -- see
 the "Extra"
 column.  I can't paste the explain output here without
 using very long lines,
 so I've pastebinned it:
 http://pastebin.com/n2sbH0kY
 
 My colleague has made the data from these tables available
 here:
 https://dl.dropboxusercontent.com/u/7755033/fatdrop/test_case_data.sql
 
 We've found workarounds for this, but we're really wondering
 if we've found a
 problem (either in MariaDB-MySQL consistency, or in the
 query, or ... ?).
 
 Any assistance appreciated.
 
 Charles
 -- 
 ------------------------------------------------------------------
 Charles Cazabon       <charlesc-web-register-launchpad.net@xxxxxxxxxx>
 Software, consulting, and services available at http://pyropus.ca/
 ------------------------------------------------------------------
 
 _______________________________________________
 Mailing list: https://launchpad.net/~maria-discuss
 Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
 Unsubscribe : https://launchpad.net/~maria-discuss
 More help   : https://help.launchpad.net/ListHelp