maria-discuss team mailing list archive

[Charles Cazabon <charlesc-web-register-launchpad.net@xxxxxxxxxx>]

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/
------------------------------------------------------------------