#At file:///export/home/didrik/repo/next-mr-opt-team-wl1393-merge/ based on revid:tor.didriksen@stripped
3258 Tor Didriksen 2010-12-10
Bug #58782 Missing rows with SELECT .. WHERE .. IN subquery with full GROUP BY and no aggr
The missing results were caused by a 'group by' which was transformed to an 'order by'.
@ mysql-test/r/explain.result
New explain result, the group by can be eliminated.
@ mysql-test/r/group_by.result
New test case.
@ mysql-test/r/myisam_mrr.result
New explain result, the group by can be eliminated.
@ mysql-test/r/myisam_mrr_cost.result
New explain result, the group by can be eliminated.
@ mysql-test/r/myisam_mrr_cost_icp.result
New explain result, the group by can be eliminated.
@ mysql-test/r/myisam_mrr_icp.result
New explain result, the group by can be eliminated.
@ mysql-test/r/myisam_mrr_none.result
New explain result, the group by can be eliminated.
@ mysql-test/r/order_by_icp_mrr.result
New (correct) result.
@ mysql-test/r/order_by_none.result
New (correct) result.
@ mysql-test/t/group_by.test
New test case.
@ sql/sql_select.cc
If the query as a GROUP BY, which can be converted to an ORDER BY,
we can eliminate it for subqueries.
modified:
mysql-test/r/explain.result
mysql-test/r/group_by.result
mysql-test/r/myisam_mrr.result
mysql-test/r/myisam_mrr_cost.result
mysql-test/r/myisam_mrr_cost_icp.result
mysql-test/r/myisam_mrr_icp.result
mysql-test/r/myisam_mrr_none.result
mysql-test/r/order_by_icp_mrr.result
mysql-test/r/order_by_none.result
mysql-test/t/group_by.test
sql/sql_select.cc
=== modified file 'mysql-test/r/explain.result'
--- a/mysql-test/r/explain.result 2010-12-06 13:12:51 +0000
+++ b/mysql-test/r/explain.result 2010-12-10 15:00:25 +0000
@@ -283,7 +283,7 @@ WHERE 1 > ALL((SELECT 1 FROM t1 JOIN t1
WHERE t1.f1 GROUP BY t1.f1));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1 Using filesort
+2 SUBQUERY a system NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
PREPARE stmt FROM
'EXPLAIN SELECT 1 FROM t1
@@ -293,12 +293,12 @@ PREPARE stmt FROM
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1 Using filesort
+2 SUBQUERY a system NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1 Using filesort
+2 SUBQUERY a system NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
DEALLOCATE PREPARE stmt;
PREPARE stmt FROM
@@ -309,12 +309,12 @@ PREPARE stmt FROM
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1 Using filesort
+2 SUBQUERY a system NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
-2 SUBQUERY a system NULL NULL NULL NULL 1 Using filesort
+2 SUBQUERY a system NULL NULL NULL NULL 1
2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result 2010-12-06 13:12:51 +0000
+++ b/mysql-test/r/group_by.result 2010-12-10 15:00:25 +0000
@@ -1886,3 +1886,48 @@ f1 MIN(f2) MAX(f2)
4 00:25:00 00:25:00
DROP TABLE t1;
#End of test#49771
+#
+# Bug #58782
+# Missing rows with SELECT .. WHERE .. IN subquery
+# with full GROUP BY and no aggr
+#
+CREATE TABLE t1 (
+pk INT NOT NULL,
+col_int_nokey INT,
+PRIMARY KEY (pk)
+);
+INSERT INTO t1 VALUES (10,7);
+INSERT INTO t1 VALUES (11,1);
+INSERT INTO t1 VALUES (12,5);
+INSERT INTO t1 VALUES (13,3);
+SELECT pk AS field1, col_int_nokey AS field2
+FROM t1
+WHERE col_int_nokey > 0
+GROUP BY field1, field2;
+field1 field2
+10 7
+11 1
+12 5
+13 3
+CREATE TABLE where_subselect
+SELECT pk AS field1, col_int_nokey AS field2
+FROM t1
+WHERE col_int_nokey > 0
+GROUP BY field1, field2
+;
+SELECT *
+FROM where_subselect
+WHERE (field1, field2) IN (
+SELECT pk AS field1, col_int_nokey AS field2
+FROM t1
+WHERE col_int_nokey > 0
+GROUP BY field1, field2
+);
+field1 field2
+10 7
+11 1
+12 5
+13 3
+DROP TABLE t1;
+DROP TABLE where_subselect;
+# End of Bug #58782
=== modified file 'mysql-test/r/myisam_mrr.result'
--- a/mysql-test/r/myisam_mrr.result 2010-12-06 13:10:10 +0000
+++ b/mysql-test/r/myisam_mrr.result 2010-12-10 15:00:25 +0000
@@ -347,7 +347,7 @@ GROUP BY t2.pk
);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using where; Using filesort
+2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using where
Warnings:
Note 1003 select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0
DROP TABLE t1, t2;
=== modified file 'mysql-test/r/myisam_mrr_cost.result'
--- a/mysql-test/r/myisam_mrr_cost.result 2010-12-06 13:10:10 +0000
+++ b/mysql-test/r/myisam_mrr_cost.result 2010-12-10 15:00:25 +0000
@@ -347,7 +347,7 @@ GROUP BY t2.pk
);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using where; Using filesort
+2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using where
Warnings:
Note 1003 select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0
DROP TABLE t1, t2;
=== modified file 'mysql-test/r/myisam_mrr_cost_icp.result'
--- a/mysql-test/r/myisam_mrr_cost_icp.result 2010-12-06 13:10:10 +0000
+++ b/mysql-test/r/myisam_mrr_cost_icp.result 2010-12-10 15:00:25 +0000
@@ -347,7 +347,7 @@ GROUP BY t2.pk
);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition; Using where; Using filesort
+2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition
Warnings:
Note 1003 select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0
DROP TABLE t1, t2;
=== modified file 'mysql-test/r/myisam_mrr_icp.result'
--- a/mysql-test/r/myisam_mrr_icp.result 2010-12-06 13:10:10 +0000
+++ b/mysql-test/r/myisam_mrr_icp.result 2010-12-10 15:00:25 +0000
@@ -347,7 +347,7 @@ GROUP BY t2.pk
);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition; Using where; Using filesort
+2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using index condition
Warnings:
Note 1003 select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0
DROP TABLE t1, t2;
=== modified file 'mysql-test/r/myisam_mrr_none.result'
--- a/mysql-test/r/myisam_mrr_none.result 2010-11-26 15:20:05 +0000
+++ b/mysql-test/r/myisam_mrr_none.result 2010-12-10 15:00:25 +0000
@@ -346,7 +346,7 @@ GROUP BY t2.pk
);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
-2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using where; Using filesort
+2 SUBQUERY t2 ref int_key int_key 5 const 1 100.00 Using where
Warnings:
Note 1003 select min(`test`.`t1`.`pk`) AS `MIN(t1.pk)` from `test`.`t1` where 0
DROP TABLE t1, t2;
=== modified file 'mysql-test/r/order_by_icp_mrr.result'
--- a/mysql-test/r/order_by_icp_mrr.result 2010-12-09 11:54:39 +0000
+++ b/mysql-test/r/order_by_icp_mrr.result 2010-12-10 15:00:25 +0000
@@ -2384,6 +2384,8 @@ GROUP BY field1, field2
);
field1 field2
27 27
+28 28
+29 29
DROP TABLE t1;
DROP TABLE where_subselect;
# End of Bug #58761
=== modified file 'mysql-test/r/order_by_none.result'
--- a/mysql-test/r/order_by_none.result 2010-12-09 11:54:39 +0000
+++ b/mysql-test/r/order_by_none.result 2010-12-10 15:00:25 +0000
@@ -2383,6 +2383,8 @@ GROUP BY field1, field2
);
field1 field2
27 27
+28 28
+29 29
DROP TABLE t1;
DROP TABLE where_subselect;
# End of Bug #58761
=== modified file 'mysql-test/t/group_by.test'
--- a/mysql-test/t/group_by.test 2010-12-06 13:12:51 +0000
+++ b/mysql-test/t/group_by.test 2010-12-10 15:00:25 +0000
@@ -1273,3 +1273,52 @@ SELECT f1,MIN(f2),MAX(f2) FROM t1 GROUP
DROP TABLE t1;
--echo #End of test#49771
+
+--echo #
+--echo # Bug #58782
+--echo # Missing rows with SELECT .. WHERE .. IN subquery
+--echo # with full GROUP BY and no aggr
+--echo #
+
+CREATE TABLE t1 (
+ pk INT NOT NULL,
+ col_int_nokey INT,
+ PRIMARY KEY (pk)
+);
+
+INSERT INTO t1 VALUES (10,7);
+INSERT INTO t1 VALUES (11,1);
+INSERT INTO t1 VALUES (12,5);
+INSERT INTO t1 VALUES (13,3);
+
+## original query:
+
+SELECT pk AS field1, col_int_nokey AS field2
+FROM t1
+WHERE col_int_nokey > 0
+GROUP BY field1, field2;
+
+## store query results in a new table:
+
+CREATE TABLE where_subselect
+ SELECT pk AS field1, col_int_nokey AS field2
+ FROM t1
+ WHERE col_int_nokey > 0
+ GROUP BY field1, field2
+;
+
+## query the new table and compare to original using WHERE ... IN():
+
+SELECT *
+FROM where_subselect
+WHERE (field1, field2) IN (
+ SELECT pk AS field1, col_int_nokey AS field2
+ FROM t1
+ WHERE col_int_nokey > 0
+ GROUP BY field1, field2
+);
+
+DROP TABLE t1;
+DROP TABLE where_subselect;
+
+--echo # End of Bug #58782
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-12-09 11:54:39 +0000
+++ b/sql/sql_select.cc 2010-12-10 15:00:25 +0000
@@ -2098,9 +2098,10 @@ JOIN::optimize()
We have found that grouping can be removed since groups correspond to
only one row anyway, but we still have to guarantee correct result
order. The line below effectively rewrites the query from GROUP BY
- <fields> to ORDER BY <fields>. There are two exceptions:
+ <fields> to ORDER BY <fields>. There are three exceptions:
- if skip_sort_order is set (see above), then we can simply skip
GROUP BY;
+ - if we are in a subquery, we don't have to maintain order
- we can only rewrite ORDER BY if the ORDER BY fields are 'compatible'
with the GROUP BY ones, i.e. either one is a prefix of another.
We only check if the ORDER BY is a prefix of GROUP BY. In this case
@@ -2110,7 +2111,13 @@ JOIN::optimize()
'order' as is.
*/
if (!order || test_if_subpart(group_list, order))
- order= skip_sort_order ? 0 : group_list;
+ {
+ if (skip_sort_order ||
+ select_lex->master_unit()->item) // This is a subquery
+ order= NULL;
+ else
+ order= group_list;
+ }
/*
If we have an IGNORE INDEX FOR GROUP BY(fields) clause, this must be
rewritten to IGNORE INDEX FOR ORDER BY(fields).
Attachment: [text/bzr-bundle] bzr/tor.didriksen@oracle.com-20101210150025-xpi8jizo6f6hzc7n.bundle
