Bug #61568 Result is not grouped on sequential store procedure runs
Submitted: 20 Jun 2011 14:22 Modified: 21 Jun 2011 6:40
Reporter: Mikhail Sedov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.5.15, 5.5.13 OS:Any
Assigned to: CPU Architecture:Any
Tags: GROUP BY, query, stored procedure

[20 Jun 2011 14:22] Mikhail Sedov
Description:
Grouping at one of selection statement has been ignored during second run of  stored procedure. 

How to repeat:

sql script with tables definition, stored function and actions to reproduce the problem are attached. 

Stored function:

...

SELECT 
  ...
  UNIX_TIMESTAMP(STARTTIME) DIV 900 AS GROUP_COLUMN
FROM ... JOIN ..
...
GROUP BY .., GROUP_COLUMN

Result is not grouped correctly. 
Problem is reproduced only on second invocation of procedure. 

2 files are attached:

db_issue.sql - script to reproduce the problem. 
db_issue_workaround.sql - same script with workaround.

Incorrect result from FACT_AGG table after db_issue.sql execution:

LOCATIONID, DAY, TIME, FACT_COUNT
'00000000-0000-0000-0000-000000000001', 20110616, 180000, 5
'ad617309-c673-48aa-a908-b66225576e4c', 20110616, 180000, 5

Suggested result from FACT_AGG table (after db_issue_workaround.sql execution):

LOCATIONID, DAY, TIME, FACT_COUNT
'00000000-0000-0000-0000-000000000001', 20110616, 180000, 3
'ad617309-c673-48aa-a908-b66225576e4c', 20110616, 180000, 3
'00000000-0000-0000-0000-000000000001', 20110616, 181500, 2
'ad617309-c673-48aa-a908-b66225576e4c', 20110616, 181500, 2

Suggested fix:
Workaround: Prepared statement from INSERT .. SELECT statement fixes the problem
[20 Jun 2011 14:23] Mikhail Sedov
script to reproduce the problem

Attachment: db_issue.sql (application/octet-stream, text), 4.35 KiB.

[20 Jun 2011 14:23] Mikhail Sedov
possible workaround

Attachment: db_issue_workaround.sql (application/octet-stream, text), 4.42 KiB.

[21 Jun 2011 6:40] Valeriy Kravchuk
Verified with current mysql-5.5 on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test < ~/Downloads/db_issue.sql 
macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.15-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from fact_agg;
+--------------------------------------+----------+--------+------------+
| LOCATIONID                           | DAY      | TIME   | FACT_COUNT |
+--------------------------------------+----------+--------+------------+
| 00000000-0000-0000-0000-000000000001 | 20110616 | 180000 |          5 |
| ad617309-c673-48aa-a908-b66225576e4c | 20110616 | 180000 |          5 |
+--------------------------------------+----------+--------+------------+
2 rows in set (0.00 sec)

mysql> exit
Bye
macbook-pro:5.5 openxs$ bin/mysql -uroot test < ~/Downloads/db_issue_workaround.sql 
macbook-pro:5.5 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.15-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from fact_agg;
+--------------------------------------+----------+--------+------------+
| LOCATIONID                           | DAY      | TIME   | FACT_COUNT |
+--------------------------------------+----------+--------+------------+
| 00000000-0000-0000-0000-000000000001 | 20110616 | 180000 |          3 |
| ad617309-c673-48aa-a908-b66225576e4c | 20110616 | 180000 |          3 |
| 00000000-0000-0000-0000-000000000001 | 20110616 | 181500 |          2 |
| ad617309-c673-48aa-a908-b66225576e4c | 20110616 | 181500 |          2 |
+--------------------------------------+----------+--------+------------+
4 rows in set (0.00 sec)

Workaround is to use prepared statement.