Bug #53534 Incorrect 'select SQL_BIG_RESULT...' with GROUP BY on subquery columns
Submitted: 10 May 2010 8:50 Modified: 6 Dec 2011 1:05
Reporter: Ole John Aske Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.1.44, 5.1.47, 5.1.49, 5.0, 5.6.99 OS:Any
Assigned to: CPU Architecture:Any
Tags: GROUP BY, SQL_BIG_RESULT, wrong result

[10 May 2010 8:50] Ole John Aske
Description:
Selecting a query SQL_BIG_RESULT may change the result set from the query - which it is absolutely not supposed to do.

Experimenting with different variants of the query this seems to be related to the usage of GROUP BY in combination with scalar subquery.

How to repeat:
Load attached mysqldump

1) The following query is our 'baseline':

SELECT
  table1.col_int_key AS field1,
  (SELECT table2s.col_int_unique FROM A AS table1s JOIN C AS table2s ON table1s.pk = table2s.col_int WHERE table2s.pk = table1.pk ) AS field3
FROM E AS table1
GROUP BY field1, field3;

+--------+--------+
| field1 | field3 |
+--------+--------+
|   NULL |   NULL |
|      0 |      0 |
|      1 |   NULL |
|      2 |   NULL |
|      3 |      8 |
|      4 |   NULL |
|      6 |   NULL |
|      8 |   NULL |
|      8 |     40 |
|      9 |   NULL |
|     10 |   NULL |
|     11 |   NULL |
|     12 |   NULL |
|     15 |   NULL |
|     17 |   NULL |
|     25 |   NULL |
|     26 |   NULL |
|     26 |     33 |
|     29 |   NULL |
|     35 |      3 |
|     39 |   NULL |
|     42 |   NULL |
|     44 |   NULL |
|     48 |   NULL |
|     48 |     15 |
|     50 |   NULL |
+--------+--------+
26 rows in set (0.00 sec)

We then add a SQL_BIG_RESULT to the statement and get one row less in the result set:

 SELECT SQL_BIG_RESULT
    ->   table1.col_int_key AS field1,
    ->   (SELECT table2s.col_int_unique FROM A AS table1s JOIN C AS table2s ON table1s.pk = table2s.col_int WHERE table2s.pk = table1.pk ) AS field3
    -> FROM E AS table1
    -> GROUP BY field1, field3;
+--------+--------+
| field1 | field3 |
+--------+--------+
|   NULL |   NULL |
|      0 |      0 |
|      1 |   NULL |
|      2 |   NULL |
|      3 |      8 |
|      4 |   NULL |
|      6 |   NULL |
|      8 |   NULL |
|      9 |   NULL |
|     10 |   NULL |
|     11 |   NULL |
|     12 |   NULL |
|     15 |   NULL |
|     17 |   NULL |
|     25 |   NULL |
|     26 |     33 |
|     26 |   NULL |
|     29 |   NULL |
|     35 |      3 |
|     39 |   NULL |
|     42 |   NULL |
|     44 |   NULL |
|     48 |     15 |
|     48 |   NULL |
|     50 |   NULL |
+--------+--------+
25 rows in set (0.00 sec)
[10 May 2010 8:50] Ole John Aske
schema + data for the testcase

Attachment: spj_myisam.dump (application/octet-stream, text), 37.08 KiB.

[10 May 2010 12:05] MySQL Verification Team
Thank you for the bug report. Please try with today source?. Thanks in advanc.

miguel@tikal:~$ dbs/5.0/bin/mysqladmin -uroot shutdown
miguel@tikal:~$ dbs/5.0/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.48 Source distribution

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

mysql> create database spj_myisam;
Query OK, 1 row affected (0.00 sec)

mysql> use spj_myisam
Database changed
mysql> source /home/miguel/Downloads/dump
Query OK, 0 rows affected (0.00 sec)

<cut>

mysql> SELECT
    ->   table1.col_int_key AS field1,
    ->   (SELECT table2s.col_int_unique FROM A AS table1s JOIN C AS table2s ON table1s.pk =
    -> table2s.col_int WHERE table2s.pk = table1.pk ) AS field3
    -> FROM E AS table1
    -> GROUP BY field1, field3;
+--------+--------+
| field1 | field3 |
+--------+--------+
|   NULL |   NULL | 
|      0 |      0 | 
|      1 |   NULL | 
|      2 |   NULL | 
|      3 |      8 | 
|      4 |   NULL | 
|      6 |   NULL | 
|      8 |   NULL | 
|      8 |     40 | 
|      9 |   NULL | 
|     10 |   NULL | 
|     11 |   NULL | 
|     12 |   NULL | 
|     15 |   NULL | 
|     17 |   NULL | 
|     25 |   NULL | 
|     26 |   NULL | 
|     26 |     33 | 
|     29 |   NULL | 
|     35 |      3 | 
|     39 |   NULL | 
|     42 |   NULL | 
|     44 |   NULL | 
|     48 |   NULL | 
|     48 |     15 | 
|     50 |   NULL | 
+--------+--------+
26 rows in set (0.00 sec)

mysql> SELECT SQL_BIG_RESULT
    ->   table1.col_int_key AS field1,
    ->   (SELECT table2s.col_int_unique FROM A AS table1s JOIN C AS table2s ON table1s.pk =
    -> table2s.col_int WHERE table2s.pk = table1.pk ) AS field3
    -> FROM E AS table1
    -> GROUP BY field1, field3;
+--------+--------+
| field1 | field3 |
+--------+--------+
|   NULL |   NULL | 
|      0 |      0 | 
|      1 |   NULL | 
|      2 |   NULL | 
|      3 |      8 | 
|      4 |   NULL | 
|      6 |   NULL | 
|      8 |   NULL | 
|      8 |     40 | 
|      9 |   NULL | 
|     10 |   NULL | 
|     11 |   NULL | 
|     12 |   NULL | 
|     15 |   NULL | 
|     17 |   NULL | 
|     25 |   NULL | 
|     26 |   NULL | 
|     26 |     33 | 
|     29 |   NULL | 
|     35 |      3 | 
|     39 |   NULL | 
|     42 |   NULL | 
|     44 |   NULL | 
|     48 |   NULL | 
|     48 |     15 | 
|     50 |   NULL | 
+--------+--------+
26 rows in set (0.00 sec)

mysql> exit
Bye
miguel@tikal:~$
[10 May 2010 16:15] MySQL Verification Team
I was able to repeat with 5.0 but not anymore with 5.1.48 (today source) then I assume it was fixed somewhat in 5.1.
[10 Jun 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[13 Jul 2010 12:12] Ole John Aske
Has verified that this bug is not reproducible in 5.1.47 (5.1.47-ndb-7.0.16) - Changed status to 'Closed'
[13 Jul 2010 12:56] Ole John Aske
Reopened this bug (which I just closed):

Further RQG testing shows that there are still issues with SQL_BIG_RESULT in combination with GROUP BY. (Has tested the queries below on both 5.1.47 and 5.1.49)

A (simpler) variant of the previous query is now returning incorrect result.

Baseline query (correct result):
SELECT
  table1.col_int_unique AS field1,
  table1.col_int_unique AS field2
 FROM K AS table1
 GROUP BY field1, field2;

+--------+--------+
| field1 | field2 |
+--------+--------+
|   NULL |   NULL |
|      0 |      0 |
|      1 |      1 |
|      2 |      2 |
|      3 |      3 |
|      5 |      5 |
|      6 |      6 |
|     13 |     13 |
|     18 |     18 |
|     22 |     22 |
|     23 |     23 |
|     30 |     30 |
|     34 |     34 |
|     36 |     36 |
+--------+--------+
14 rows in set (0.00 sec)

We then add 'SQL_BIG_RESULT' to the query

SELECT SQL_BIG_RESULT
  table1.col_int_unique AS field1,
  table1.col_int_unique AS field2
 FROM K AS table1
 GROUP BY field1, field2;

+--------+--------+
| field1 | field2 |
+--------+--------+
|     36 |     36 |
|     36 |     36 |
|     36 |     36 |
|     36 |     36 |
|     36 |     36 |
|     36 |     36 |
|     36 |     36 |
|     36 |     36 |
|     36 |     36 |
|     36 |     36 |
|     36 |     36 |
|     36 |     36 |
|     36 |     36 |
|     36 |     36 |
+--------+--------+
14 rows in set (0.00 sec)
[13 Jul 2010 12:58] Ole John Aske
Appended mysqldump of new test DB - might differ slightly from previous attached dump.

Attachment: spj_myisam.sql (text/x-sql), 120.22 KiB.

[14 Jul 2010 5:56] Sveta Smirnova
Thank you for the feedback.

Verified as described. With version next-mr I get NULLs instead of 36.

Simplified test case:

create table t1(`col_int_unique` int(11) DEFAULT NULL, UNIQUE KEY `col_int_unique` (`col_int_unique`));
INSERT INTO `t1` VALUES (2),(23),(13),(3),(5),(18),(1),(22),(0),(6),(36),(34),(NULL),(30);

SELECT
  table1.col_int_unique AS field1,
  table1.col_int_unique AS field2
 FROM t1 AS table1
 GROUP BY field1, field2;

SELECT SQL_BIG_RESULT
  table1.col_int_unique AS field1,
  table1.col_int_unique AS field2
 FROM t1 AS table1
 GROUP BY field1, field2;

drop table t1;
[2 Sep 2011 12:13] Antonin Lenfant
I discovered what I think is the same problem with MySQL 5.5.8
Using SQL_BIG_QUERY changed the results compared to when  it wasn't present and - even worse ! - didn't give the same results when I repeated the query multiple times, with the result changing every 10 queries or so.

I was using it with a query comporting a subquery (with an order by) which was then grouped by a column.

This is very concerning and serious as it seems this is an optimization and it should absolutely NOT change a query's result ! I just lost a few hours on this one.
[6 Dec 2011 1:05] Paul DuBois
Noted in 5.6.4 changelog.

The SQL_BIG_RESULT modifier could change the results for queries that
included a GROUP BY clause.
[27 Sep 2012 17:02] Jon Stephens
Also fixed in trunk, currently tagged 5.7.0.

No status change.