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:
None 
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
Triage: Needs Triage: D2 (Serious)

[3 Apr 2012 14:53] Patrick Mulvany
Description:
Use of non temporary/temporary table within a store procedure can result in wrong data being returned when join used with group by (Also seen simliar effect with order by being ignored example included) clause.

Stored procedure works fine until a single row in the temporary table then optimiser appears to short ciruit the group by clause and return one row only (the first row?) for all subsiquent executions with different data. In the Order by case the order is ignored. 

Work around is to detect number of rows in Temp table and special case the select to use a non order/group by for single row returns stoping the optimiser from executing the group by/order by when only 1 row is returned.

Thought this may be a varient on :-
Bug #62438 : Temporary Table in Routine

And worklog :-
http://forge.mysql.com/worklog/task.php?id=4179

However this issue occurs when the table has additional records and the tables meta data has not changed.

Repeatable on Windows and Linux servers.

How to repeat:
-- Create RawData with 45 records
\. Dump-testcase1-script1.sql
-- Create RawData2 with 62 records
\. Dump-testcase1-script2.sql
-- Create sp which creates a table joining the other tables and populates
\. Dump-testcase1-script3.sql

-- Succesful test
call sp_test4(1,'nps_score');
-- returns 40 rows of data

-- Create RawData with 1 record
\. Dump-testcase1-script4.sql

-- Succesful test
call sp_test4(1,'nps_score');
-- returns 1 row of data

-- Recreate RawData with 45 rows
\. Dump-testcase1-script1.sql

-- Failing test
call sp_test4(1,'nps_score');
-- returns 1 row of data

-- Recreate sp which creates a table joining the other tables and populates
\. Dump-testcase1-script3.sql

-- Succesful test
call sp_test4(1,'nps_score');
-- returns 40 rows of data

-- Create RawData with 1 record
\. Dump-testcase1-script4.sql

-- Succesful test
call sp_test4(1,'nps_score');
-- returns 1 row of data

-- Inserts 45 rows into RawData
\. Dump-testcase1-script5.sql

-- Failing test
call sp_test4(1,'nps_score');
-- returns 1 row of data

-- Repeat above using script6 instead of script3 to see ORDER BY example
[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] Mikiya Okuno
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.