http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html
here, but must check if mariadb have something like it 2014-05-23 15:21 GMT-03:00 Roberto Spadim <[email protected]>: > maybe you should use something like MIN() MAX(), since you are using a > GROUP BY > i don't know if this is well documented but i think it's > > > 2014-05-23 15:18 GMT-03:00 Charles Cazabon < > [email protected]>: > > 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 >> > > > > -- > Roberto Spadim > SPAEmpresarial > Eng. Automação e Controle > -- 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
