i think it's a bug since sergei commented that some test cases don't pass i don't think it's a bug, since it's well documented that this kind of column used with group by could not be deterministic
but as a work around try to use MIN() MAX() and check if the query 'become' deterministic and solve the problem, while developers check what should/shouldn't be done, solve a bug, or not solve 2014-05-23 16:50 GMT-03:00 Pantelis Theodosiou <[email protected]>: > Roberto, Federico, > > While I agree with your comments regarding use of ONLY_FULL_GROUP_BY, the > specific case has primary keys defined on (id), on both tables. > > So, the GROUP BY t1.id has (or should have) no effect as every group will > have exactly one row and the results should be the deterministic. I think > Pavel is correct and this is a bug. > > Pantelis > > > On Fri, May 23, 2014 at 8:16 PM, Federico Razzoli > <[email protected]>wrote: > >> 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 < >> [email protected]> escribió: >> >> Asunto: [Maria-discuss] query result inconsistency between MariaDB >> 10.0.x and Oracle MySQL 5.1.x >> Para: "MariaDB discuss" <[email protected]> >> 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 <[email protected]> >> Software, consulting, and services available at http://pyropus.ca/ >> ------------------------------------------------------------------ >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-discuss >> Post to : [email protected] >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp >> >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~maria-discuss >> Post to : [email protected] >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp >> > > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : [email protected] > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp > > -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
