From: Jorgen Loland
Date: December 10 2010 3:17pm
Subject: Re: bzr commit into mysql-trunk-bugfixing branch (tor.didriksen:3258)
 Bug#58782
List-Archive: http://lists.mysql.com/commits/126536
Message-Id: <4D024476.3050106@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Tor,

Thank you for the fix. Patch approved.

On 12/10/2010 04:00 PM, Tor Didriksen wrote:
> #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).
>
>
>
>
>

-- 
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway