maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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 <roberto@xxxxxxxxxxxxx>:

> 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 <
> 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/
>> ------------------------------------------------------------------
>>
>> _______________________________________________
>> 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
>>
>
>
>
> --
> Roberto Spadim
> SPAEmpresarial
> Eng. Automação e Controle
>



-- 
Roberto Spadim
SPAEmpresarial
Eng. Automação e Controle