[MDEV-5464]  sql shows the wrong values Created: 2013-12-18  Updated: 2013-12-22  Due: 2014-01-13  Resolved: 2013-12-22

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.34
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Peter Magsam Assignee: Elena Stepanova
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

windows server Glassfish 4.0 eclipse primefaces


Attachments: JPEG File datatable.JPG     JPEG File mariaDB_wrong.JPG     JPEG File mysql_correct.JPG    

 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



 Comments   
Comment by Elena Stepanova [ 2013-12-19 ]

Could you please provide an example of the wrong result and the expected result set?
Thanks.

Comment by Peter Magsam [ 2013-12-22 ]

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

Comment by Elena Stepanova [ 2013-12-22 ]

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.

Generated at Thu Feb 08 07:04:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.