From d0d0f88f2cd4da23c2c2da702da51fb533e7fb8a Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Sun, 6 Jan 2019 23:15:25 +0530 Subject: 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/t/union.test | 35 +++++++++++++++++++++++++++++++++++ 1 file changed, 35 insertions(+) (limited to 'mysql-test/t/union.test') 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; -- cgit v1.2.1