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
