revision-id: d0d0f88f2cd4da23c2c2da702da51fb533e7fb8a (mariadb-10.0.37-40-gd0d0f88f2cd) parent(s): b87eb04f77234acdbee1e626338ea95b04f4db2e author: Varun Gupta committer: Varun Gupta timestamp: 2019-01-06 23:15:25 +0530 message: MDEV-13784: query causes seg fault When we have a nested subquery then a subquery that was a dependent subquery may change to an independent one when we optimizer the inner subqueries. This is handled st_select_lex::optimize_unflattened_subqueries. Currently a subquery that was changed to independent from dependent after optimization phase incorrectly shows dependent in the output of Explain, this happens because we don't update used_tables for the WHERE clause, ON clause, etc after the optimization phase. --- mysql-test/r/subselect_exists2in.result | 4 ++-- mysql-test/r/union.result | 38 +++++++++++++++++++++++++++++++++ mysql-test/r/view.result | 4 ++-- mysql-test/t/union.test | 35 ++++++++++++++++++++++++++++++ sql/sql_lex.cc | 1 + 5 files changed, 78 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/subselect_exists2in.result b/mysql-test/r/subselect_exists2in.result index d47e446fe8f..b6b2f5b476f 100644 --- a/mysql-test/r/subselect_exists2in.result +++ b/mysql-test/r/subselect_exists2in.result @@ -330,7 +330,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2 -Note 1003 select (select 1 from dual where (not(((1 is not null) and <in_optimizer>(1,1 in ( <materialize> (select `test`.`t3`.`c` from `test`.`t3` where (`test`.`t3`.`c` is not null) ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery3>`.`c`))))))))) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )` from `test`.`t1` +Note 1003 select (select 1 from dual where (not(((1 is not null) and <in_optimizer>(1,1 in (<primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery3>`.`c`))))))))) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )` from `test`.`t1` SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) 1 @@ -344,7 +344,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED t3 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2 -Note 1003 select (select 1 from dual where (not(((1 is not null) and <in_optimizer>(1,1 in ( <materialize> (select `test`.`t3`.`c` from `test`.`t3` where (`test`.`t3`.`c` is not null) ), <primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery3>`.`c`))))))))) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )` from `test`.`t1` +Note 1003 select (select 1 from dual where (not(((1 is not null) and <in_optimizer>(1,1 in (<primary_index_lookup>(1 in <temporary table> on distinct_key where ((1 = `<subquery3>`.`c`))))))))) AS `( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) )` from `test`.`t1` SELECT ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) FROM t1; ( SELECT b FROM t2 WHERE NOT EXISTS ( SELECT c FROM t3 WHERE c = b ) ) 1 diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 5ea0f975a91..9b7a361fdc5 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -2049,3 +2049,41 @@ a 1000003.0 1.0 End of 5.5 tests +# +# MDEV-13784: query causes seg fault +# +CREATE TABLE t1 (`bug_id` int NOT NULL PRIMARY KEY, `product_id` int NOT NULL); +INSERT INTO t1 VALUES (45199,1184); +CREATE TABLE t2 (`product_id` int NOT NULL,`userid` int NOT NULL, PRIMARY KEY (`product_id`,`userid`)); +INSERT INTO t2 VALUES (1184,103),(1184,624),(1184,1577),(1184,1582); +CREATE TABLE t3 (`id` int NOT NULL PRIMARY KEY,`name` varchar(64)); +CREATE TABLE t4 ( `userid` int NOT NULL PRIMARY KEY, `login_name` varchar(255)); +INSERT INTO t4 VALUES (103,'foo'),(624,'foo'),(1577,'foo'),(1582,'foo'); +CREATE TABLE t5 (`id` int NOT NULL PRIMARY KEY, `name` varchar(64)); +explain select +( +select login_name from t4 where userId = ( +select userid from t2 where product_id = t1.product_id +union +select userid from t2 where product_id = ( +select id from t5 where name = (select name from t3 where id = t1.product_id)) limit 1 ) +) as x from t1 where (t1.bug_id=45199); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 +2 SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 func 1 Using where +3 SUBQUERY t2 ref PRIMARY PRIMARY 4 const 3 Using index +4 UNION t2 ref PRIMARY PRIMARY 4 func 1 Using where; Using index +5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +6 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +select +( +select login_name from t4 where userId = ( +select userid from t2 where product_id = t1.product_id +union +select userid from t2 where product_id = ( +select id from t5 where name = (select name from t3 where id = t1.product_id)) limit 1 ) +) as x from t1 where (t1.bug_id=45199); +x +foo +drop table t1, t2, t3, t4, t5; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 4e3146052e9..3088704e911 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4633,7 +4633,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` where (not(<expr_cache><10,`test`.`t1`.`a`>(<in_optimizer>(10,<exists>(select NULL from `test`.`t4` where ((`test`.`t4`.`a` >= `test`.`t1`.`a`) and trigcond(((<cache>(10) = NULL) or <cache>(isnull(NULL))))) having trigcond(<is_not_null_test>(NULL))))))) +Note 1003 select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` where (not(<expr_cache><10,`test`.`t1`.`a`>(<in_optimizer>(10,<exists>(select NULL from `test`.`t4` where ((`test`.`t4`.`a` >= `test`.`t1`.`a`) and trigcond(((<cache>(10) = NULL) or 1))) having trigcond(<is_not_null_test>(NULL))))))) SELECT * FROM t1, t2 WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a) WHERE t4.a >= t1.a); @@ -4649,7 +4649,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1276 Field or reference 'v1.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` where (not(<expr_cache><10,`test`.`t1`.`a`>(<in_optimizer>(10,<exists>(select NULL from `test`.`t4` where ((`test`.`t4`.`a` >= `test`.`t1`.`a`) and trigcond(((<cache>(10) = NULL) or <cache>(isnull(NULL))))) having trigcond(<is_not_null_test>(NULL))))))) +Note 1003 select `test`.`t1`.`a` AS `a`,10 AS `a` from `test`.`t1` where (not(<expr_cache><10,`test`.`t1`.`a`>(<in_optimizer>(10,<exists>(select NULL from `test`.`t4` where ((`test`.`t4`.`a` >= `test`.`t1`.`a`) and trigcond(((<cache>(10) = NULL) or 1))) having trigcond(<is_not_null_test>(NULL))))))) SELECT * FROM v1, t2 WHERE t2.a NOT IN (SELECT t3.b FROM t3 RIGHT JOIN t4 ON (t4.a = t3.a) WHERE t4.a >= v1.a); diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 240115837c7..8ef8f7c4017 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -1437,3 +1437,38 @@ SET @advertAcctId = 1000003; select @advertAcctId as a from dual union all select 1.0 from dual; --echo End of 5.5 tests + +--echo # +--echo # MDEV-13784: query causes seg fault +--echo # + +CREATE TABLE t1 (`bug_id` int NOT NULL PRIMARY KEY, `product_id` int NOT NULL); +INSERT INTO t1 VALUES (45199,1184); + +CREATE TABLE t2 (`product_id` int NOT NULL,`userid` int NOT NULL, PRIMARY KEY (`product_id`,`userid`)); +INSERT INTO t2 VALUES (1184,103),(1184,624),(1184,1577),(1184,1582); + +CREATE TABLE t3 (`id` int NOT NULL PRIMARY KEY,`name` varchar(64)); + + +CREATE TABLE t4 ( `userid` int NOT NULL PRIMARY KEY, `login_name` varchar(255)); +INSERT INTO t4 VALUES (103,'foo'),(624,'foo'),(1577,'foo'),(1582,'foo'); +CREATE TABLE t5 (`id` int NOT NULL PRIMARY KEY, `name` varchar(64)); + +explain select +( + select login_name from t4 where userId = ( + select userid from t2 where product_id = t1.product_id + union + select userid from t2 where product_id = ( + select id from t5 where name = (select name from t3 where id = t1.product_id)) limit 1 ) +) as x from t1 where (t1.bug_id=45199); +select +( + select login_name from t4 where userId = ( + select userid from t2 where product_id = t1.product_id + union + select userid from t2 where product_id = ( + select id from t5 where name = (select name from t3 where id = t1.product_id)) limit 1 ) +) as x from t1 where (t1.bug_id=45199); +drop table t1, t2, t3, t4, t5; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 891cf9987c6..08c169c5999 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3551,6 +3551,7 @@ bool st_select_lex::optimize_unflattened_subqueries(bool const_only) inner_join->select_options|= SELECT_DESCRIBE; } res= inner_join->optimize(); + sl->update_used_tables(); sl->update_correlated_cache(); is_correlated_unit|= sl->is_correlated; inner_join->select_options= save_options;