maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #01611
Re: query result inconsistency between MariaDB 10.0.x and Oracle MySQL 5.1.x
i think it's a bug since sergei commented that some test cases don't pass
i don't think it's a bug, since it's well documented that this kind of
column used with group by could not be deterministic
but as a work around try to use MIN() MAX() and check if the query
'become' deterministic and solve the problem, while developers check what
should/shouldn't be done, solve a bug, or not solve
2014-05-23 16:50 GMT-03:00 Pantelis Theodosiou <ypercube@xxxxxxxxx>:
> Roberto, Federico,
>
> While I agree with your comments regarding use of ONLY_FULL_GROUP_BY, the
> specific case has primary keys defined on (id), on both tables.
>
> So, the GROUP BY t1.id has (or should have) no effect as every group will
> have exactly one row and the results should be the deterministic. I think
> Pavel is correct and this is a bug.
>
> Pantelis
>
>
> On Fri, May 23, 2014 at 8:16 PM, Federico Razzoli <federico_raz@xxxxxxxx>wrote:
>
>> Hi!
>> From MySQL documentation:
>> http://dev.mysql.com/doc/refman/5.6/en/group-by-extensions.html
>>
>> "MySQL extends the use of GROUP BY so that the select list can refer to
>> nonaggregated columns not named in the GROUP BY clause. (...) However, this
>> is useful primarily when all values in each nonaggregated column not named
>> in the GROUP BY are the same for each group. The server is free to choose
>> any value from each group, so unless they are the same, the values chosen
>> are indeterminate."
>>
>> This is the reason why I think that the ONLY_FULL_GROUP_BY sql_mode flag
>> should always be on.
>>
>> In your specific case, I note that album_id is not in GROUP BY and is not
>> passed to any aggregating function (such as MAX()).
>>
>> Regards
>> Federico
>>
>>
>> --------------------------------------------
>> El vie, 23/5/14, Charles Cazabon <
>> charlesc-web-register-launchpad.net@xxxxxxxxxx> escribió:
>>
>> Asunto: [Maria-discuss] query result inconsistency between MariaDB
>> 10.0.x and Oracle MySQL 5.1.x
>> Para: "MariaDB discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
>> Fecha: viernes, 23 de mayo, 2014 20:18
>>
>> 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
>>
>>
>> _______________________________________________
>> 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
>>
>
>
> _______________________________________________
> 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
References
