Bug #64855 | Stored Procedure with GROUP/ORDER BY return unpredicatable results with CASE/IF | ||
---|---|---|---|
Submitted: | 3 Apr 2012 14:53 | Modified: | 16 May 2012 13:20 |
Reporter: | Patrick Mulvany (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
Version: | 5.5.21, 5.6.5-m8, 5.1.64, 5.6.6, 5.5.25, 5.5.20-ndb-7.2.5 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[3 Apr 2012 14:53]
Patrick Mulvany
[3 Apr 2012 14:53]
Patrick Mulvany
Part 1 of Test case
Attachment: Dump-testcase1-script1.sql (application/octet-stream, text), 6.39 KiB.
[3 Apr 2012 14:56]
Patrick Mulvany
Part 2 of Test case
Attachment: Dump-testcase1-script2.sql (application/octet-stream, text), 7.57 KiB.
[3 Apr 2012 14:56]
Patrick Mulvany
Part 3 of Test case
Attachment: Dump-testcase1-script3.sql (application/octet-stream, text), 3.26 KiB.
[3 Apr 2012 14:57]
Patrick Mulvany
Part 4 of Test case
Attachment: Dump-testcase1-script4.sql (application/octet-stream, text), 2.58 KiB.
[3 Apr 2012 14:57]
Patrick Mulvany
Part 5 of Test case
Attachment: Dump-testcase1-script5.sql (application/octet-stream, text), 3.93 KiB.
[3 Apr 2012 14:57]
Patrick Mulvany
Part 6 of Test case
Attachment: Dump-testcase1-script6.sql (application/octet-stream, text), 3.26 KiB.
[3 Apr 2012 15:17]
Patrick Mulvany
Looks like the drop/recreate may be a diversion as you get the same effect by deleting all by the last result from the RawData table, calling the sp and the reinserting the data. mysql> delete from RawData where nps_score>-81; Query OK, 45 rows affected (0.00 sec) mysql> call sp_test4(1,'nps_score'); +-------------------+-------------------+--------------------+ | rowid | sort_asc_string_1 | sort_numeric | +-------------------+-------------------+--------------------+ | 22364454717488139 | NULL STRING | -84.21050262451172 | +-------------------+-------------------+--------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> \. Dump-testcase1-script5.sql Query OK, 45 rows affected (0.00 sec) Records: 45 Duplicates: 0 Warnings: 0 mysql> call sp_test4(1,'nps_score'); +-------------------+-------------------+--------------------+ | rowid | sort_asc_string_1 | sort_numeric | +-------------------+-------------------+--------------------+ | 22364454717488153 | NULL STRING | -67.90119934082031 | +-------------------+-------------------+--------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
[3 Apr 2012 15:29]
Patrick Mulvany
Minor update to use column names in GROUP BY rather than numeric position
Attachment: Dump-testcase1-script3.sql (application/octet-stream, text), 3.29 KiB.
[3 Apr 2012 15:29]
Patrick Mulvany
Minor update to use column names in ORDER BY rather than numeric position
Attachment: Dump-testcase1-script6.sql (application/octet-stream, text), 3.29 KiB.
[3 Apr 2012 16:11]
Patrick Mulvany
As tables involved don't have indexes wondering if :- Bug #54481 GROUP BY loses effect with JOIN + ORDER BY + LIMIT and join caching Might be related Helps if you get the number right ;)
[4 Apr 2012 14:41]
Patrick Mulvany
Updated script so that RawData was a INNODB table rather than a MEMORY table and error did not occur. Updated the script so that both tables were MEMORY and error occurs so looks like this may be a MEMORY engine related issue.
[4 Apr 2012 16:35]
Patrick Mulvany
GROUP BY appears to be fixed in 5.6.4-m7 but not ORDER BY for both cases dropping the table and replacing with a one row table and deleting all but one row.
[5 Apr 2012 10:20]
Patrick Mulvany
Updated script 3 group by (now 7) to simplify call sp_test6('nps_score'); and script 6 order by (now 8) call sp_test7('nps_score'); New scripts show better how the issue occurs when GROUP BY or ORDER BY. When stored procedure has a select that has a column that uses CASE once a single record result is returned information from GROUP BY/ORDER BY clause is ignored for subsiquent executions of the stored procedure.
[5 Apr 2012 10:20]
Patrick Mulvany
GROUP BY example
Attachment: Dump-testcase1-script7.sql (application/octet-stream, text), 2.95 KiB.
[5 Apr 2012 10:20]
Patrick Mulvany
ORDER BY example
Attachment: Dump-testcase1-script8.sql (application/octet-stream, text), 2.98 KiB.
[5 Apr 2012 10:41]
Patrick Mulvany
Example replaces scripts 3,6,7,8 covers GROUP BY/ORDER BY with CASE/IF
Attachment: Dump-testcase1-script9.sql (application/octet-stream, text), 1.86 KiB.
[5 Apr 2012 10:47]
Patrick Mulvany
Tested IF to see if it was CASE specific and IF also effected. Part I Test using script 1 then 2 as data sources script 9 for stored procs. call sp_test6('nps_score'); -- GROUP BY CASE call sp_test7('nps_score'); -- ORDER BY CASE call sp_test8('nps_score'); -- GROUP BY IF call sp_test9('nps_score'); -- ORDER BY IF Part II Use script 4 to rebuild RawData with one record. recall procedures. Part III Use script 1 to rebuild RawData with multiple records. recall procedures and get different results from first runs in Part I.
[10 Apr 2012 9:30]
Patrick Mulvany
Probably related bug :- Bug #61568 Result is not grouped on sequential store procedure runs
[16 Apr 2012 18:04]
Sveta Smirnova
Thank you for the report. Please provide output of first and second run for last test case.
[18 Apr 2012 13:49]
Patrick Mulvany
MySQL 5.5.21 log showing GROUP BY issue
Attachment: mysql-5.5.21-group-by-case.log (application/octet-stream, text), 9.98 KiB.
[18 Apr 2012 13:49]
Patrick Mulvany
MySQL 5.6.4 log showing GROUP BY and CASE working
Attachment: mysql-5.6.4-group-by-case.log (application/octet-stream, text), 9.20 KiB.
[18 Apr 2012 13:55]
Patrick Mulvany
MySQL 5.5.21 log showing ORDER BY and CASE issue
Attachment: mysql-5.5.21-order-by-case.log (application/octet-stream, text), 9.34 KiB.
[18 Apr 2012 13:55]
Patrick Mulvany
MySQL 5.6.4 log showing ORDER BY and CASE issue
Attachment: mysql-5.6.4-order-by-case.log (application/octet-stream, text), 11.99 KiB.
[18 Apr 2012 14:01]
Patrick Mulvany
Added example logs for MySQL 5.5.21 and 5.6.4 showing MySQL 5.5.21 - GROUP BY and CASE issue MySQL 5.6.4 - GROUP BY and CASE working MySQL 5.5.21 - ORDER BY and CASE issue MySQL 5.6.4 - ORDER BY and CASE issue Identical results occur for scripts using IF rather than CASE but not added logs but can do if required. Recompiling or reconnection to the server resets the fault until another single row record occurs. Also seen same effect when MySQL is using tempory table for a sort in a query but harder to reproduce test case.
[4 May 2012 13:55]
Patrick Mulvany
Retested with 5.6.5-m8 and same issue with ORDER BY and CASE/IF however GROUP BY and CASE/IF works same as in 5.6.4-m7
[4 May 2012 20:11]
Sveta Smirnova
Thank you for the feedback. ORDER BY case verified as described. GROUP BY case is not repeatable for me with any version.
[11 May 2012 9:36]
Patrick Mulvany
Just retested with MySQL 5.5.24 and agree GROUP BY has been fixed at some point between 5.5.22 and 5.5.24. (don't have a copy of 5.5.23 to do an exhaustive tests) Both GROUP BY and ORDER BY broken in MySQL Cluster 7.2.5 as expected due to this being based on 5.5.20. ORDER BY broken in 5.5.24 as expected. Uploading simplified example scripts and logs of all additional tests. sp_reset_sp() just re/creates a view to cause all stored procs to be recompiled rather than having to recreate them.
[11 May 2012 9:37]
Patrick Mulvany
Initialisation of test case
Attachment: Dump-testcase2-script1.sql (application/octet-stream, text), 12.72 KiB.
[11 May 2012 9:37]
Patrick Mulvany
Repopulate test case
Attachment: Dump-testcase2-script3.sql (application/octet-stream, text), 3.84 KiB.
[11 May 2012 9:38]
Patrick Mulvany
Test case logs for MySQL 5.5.24
Attachment: Dump-testcase2-MySQL-5.5.24.log (application/octet-stream, text), 21.28 KiB.
[11 May 2012 9:39]
Patrick Mulvany
Test case logs for MySQL Cluster 7.2.5
Attachment: Dump-testcase2-MySQL-cluster-7.2.5.log (application/octet-stream, text), 20.15 KiB.
[16 May 2012 13:20]
Patrick Mulvany
Just retested with MySQL 5.5.23 and GROUP BY was fixed between 5.5.22 and 5.5.23.
[28 Feb 2013 13:10]
MySQL Verification Team
Hi Patrick, I'm not certain what is the real problem on this bug. Can you please describe what is the real problem? For me, it looks the output is correct in every case. So, please show me an expected output so that I can compare with wrong one. Kind regards, Mikiya
[28 Feb 2013 13:31]
Sveta Smirnova
test case for MTR
Attachment: bug64855.test (application/octet-stream, text), 15.81 KiB.
[28 Feb 2013 13:42]
Sveta Smirnova
Mikiya, Patrick complains about wrong results of ORDER BY and GROUP BY queries. Patrick, please check current version 5.6.10: I can not repeat this case anymore since 5.6.9.