← Back to team overview

maria-discuss team mailing list archive

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