Below is the list of changes that have just been committed into a local
5.2 repository of tkatchaounov. When tkatchaounov does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-10-25 12:07:16+03:00, timour@stripped +8 -0
BUG#31639: Wrong plan for uncorrelated subquery when loose scan
is applicable.
There were two distinct problems in this bug:
1. At execution time the subquery under an IN predicate was optimized with
the global memeber thd->lex->current_select pointing to the outer select.
This caused the subquery to be optimized differently when
JOIN::optimize needed to analyze current_select.
Fixed by setting the correct current_select for the optimization
and execution (materialization) of the subquery.
2. When running EXPLAIN, optimization took a different path because
- Item_exists_subselect::fix_length_and_dec sets the subquery
LIMIT to 1, then
- mysql_explain_union(), which is called only for EXPLAIN called
unit->set_limit(unit->global_parameters), which resets
join->unit->select_limit_cnt to 1, this in turn
- affected make_join_select to call sel->test_quick_select, which
deleted the quick_select access method.
Fixed by setting the limit correctly when IN is executed via
materialization.
mysql-test/r/group_min_max.result@stripped, 2007-10-25 12:06:37+03:00, timour@stripped +1 -1
Adjusted test result.
mysql-test/r/subselect.result@stripped, 2007-10-25 12:06:40+03:00, timour@stripped +1 -1
Adjusted test result.
mysql-test/r/subselect_mat.result@stripped, 2007-10-25 12:06:42+03:00, timour@stripped +61 -5
Test for BUG#31639.
mysql-test/r/subselect_no_opts.result@stripped, 2007-10-25 12:06:43+03:00, timour@stripped +1 -1
Adjusted test result.
mysql-test/r/subselect_no_semijoin.result@stripped, 2007-10-25 12:06:46+03:00, timour@stripped +1 -1
Adjusted test result.
mysql-test/t/subselect_mat.test@stripped, 2007-10-25 12:06:47+03:00, timour@stripped +42 -4
Test for BUG#31639.
sql/item_subselect.cc@stripped, 2007-10-25 12:06:50+03:00, timour@stripped +23 -4
- reset the LIMIT for the subquery to inifinity if it will be materialized.
- set and restore the correct thd->lex->current_select around the optimization
and materialization of the subquery.
sql/sql_lex.cc@stripped, 2007-10-25 12:06:51+03:00, timour@stripped +16 -8
Update the ASSERT to reflect that with materialization we set the LIMIT
to infinity.
diff -Nrup a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
--- a/mysql-test/r/group_min_max.result 2007-08-26 10:54:59 +03:00
+++ b/mysql-test/r/group_min_max.result 2007-10-25 12:06:37 +03:00
@@ -2256,7 +2256,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHE
a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1_outer index NULL a 10 NULL 15 Using where; Using index
-2 SUBQUERY t1 index NULL a 10 NULL 1 Using index
+2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
EXPLAIN SELECT 1 FROM t1 AS t1_outer GROUP BY a HAVING
a > (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
id select_type table type possible_keys key key_len ref rows Extra
diff -Nrup a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
--- a/mysql-test/r/subselect.result 2007-10-12 16:46:17 +03:00
+++ b/mysql-test/r/subselect.result 2007-10-25 12:06:40 +03:00
@@ -3410,7 +3410,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 SUBQUERY t1 index NULL a 8 NULL 1
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
DROP TABLE t1;
create table t1( f1 int,f2 int);
insert into t1 values (1,1),(2,2);
diff -Nrup a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
--- a/mysql-test/r/subselect_mat.result 2007-10-18 17:50:27 +03:00
+++ b/mysql-test/r/subselect_mat.result 2007-10-25 12:06:42 +03:00
@@ -1,4 +1,4 @@
-drop table if exists t1, t2, t3;
+drop table if exists t1, t2, t3, t1i, t2i, t3i;
create table t1 (a1 char(8), a2 char(8));
create table t2 (b1 char(8), b2 char(8));
create table t3 (c1 char(8), c2 char(8));
@@ -6,25 +6,81 @@ insert into t1 values ('1 - 00', '2 - 00
insert into t1 values ('1 - 01', '2 - 01');
insert into t1 values ('1 - 02', '2 - 02');
insert into t2 values ('1 - 01', '2 - 01');
+insert into t2 values ('1 - 01', '2 - 01');
+insert into t2 values ('1 - 02', '2 - 02');
insert into t2 values ('1 - 02', '2 - 02');
insert into t2 values ('1 - 03', '2 - 03');
insert into t3 values ('1 - 01', '2 - 01');
insert into t3 values ('1 - 02', '2 - 02');
insert into t3 values ('1 - 03', '2 - 03');
insert into t3 values ('1 - 04', '2 - 04');
+create table t1i (a1 char(8), a2 char(8));
+create table t2i (b1 char(8), b2 char(8));
+create table t3i (c1 char(8), c2 char(8));
+create index it1i1 on t1i (a1);
+create index it1i2 on t1i (a2);
+create index it1i3 on t1i (a1, a2);
+create index it2i1 on t2i (b1);
+create index it2i2 on t2i (b2);
+create index it2i3 on t2i (b1, b2);
+create index it3i1 on t3i (c1);
+create index it3i2 on t3i (c2);
+create index it3i3 on t3i (c1, c2);
+insert into t1i select * from t1;
+insert into t2i select * from t2;
+insert into t3i select * from t3;
set @@optimizer_switch=no_semijoin;
/******************************************************************************
* Simple tests. *
******************************************************************************/
-# test re-optimization/re-execution with different execution methods
-# prepare once, exec with different modes
+# BUG#31639: Wrong plan for uncorrelated subquery when loose scan is applicable.
+explain extended
+select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 SUBQUERY t2i range NULL it2i3 9 NULL 3 100.00 Using index for group-by
+Warnings:
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,max(`test`.`t2i`.`b2`) AS `max(b2)` from `test`.`t2i` group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key)))
+select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
+a1 a2
+1 - 01 2 - 01
+1 - 02 2 - 02
+prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
+execute st1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
+2 SUBQUERY t2i range NULL it2i3 9 NULL 3 Using index for group-by
+execute st1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where
+2 SUBQUERY t2i range NULL it2i3 9 NULL 3 Using index for group-by
+prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
+execute st2;
+a1 a2
+1 - 01 2 - 01
+1 - 02 2 - 02
+execute st2;
+a1 a2
+1 - 01 2 - 01
+1 - 02 2 - 02
+explain extended
+select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where
+2 SUBQUERY t2i range it2i1,it2i3 it2i3 18 NULL 3 100.00 Using where; Using index for group-by
+Warnings:
+Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2` from `test`.`t1` where <in_optimizer>((`test`.`t1`.`a1`,`test`.`t1`.`a2`),(`test`.`t1`.`a1`,`test`.`t1`.`a2`) in ( <materialize> (select `test`.`t2i`.`b1` AS `b1`,min(`test`.`t2i`.`b2`) AS `min(b2)` from `test`.`t2i` where (`test`.`t2i`.`b1` > _latin1'0') group by `test`.`t2i`.`b1` ), <primary_index_lookup>(`test`.`t1`.`a1` in <temporary table> on distinct_key)))
+select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
+a1 a2
+1 - 01 2 - 01
+1 - 02 2 - 02
set @@optimizer_switch=no_materialization;
set @@optimizer_switch=no_semijoin;
+drop table t1, t2, t3, t1i, t2i, t3i;
/******************************************************************************
* Test the cache of the left operand of IN. *
******************************************************************************/
set @@optimizer_switch=no_semijoin;
-drop table if exists t1, t2;
create table t1 (s1 int);
create table t2 (s2 int);
insert into t1 values (5),(1),(0);
@@ -33,7 +89,7 @@ select s2 from t2 where s2 in (select s1
s2
0
1
-drop table if exists t1, t2, t3;
+drop table t1, t2;
create table t1 (a int not null, b int not null);
create table t2 (c int not null, d int not null);
create table t3 (e int not null);
diff -Nrup a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
--- a/mysql-test/r/subselect_no_opts.result 2007-08-26 10:55:00 +03:00
+++ b/mysql-test/r/subselect_no_opts.result 2007-10-25 12:06:43 +03:00
@@ -3414,7 +3414,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 SUBQUERY t1 index NULL a 8 NULL 1
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
DROP TABLE t1;
create table t1( f1 int,f2 int);
insert into t1 values (1,1),(2,2);
diff -Nrup a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
--- a/mysql-test/r/subselect_no_semijoin.result 2007-08-26 10:55:01 +03:00
+++ b/mysql-test/r/subselect_no_semijoin.result 2007-10-25 12:06:46 +03:00
@@ -3414,7 +3414,7 @@ EXPLAIN
SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 9 Using where
-2 SUBQUERY t1 index NULL a 8 NULL 1
+2 SUBQUERY t1 ALL NULL NULL NULL NULL 9 Using temporary; Using filesort
DROP TABLE t1;
create table t1( f1 int,f2 int);
insert into t1 values (1,1),(2,2);
diff -Nrup a/mysql-test/t/subselect_mat.test b/mysql-test/t/subselect_mat.test
--- a/mysql-test/t/subselect_mat.test 2007-10-18 17:50:27 +03:00
+++ b/mysql-test/t/subselect_mat.test 2007-10-25 12:06:47 +03:00
@@ -4,7 +4,7 @@
#
--disable_warnings
-drop table if exists t1, t2, t3;
+drop table if exists t1, t2, t3, t1i, t2i, t3i;
--enable_warnings
create table t1 (a1 char(8), a2 char(8));
@@ -16,6 +16,8 @@ insert into t1 values ('1 - 01', '2 - 01
insert into t1 values ('1 - 02', '2 - 02');
insert into t2 values ('1 - 01', '2 - 01');
+insert into t2 values ('1 - 01', '2 - 01');
+insert into t2 values ('1 - 02', '2 - 02');
insert into t2 values ('1 - 02', '2 - 02');
insert into t2 values ('1 - 03', '2 - 03');
@@ -24,6 +26,26 @@ insert into t3 values ('1 - 02', '2 - 02
insert into t3 values ('1 - 03', '2 - 03');
insert into t3 values ('1 - 04', '2 - 04');
+# Indexed columns
+create table t1i (a1 char(8), a2 char(8));
+create table t2i (b1 char(8), b2 char(8));
+create table t3i (c1 char(8), c2 char(8));
+create index it1i1 on t1i (a1);
+create index it1i2 on t1i (a2);
+create index it1i3 on t1i (a1, a2);
+
+create index it2i1 on t2i (b1);
+create index it2i2 on t2i (b2);
+create index it2i3 on t2i (b1, b2);
+
+create index it3i1 on t3i (c1);
+create index it3i2 on t3i (c2);
+create index it3i3 on t3i (c1, c2);
+
+insert into t1i select * from t1;
+insert into t2i select * from t2;
+insert into t3i select * from t3;
+
# force the use of materialization
set @@optimizer_switch=no_semijoin;
@@ -31,6 +53,22 @@ set @@optimizer_switch=no_semijoin;
* Simple tests. *
******************************************************************************/
+# BUG#31639: Wrong plan for uncorrelated subquery when loose scan is applicable.
+explain extended
+select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
+select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1);
+
+prepare st1 from "explain select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
+execute st1;
+execute st1;
+prepare st2 from "select * from t1 where (a1, a2) in (select b1, max(b2) from t2i group by b1)";
+execute st2;
+execute st2;
+
+explain extended
+select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
+select * from t1 where (a1, a2) in (select b1, min(b2) from t2i where b1 > '0' group by b1);
+
# test re-optimization/re-execution with different execution methods
# prepare once, exec with different modes
@@ -44,21 +82,21 @@ set @@optimizer_switch=no_semijoin;
# materialize the result of subquery over temp-table view
-
+drop table t1, t2, t3, t1i, t2i, t3i;
+
/******************************************************************************
* Test the cache of the left operand of IN. *
******************************************************************************/
set @@optimizer_switch=no_semijoin;
# Test that default values of Cached_item are not used for comparison
-drop table if exists t1, t2;
create table t1 (s1 int);
create table t2 (s2 int);
insert into t1 values (5),(1),(0);
insert into t2 values (0), (1);
select s2 from t2 where s2 in (select s1 from t1);
+drop table t1, t2;
-drop table if exists t1, t2, t3;
create table t1 (a int not null, b int not null);
create table t2 (c int not null, d int not null);
create table t3 (e int not null);
diff -Nrup a/sql/item_subselect.cc b/sql/item_subselect.cc
--- a/sql/item_subselect.cc 2007-10-18 17:50:27 +03:00
+++ b/sql/item_subselect.cc 2007-10-25 12:06:50 +03:00
@@ -1798,8 +1798,16 @@ bool Item_in_subselect::setup_engine()
}
/* Initilizations done in runtime memory, repeated for each execution. */
- if (new_engine && (res= new_engine->init_runtime()))
- DBUG_RETURN(res);
+ if (new_engine)
+ {
+ /*
+ With materialization there is no limit on the materialized subquery.
+ Reset the limit that was set in Item_exists_subselect::fix_length_and_dec.
+ */
+ unit->global_parameters->select_limit= NULL;
+ if ((res= new_engine->init_runtime()))
+ DBUG_RETURN(res);
+ }
DBUG_RETURN(res);
}
@@ -3121,9 +3129,15 @@ int subselect_hash_sj_engine::exec()
*/
if (!is_materialized)
{
- if (materialize_join->optimize())
- DBUG_RETURN(TRUE);
+ int res= 0;
+ SELECT_LEX *save_select= thd->lex->current_select;
+ thd->lex->current_select= materialize_engine->select_lex;
+ if ((res= materialize_join->optimize()))
+ goto err;
materialize_join->exec();
+ if ((res= test(materialize_join->error || thd->is_fatal_error)))
+ goto err;
+
/*
TODO:
- Unlock all subquery tables as we don't need them. To implement this
@@ -3152,6 +3166,11 @@ int subselect_hash_sj_engine::exec()
tmp_param= &(item_in->unit->outer_select()->join->tmp_table_param);
if (tmp_param && !tmp_param->copy_field)
tmp_param= NULL;
+
+err:
+ thd->lex->current_select= save_select;
+ if (res)
+ DBUG_RETURN(res);
}
/*
diff -Nrup a/sql/sql_lex.cc b/sql/sql_lex.cc
--- a/sql/sql_lex.cc 2007-10-18 17:50:28 +03:00
+++ b/sql/sql_lex.cc 2007-10-25 12:06:51 +03:00
@@ -2007,16 +2007,24 @@ void st_select_lex::print_limit(THD *thd
{
SELECT_LEX_UNIT *unit= master_unit();
Item_subselect *item= unit->item;
- if (item && unit->global_parameters == this &&
- (item->substype() == Item_subselect::EXISTS_SUBS ||
- item->substype() == Item_subselect::IN_SUBS ||
- item->substype() == Item_subselect::ALL_SUBS))
+
+ if (item && unit->global_parameters == this)
{
- DBUG_ASSERT(!item->fixed ||
- select_limit->val_int() == LL(1) && offset_limit == 0);
- return;
+ Item_subselect::subs_type subs_type= item->substype();
+ if (subs_type == Item_subselect::EXISTS_SUBS ||
+ subs_type == Item_subselect::IN_SUBS ||
+ subs_type == Item_subselect::ALL_SUBS)
+ {
+ DBUG_ASSERT(!item->fixed ||
+ /* select_limit should be 1 only if not using materialization */
+ (((subs_type == Item_subselect::IN_SUBS) &&
+ ((Item_in_subselect*)item)->exec_method ==
+ Item_in_subselect::MATERIALIZATION) ?
+ TRUE : select_limit->val_int() == LL(1)) &&
+ offset_limit == 0);
+ return;
+ }
}
-
if (explicit_limit)
{
str->append(STRING_WITH_LEN(" limit "));
| Thread |
|---|
| • bk commit into 5.2 tree (timour:1.2616) BUG#31639 | timour | 25 Oct |
