maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

orry many mails

" The server is free to choose any value from each group, so unless they
are the same, the values chosen are indeterminate. "


2014-05-23 15:32 GMT-03:00 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
>



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