maria-discuss team mailing list archive

[Pavel Ivanov <pivanof@xxxxxxxxxx>]

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.net@xxxxxxxxxx> 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.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