| 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: | |
| 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
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.
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)