revision-id: de722585c1b2ad2af99bd1749e8566ea77838764 (mariadb-5.5.56-219-gde722585c1b) parent(s): 1ada4afb0a51f7283b6187a95019ec2cb80c8a0b author: Varun Gupta committer: Varun Gupta timestamp: 2018-05-24 03:09:42 +0530 message: MDEV-16225: wrong resultset from query with semijoin=on For non-semi-join subquery optimization we do a cost based decision between Materialisation and IN -> EXIST transformation. The issue in this case is that for IN->EXIST transformation we run JOIN::reoptimize with the IN->EXISt conditions and we come up with a new query plan. But when we compare the cost with Materialization, we make the decision to chose Materialization so we need to restore the query plan for Materilization. The restoring of query plan is not handled correctly. --- mysql-test/r/subselect_sj2_mat.result | 57 +++++++++++++++++++++++++++++++++++ mysql-test/t/subselect_sj2_mat.test | 42 ++++++++++++++++++++++++++ sql/sql_select.cc | 16 ++++++++++ 3 files changed, 115 insertions(+) diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result index 835742a3ff4..19aa3bd3f02 100644 --- a/mysql-test/r/subselect_sj2_mat.result +++ b/mysql-test/r/subselect_sj2_mat.result @@ -1601,3 +1601,60 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 11 func 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where DROP TABLE t1,t2; +# +# MDEV-16225: wrong resultset from query with semijoin=on +# +CREATE TABLE t1 ( +`id` int(10) NOT NULL AUTO_INCREMENT, +`local_name` varchar(64) NOT NULL, +PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1; +insert into t1(`id`,`local_name`) values +(1,'Cash Advance'), +(2,'Cash Advance'), +(3,'Rollover'), +(4,'AL Installment'), +(5,'AL Installment'), +(6,'AL Installment'), +(7,'AL Installment'), +(8,'AL Installment'), +(9,'AL Installment'), +(10,'Internet Payday'), +(11,'Rollover - Internet Payday'), +(12,'AL Monthly Installment'), +(13,'AL Semi-Monthly Installment'); +explain +SELECT SQL_NO_CACHE t.id +FROM t1 t +WHERE ( +t.id IN (SELECT A.id FROM t1 AS A WHERE A.local_name IN (SELECT B.local_name FROM t1 AS B WHERE B.id IN (0,4,12,13,1,10,3,11))) +OR +(t.id IN (0,4,12,13,1,10,3,11)) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t index PRIMARY PRIMARY 4 NULL 13 Using where; Using index +2 MATERIALIZED <subquery3> ALL distinct_key NULL NULL NULL 8 +2 MATERIALIZED A ALL PRIMARY NULL NULL NULL 13 Using where; Using join buffer (flat, BNL join) +3 MATERIALIZED B ALL PRIMARY NULL NULL NULL 13 Using where +SELECT SQL_NO_CACHE t.id +FROM t1 t +WHERE ( +t.id IN (SELECT A.id FROM t1 AS A WHERE A.local_name IN (SELECT B.local_name FROM t1 AS B WHERE B.id IN (0,4,12,13,1,10,3,11))) +OR +(t.id IN (0,4,12,13,1,10,3,11)) +); +id +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +12 +13 +drop table t1; diff --git a/mysql-test/t/subselect_sj2_mat.test b/mysql-test/t/subselect_sj2_mat.test index cfb6c8c2819..0665cdf68fe 100644 --- a/mysql-test/t/subselect_sj2_mat.test +++ b/mysql-test/t/subselect_sj2_mat.test @@ -303,3 +303,45 @@ eval $q; eval explain $q; DROP TABLE t1,t2; + +--echo # +--echo # MDEV-16225: wrong resultset from query with semijoin=on +--echo # + +CREATE TABLE t1 ( + `id` int(10) NOT NULL AUTO_INCREMENT, + `local_name` varchar(64) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1; + +insert into t1(`id`,`local_name`) values +(1,'Cash Advance'), +(2,'Cash Advance'), +(3,'Rollover'), +(4,'AL Installment'), +(5,'AL Installment'), +(6,'AL Installment'), +(7,'AL Installment'), +(8,'AL Installment'), +(9,'AL Installment'), +(10,'Internet Payday'), +(11,'Rollover - Internet Payday'), +(12,'AL Monthly Installment'), +(13,'AL Semi-Monthly Installment'); + +explain +SELECT SQL_NO_CACHE t.id +FROM t1 t +WHERE ( + t.id IN (SELECT A.id FROM t1 AS A WHERE A.local_name IN (SELECT B.local_name FROM t1 AS B WHERE B.id IN (0,4,12,13,1,10,3,11))) + OR + (t.id IN (0,4,12,13,1,10,3,11)) +); +SELECT SQL_NO_CACHE t.id +FROM t1 t +WHERE ( + t.id IN (SELECT A.id FROM t1 AS A WHERE A.local_name IN (SELECT B.local_name FROM t1 AS B WHERE B.id IN (0,4,12,13,1,10,3,11))) + OR + (t.id IN (0,4,12,13,1,10,3,11)) +); +drop table t1; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d6d269a700f..b8d43b9797e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -23805,6 +23805,22 @@ void JOIN::restore_query_plan(Join_plan_state *restore_from) } } + else + { + /* + After running JOIN::reoptimize for IN->EXISTS + transformation we might add elements to the keyuse array, + The original query plan was that the keyuse array had no elements, so we + should restore that state here. + */ + while(keyuse.elements) + delete_dynamic_element(&keyuse, 0); + for (uint i= 0; i < table_count; i++) + { + join_tab[i].keyuse= NULL; + join_tab[i].checked_keys.clear_all(); + } + } memcpy((uchar*) best_positions, (uchar*) restore_from->best_positions, sizeof(POSITION) * (table_count + 1)); /* Restore SJM nests */