From 59784abeadc4014f357e44ae507c62dc93c641b3 Mon Sep 17 00:00:00 2001 From: unknown Date: Mon, 4 Jul 2011 14:51:16 +0300 Subject: Fix LP bug lp:802979 Analysis: This bug consists of two related problems that are result of too early evaluation of single-row subqueries during the optimization phase of the outer query. Several optimizer code paths try to evaluate single-row subqueries in order to produce a constant and use that constant for further optimzation. When the execution of the subquery peforms destructive changes to the representation of the subquery, and these changes are not anticipated by the subsequent optimization phases of the outer query, we tipically get a crash or failed assert. Specifically, in this bug the inner-most suqbuery with DISTINCT triggers a substitution of the original JOIN object by a single-table JOIN object with a temp table needed to perform the DISTINCT operation (created by JOIN::make_simple_join). This substitution breaks EXPLAIN because: a) in the first example JOIN::cleanup no longer can reach the original table of the innermost subquery, and close all indexes, and b) in this second test query, EXPLAIN attempts to print the name of the internal temp table, and crashes because the temp table has no name (NULL pointer instead). Solution: a) fully disable subquery evaluation during optimization in all cases - both for constant propagation and range optimization, and b) change JOIN::join_free() to perform cleanup irrespective of EXPLAIN or not. --- mysql-test/r/group_min_max.result | 6 ++-- mysql-test/r/sp.result | 6 ++-- mysql-test/r/subselect.result | 6 ++-- mysql-test/r/subselect4.result | 50 +++++++++++++++++++++++++++++-- mysql-test/r/subselect_no_mat.result | 6 ++-- mysql-test/r/subselect_no_opts.result | 6 ++-- mysql-test/r/subselect_no_semijoin.result | 6 ++-- mysql-test/t/subselect4.test | 42 ++++++++++++++++++++++++++ sql/opt_range.cc | 5 +++- sql/sql_select.cc | 7 ++--- 10 files changed, 115 insertions(+), 25 deletions(-) diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 0b3df689089..d3068543619 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2412,7 +2412,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) AND t1_outer1.b = t1_outer2.b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer1 ref a a 5 const 1 Using index +1 PRIMARY t1_outer1 ref a a 5 const 2 Using where; Using index 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index; Using join buffer (flat, BNL join) 2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x @@ -2749,7 +2749,7 @@ NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x Using where; Using index x x x x x x x x x Using where; Using index SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); MIN( a ) @@ -2821,7 +2821,7 @@ NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x Using where; Using index x x x x x x x x x Using where; Using index SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); MIN( a ) diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 50dd2ae7047..ffb2dd1819c 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -6420,16 +6420,16 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref c1 c1 5 const 1 Using index EXPLAIN SELECT * FROM t1 WHERE c1=f1(); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c1 c1 5 const 1 Using index +1 SIMPLE t1 ref c1 c1 5 const 0 Using index EXPLAIN SELECT * FROM v1 WHERE c1=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref c1 c1 5 const 1 Using index EXPLAIN SELECT * FROM v1 WHERE c1=f1(); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c1 c1 5 const 1 Using index +1 SIMPLE t1 ref c1 c1 5 const 0 Using index EXPLAIN SELECT * FROM t1 WHERE c1=f2(10); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 ref c1 c1 5 const 1 Using index +1 SIMPLE t1 ref c1 c1 5 const 0 Using index EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL c1 5 NULL 5 Using where; Using index diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index c84d4538a88..f6b5ec4d5c3 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -551,7 +551,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = 3)) +Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -1236,7 +1236,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using index condition +1 PRIMARY t1 ref salary salary 5 const 0 0.00 Using index condition 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`)) @@ -4030,7 +4030,7 @@ CREATE TABLE t1 (a int, b int, KEY (a)); INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 1 Using where; Using index +1 PRIMARY t1 ref a a 5 const 0 Using where; Using index 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 5a382730a99..ab2f1d6eec4 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -731,7 +731,7 @@ WHERE f3 = ( SELECT t1.f3 FROM t1 WHERE ( t1.f10 ) IN ( SELECT f11 FROM t2 GROUP BY f11 )); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY t1 ref f3 f3 5 const 0 Using index condition 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where 2 SUBQUERY eq_ref distinct_key distinct_key 5 test.t1.f10 1 3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary @@ -746,7 +746,7 @@ WHERE f3 = ( SELECT f3 FROM t1 WHERE ( f10, f10 ) IN ( SELECT f11, f11 FROM t2 GROUP BY f11 )); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +1 PRIMARY t1 ref f3 f3 5 const 0 Using index condition 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where 2 SUBQUERY eq_ref distinct_key distinct_key 10 test.t1.f10,test.t1.f10 1 3 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using temporary @@ -1736,3 +1736,49 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary drop table t1, t2, t3; +# +# LP BUG#802979 Assertion `table->key_read == 0' in close_thread_table +# +CREATE TABLE t1 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t1 VALUES (1,0),(5,0); +CREATE TABLE t2 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t2 VALUES (1,0),(5,0); +CREATE TABLE t3 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t3 VALUES (1,0),(5,0); +CREATE TABLE t4 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t4 VALUES (1,0),(5,0); +EXPLAIN +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 +WHERE EXISTS (SELECT DISTINCT f1 FROM t4)) +AND t2.f2 = t1.f1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref f1 f1 5 const 0 Using index condition +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index; Using temporary +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 +WHERE EXISTS (SELECT DISTINCT f1 FROM t4)) +AND t2.f2 = t1.f1; +ERROR 21000: Subquery returns more than 1 row +EXPLAIN +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 +WHERE EXISTS (SELECT DISTINCT f1 FROM t4) LIMIT 1) +AND t2.f2 = t1.f1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ref f1 f1 5 const 0 Using index condition +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +3 SUBQUERY t4 index NULL f1 5 NULL 2 Using index; Using temporary +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 +WHERE EXISTS (SELECT DISTINCT f1 FROM t4) LIMIT 1) +AND t2.f2 = t1.f1; +f1 f2 f1 f2 +drop table t1,t2,t3,t4; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index f9a4a4373c2..ceb76b1a522 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -555,7 +555,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = 3)) +Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -1240,7 +1240,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using index condition +1 PRIMARY t1 ref salary salary 5 const 0 0.00 Using index condition 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`)) @@ -4034,7 +4034,7 @@ CREATE TABLE t1 (a int, b int, KEY (a)); INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 1 Using where; Using index +1 PRIMARY t1 ref a a 5 const 0 Using where; Using index 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 493ab9dba59..4b2ef0ebbb4 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -552,7 +552,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = 3)) +Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -1237,7 +1237,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using index condition +1 PRIMARY t1 ref salary salary 5 const 0 0.00 Using index condition 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`)) @@ -4031,7 +4031,7 @@ CREATE TABLE t1 (a int, b int, KEY (a)); INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 1 Using where; Using index +1 PRIMARY t1 ref a a 5 const 0 Using where; Using index 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 434bcd33a21..c8deaa6811f 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -552,7 +552,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = 3)) +Note 1003 select 3 AS `numreponse` from `test`.`t1` where ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -1237,7 +1237,7 @@ create table t1 (id int not null auto_increment primary key, salary int, key(sal insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ref salary salary 5 const 1 100.00 Using index condition +1 PRIMARY t1 ref salary salary 5 const 0 0.00 Using index condition 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) from `test`.`t1`)) @@ -4031,7 +4031,7 @@ CREATE TABLE t1 (a int, b int, KEY (a)); INSERT INTO t1 VALUES (1,1),(2,1); EXPLAIN SELECT 1 FROM t1 WHERE a = (SELECT COUNT(*) FROM t1 GROUP BY b); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref a a 5 const 1 Using where; Using index +1 PRIMARY t1 ref a a 5 const 0 Using where; Using index 2 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort DROP TABLE t1; CREATE TABLE t1 (id int NOT NULL, st CHAR(2), INDEX idx(id)); diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index 39561d5114f..0daff56571d 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -1415,3 +1415,45 @@ WHERE t2.f1 = ( FROM t1)); drop table t1, t2, t3; + +--echo # +--echo # LP BUG#802979 Assertion `table->key_read == 0' in close_thread_table +--echo # + +CREATE TABLE t1 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t1 VALUES (1,0),(5,0); +CREATE TABLE t2 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t2 VALUES (1,0),(5,0); +CREATE TABLE t3 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t3 VALUES (1,0),(5,0); +CREATE TABLE t4 ( f1 int, f2 int , KEY (f1)) ; +INSERT IGNORE INTO t4 VALUES (1,0),(5,0); + +EXPLAIN +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 + WHERE EXISTS (SELECT DISTINCT f1 FROM t4)) + AND t2.f2 = t1.f1; + +-- error ER_SUBQUERY_NO_1_ROW +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 + WHERE EXISTS (SELECT DISTINCT f1 FROM t4)) + AND t2.f2 = t1.f1; + +EXPLAIN +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 + WHERE EXISTS (SELECT DISTINCT f1 FROM t4) LIMIT 1) + AND t2.f2 = t1.f1; + +SELECT * +FROM t1, t2 +WHERE t2.f2 = (SELECT f2 FROM t3 + WHERE EXISTS (SELECT DISTINCT f1 FROM t4) LIMIT 1) + AND t2.f2 = t1.f1; + +drop table t1,t2,t3,t4; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 3b672d6b704..88cd2a5e4cd 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -7292,7 +7292,7 @@ static SEL_TREE *get_mm_tree(RANGE_OPT_PARAM *param,COND *cond) case Item_func::MULT_EQUAL_FUNC: { Item_equal *item_equal= (Item_equal *) cond; - if (!(value= item_equal->get_const())) + if (!(value= item_equal->get_const()) || value->is_expensive()) DBUG_RETURN(0); Item_equal_fields_iterator it(*item_equal); ref_tables= value->used_tables(); @@ -7325,6 +7325,9 @@ static SEL_TREE *get_mm_tree(RANGE_OPT_PARAM *param,COND *cond) } else DBUG_RETURN(0); + if (value && value->is_expensive()) + DBUG_RETURN(0); + ftree= get_full_func_mm_tree(param, cond_func, field_item, value, inv); } diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b1aec0596ed..8092e5dc887 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -9620,8 +9620,7 @@ void JOIN::join_free() Optimization: if not EXPLAIN and we are done with the JOIN, free all tables. */ - bool full= (!(select_lex->uncacheable) && - !thd->lex->describe); + bool full= !(select_lex->uncacheable); bool can_unlock= full; DBUG_ENTER("JOIN::join_free"); @@ -11472,8 +11471,8 @@ propagate_cond_constants(THD *thd, I_List *save_list, { Item_func_eq *func=(Item_func_eq*) cond; Item **args= func->arguments(); - bool left_const= args[0]->const_item(); - bool right_const= args[1]->const_item(); + bool left_const= args[0]->const_item() && !args[0]->is_expensive(); + bool right_const= args[1]->const_item() && !args[1]->is_expensive(); if (!(left_const && right_const) && args[0]->result_type() == args[1]->result_type()) { -- cgit v1.2.1