Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Not a Bug
-
Affects Version/s: 5.5.34
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
-
Environment:windows server Glassfish 4.0 eclipse primefaces
Description
this sql shows the wrong values. The order by in the left join shows the oldest values and not the values which are stored after t2.giltab <= '2013-12-22' . In the table are values with different validity date and i want to show only the last one and not the history. In Mysql it works. But we want to use mariadb in our opensource erp-application http://osretail.de/osRetail/
SELECT t2.mwstimhundert,t2.satz, t2.land, t2.code, t2.giltab FROM mwst m LEFT JOIN (SELECT * FROM mwst m1 ORDER BY m1.giltab DESC) AS t2 ON m.mandant = t2.mandant AND m.land = t2.land AND m.code = t2.code WHERE m.mandant = 999 AND t2.giltab <= '2013-12-22' GROUP BY t2.land, t2.code ORDER BY t2.land DESC, t2.giltab DESC, t2.code DESC
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Hello Elena,
you see 3 attachments. The first shows the contents of the datable the second the query with mariaDB and the last query with mysql.
Regards
Peter
Peter Magsam
K&W Consulting GmbH
Wichsenstein 312
D-91327 Gößweinstein
Tel. +499242/29 99 822
Hi Peter,
Thank you, the situation is clearer now.
Both results are not wrong. I know it's a frustrating thing to hear, but unfortunately it is so, not due to MariaDB specifics, but due to MySQL basics. It happens because your query is initially indeterministic.
Please check the following section in MySQL manual: http://dev.mysql.com/doc/refman/5.5/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. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. 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. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.
"
It happens often that people fall into this trap, with their main argument being "but it worked all right in MySQL for ages". It really did not and does not, and what you are getting is a result of pure luck. For example, if you try to switch the table from InnoDB to MyISAM or vice versa, you are likely to get yet another resultset, which will also be correct, if there is any point to call any result correct if the query itself is broken.
For a side note, if you want to keep playing with this query, please check the data in your databases, apparently it is not identical. The MySQL resultset in your 3rd screenshot contains a record with giltab='2013-01-01', which is not in the first screenshot at all.
Could you please provide an example of the wrong result and the expected result set?
Thanks.