From aab970f5e16a1dbb308cfc13d47d3b863143811f Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Fri, 19 Aug 2011 21:02:05 -0700 Subject: Fixed LP bug #826279. When the WHERE/HAVING condition of a subquery has been transformed by the optimizer the pointer stored the 'where'/'having' field of the SELECT_LEX structure used for the subquery must be updated accordingly. Otherwise the pointer may refer to an invalid item. This can lead to the reported assertion failure for some queries with correlated subqueries --- mysql-test/r/subselect.result | 25 +++++++++++++++++++++++++ mysql-test/r/subselect_no_mat.result | 25 +++++++++++++++++++++++++ mysql-test/r/subselect_no_opts.result | 25 +++++++++++++++++++++++++ mysql-test/r/subselect_no_semijoin.result | 25 +++++++++++++++++++++++++ mysql-test/r/subselect_scache.result | 25 +++++++++++++++++++++++++ mysql-test/t/subselect.test | 26 ++++++++++++++++++++++++++ sql/sql_select.cc | 14 ++++++++++++-- 7 files changed, 163 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index ccd1b537e95..f5d6fea60fc 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -5338,4 +5338,29 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY t2 range f1_key f1_key 4 NULL 6 Range checked for each record (index map: 0x1); Using temporary DROP TABLE t1,t2; +# +# LP bug #826279: assertion failure with GROUP BY a result of subquery +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (0), (0); +CREATE TABLE t2 (a int, b int, c int); +INSERT INTO t2 VALUES (10,7,0), (0,7,0); +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (10,7), (0,7); +SELECT SUM(DISTINCT b), +(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0 +WHERE t.a != 0 AND t2.a != 0) +FROM (SELECT * FROM t3) AS t +GROUP BY 2; +SUM(DISTINCT b) (SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0 +WHERE t.a != 0 AND t2.a != 0) +7 NULL +SELECT SUM(DISTINCT b), +(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1) +FROM (SELECT * FROM t3) AS t +GROUP BY 2; +SUM(DISTINCT b) (SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1) +7 NULL +7 10 +DROP TABLE t1,t2,t3; set optimizer_switch=@subselect_tmp; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index b1afa983b1f..cecc74514c2 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -5343,6 +5343,31 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY t2 range f1_key f1_key 4 NULL 6 Range checked for each record (index map: 0x1); Using temporary DROP TABLE t1,t2; +# +# LP bug #826279: assertion failure with GROUP BY a result of subquery +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (0), (0); +CREATE TABLE t2 (a int, b int, c int); +INSERT INTO t2 VALUES (10,7,0), (0,7,0); +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (10,7), (0,7); +SELECT SUM(DISTINCT b), +(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0 +WHERE t.a != 0 AND t2.a != 0) +FROM (SELECT * FROM t3) AS t +GROUP BY 2; +SUM(DISTINCT b) (SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0 +WHERE t.a != 0 AND t2.a != 0) +7 NULL +SELECT SUM(DISTINCT b), +(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1) +FROM (SELECT * FROM t3) AS t +GROUP BY 2; +SUM(DISTINCT b) (SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1) +7 NULL +7 10 +DROP TABLE t1,t2,t3; set optimizer_switch=@subselect_tmp; set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index b1575e46f10..e0604485efb 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -5339,5 +5339,30 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY t2 range f1_key f1_key 4 NULL 6 Range checked for each record (index map: 0x1); Using temporary DROP TABLE t1,t2; +# +# LP bug #826279: assertion failure with GROUP BY a result of subquery +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (0), (0); +CREATE TABLE t2 (a int, b int, c int); +INSERT INTO t2 VALUES (10,7,0), (0,7,0); +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (10,7), (0,7); +SELECT SUM(DISTINCT b), +(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0 +WHERE t.a != 0 AND t2.a != 0) +FROM (SELECT * FROM t3) AS t +GROUP BY 2; +SUM(DISTINCT b) (SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0 +WHERE t.a != 0 AND t2.a != 0) +7 NULL +SELECT SUM(DISTINCT b), +(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1) +FROM (SELECT * FROM t3) AS t +GROUP BY 2; +SUM(DISTINCT b) (SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1) +7 NULL +7 10 +DROP TABLE t1,t2,t3; set optimizer_switch=@subselect_tmp; set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 1feaee69bc1..17a4c16531d 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -5339,5 +5339,30 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY t2 range f1_key f1_key 4 NULL 6 Range checked for each record (index map: 0x1); Using temporary DROP TABLE t1,t2; +# +# LP bug #826279: assertion failure with GROUP BY a result of subquery +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (0), (0); +CREATE TABLE t2 (a int, b int, c int); +INSERT INTO t2 VALUES (10,7,0), (0,7,0); +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (10,7), (0,7); +SELECT SUM(DISTINCT b), +(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0 +WHERE t.a != 0 AND t2.a != 0) +FROM (SELECT * FROM t3) AS t +GROUP BY 2; +SUM(DISTINCT b) (SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0 +WHERE t.a != 0 AND t2.a != 0) +7 NULL +SELECT SUM(DISTINCT b), +(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1) +FROM (SELECT * FROM t3) AS t +GROUP BY 2; +SUM(DISTINCT b) (SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1) +7 NULL +7 10 +DROP TABLE t1,t2,t3; set optimizer_switch=@subselect_tmp; set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_scache.result b/mysql-test/r/subselect_scache.result index c37e8cb0657..9405482570b 100644 --- a/mysql-test/r/subselect_scache.result +++ b/mysql-test/r/subselect_scache.result @@ -5342,6 +5342,31 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY table2 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (flat, BNL join) 2 DEPENDENT SUBQUERY t2 range f1_key f1_key 4 NULL 6 Range checked for each record (index map: 0x1); Using temporary DROP TABLE t1,t2; +# +# LP bug #826279: assertion failure with GROUP BY a result of subquery +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (0), (0); +CREATE TABLE t2 (a int, b int, c int); +INSERT INTO t2 VALUES (10,7,0), (0,7,0); +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (10,7), (0,7); +SELECT SUM(DISTINCT b), +(SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0 +WHERE t.a != 0 AND t2.a != 0) +FROM (SELECT * FROM t3) AS t +GROUP BY 2; +SUM(DISTINCT b) (SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0 +WHERE t.a != 0 AND t2.a != 0) +7 NULL +SELECT SUM(DISTINCT b), +(SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1) +FROM (SELECT * FROM t3) AS t +GROUP BY 2; +SUM(DISTINCT b) (SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1) +7 NULL +7 10 +DROP TABLE t1,t2,t3; set optimizer_switch=@subselect_tmp; set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 895707597fb..6c460af6ae5 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -4590,4 +4590,30 @@ eval explain $query; DROP TABLE t1,t2; +--echo # +--echo # LP bug #826279: assertion failure with GROUP BY a result of subquery +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (0), (0); + +CREATE TABLE t2 (a int, b int, c int); +INSERT INTO t2 VALUES (10,7,0), (0,7,0); + +CREATE TABLE t3 (a int, b int); +INSERT INTO t3 VALUES (10,7), (0,7); + +SELECT SUM(DISTINCT b), + (SELECT t2.a FROM t1 JOIN t2 ON t2.c != 0 + WHERE t.a != 0 AND t2.a != 0) + FROM (SELECT * FROM t3) AS t +GROUP BY 2; + +SELECT SUM(DISTINCT b), + (SELECT t2.a FROM t1,t2 WHERE t.a != 0 or 1=2 LIMIT 1) + FROM (SELECT * FROM t3) AS t +GROUP BY 2; + +DROP TABLE t1,t2,t3; + set optimizer_switch=@subselect_tmp; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index e0b228fc45a..20b0b1a60f2 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -965,13 +965,16 @@ JOIN::optimize() sel->prep_where= conds ? conds->copy_andor_structure(thd) : 0; + sel->where= conds; + if (arena) thd->restore_active_arena(arena, &backup); } inject_jtbm_conds(this, join_list, &conds); - conds= optimize_cond(this, conds, join_list, &cond_value, &cond_equal); + conds= optimize_cond(this, conds, join_list, &cond_value, &cond_equal); + if (thd->is_error()) { error= 1; @@ -988,10 +991,17 @@ JOIN::optimize() DBUG_RETURN(1); } if (select_lex->where) + { select_lex->cond_value= cond_value; + if (sel->where != conds && cond_value == Item::COND_OK) + thd->change_item_tree(&sel->where, conds); + } if (select_lex->having) + { select_lex->having_value= having_value; - + if (sel->having != having && having_value == Item::COND_OK) + thd->change_item_tree(&sel->having, having); + } if (cond_value == Item::COND_FALSE || having_value == Item::COND_FALSE || (!unit->select_limit_cnt && !(select_options & OPTION_FOUND_ROWS))) { /* Impossible cond */ -- cgit v1.2.1