List:Commits« Previous MessageNext Message »
From:Tor Didriksen Date:December 10 2010 3:00pm
Subject:bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258) Bug#58782
View as plain text  
#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
Thread
bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258) Bug#58782Tor Didriksen10 Dec
  • Re: bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258)Bug#58782Jorgen Loland10 Dec
  • Re: bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258)Bug#58782Øystein Grøvlen11 Dec
    • Re: bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258)Bug#58782Jorgen Loland13 Dec