maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

from mariadb
https://mariadb.com/kb/en/select/

You can use any of the grouping functions in your select expression. Their
values will be calculated based on all the rows that have been grouped
together for each result row. *If you select a non-grouped column or a
value computed from a non-grouped column, it is undefined which row the
returned value is taken from. *This is not permitted if the
ONLY_FULL_GROUP_BY SQL_MODE <https://mariadb.com/kb/en/sql_mode/> is used.



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



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