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 <[email protected]>: > 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 <[email protected]>: > > 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 < >> [email protected]>: >> >> 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 <[email protected]> >>> Software, consulting, and services available at http://pyropus.ca/ >>> ------------------------------------------------------------------ >>> >>> _______________________________________________ >>> Mailing list: https://launchpad.net/~maria-discuss >>> Post to : [email protected] >>> 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
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
