This is actually a bug https://mariadb.atlassian.net/browse/MDEV-5719.
Pavel On Fri, May 23, 2014 at 11:18 AM, Charles Cazabon <charlesc-web-register-launchpad....@pyropus.ca> wrote: > 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....@pyropus.ca> > Software, consulting, and services available at http://pyropus.ca/ > ------------------------------------------------------------------ > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : maria-discuss@lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
