revision-id: 8bcec84e8c5780a7c322357fd1deac75a5f2da70 (mariadb-10.4.4-25-g8bcec84e8c5) parent(s): ee4a2fef18136165a3267b4429e5921fc306cc20 author: Galina Shalygina committer: Galina Shalygina timestamp: 2019-04-19 00:03:14 +0300 message: MDEV-19269 Pushdown into IN subquery is not made on the second execution of stmt The bug occurs because is_jtbm_const_tab field is not reset after the first execution of statement. It remains in the second execution when pushdown into IN subquery is made. That’s why pushdown for the second execution of statement is not made. To fix it is_jtbm_const_tab is reset for each statement execution. --- mysql-test/main/in_subq_cond_pushdown.result | 77 ++++++++++++++++++++++++++++ mysql-test/main/in_subq_cond_pushdown.test | 24 +++++++++ sql/opt_subselect.cc | 1 + 3 files changed, 102 insertions(+) diff --git a/mysql-test/main/in_subq_cond_pushdown.result b/mysql-test/main/in_subq_cond_pushdown.result index eef320d2d04..53355fb7b4c 100644 --- a/mysql-test/main/in_subq_cond_pushdown.result +++ b/mysql-test/main/in_subq_cond_pushdown.result @@ -3887,3 +3887,80 @@ i1 2 1 DROP TABLE t1,t2,t3; +# +# MDEV-19269: pushdown into IN subquery is not made +# on the second execution of stmt +# +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (x int, y int); +INSERT INTO t1 VALUES (1,1),(2,2); +INSERT INTO t2 VALUES (1,1),(2,2),(2,3); +PREPARE stmt FROM " +EXPLAIN FORMAT=JSON +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE @a=1 GROUP BY t2.x);"; +set @a=2; +execute stmt; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE noticed after reading const tables" + }, + "subqueries": [ + { + "query_block": { + "select_id": 2, + "table": { + "message": "Impossible WHERE" + } + } + } + ] + } +} +set @a=1; +execute stmt; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t1.b = t1.a and t1.a is not null and t1.a is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["x", "COUNT(t2.y)"], + "ref": ["test.t1.a", "test.t1.a"], + "rows": 1, + "filtered": 100, + "attached_condition": "t1.a = `<subquery2>`.`COUNT(t2.y)`", + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`COUNT(t2.y)` = t2.x", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } + } + } +} +DROP TABLE t1,t2; diff --git a/mysql-test/main/in_subq_cond_pushdown.test b/mysql-test/main/in_subq_cond_pushdown.test index 7763201cda1..a4bcbaca97b 100644 --- a/mysql-test/main/in_subq_cond_pushdown.test +++ b/mysql-test/main/in_subq_cond_pushdown.test @@ -860,3 +860,27 @@ SELECT t3.i1 FROM t3 GROUP BY i1 HAVING t.i1 < 3)); DROP TABLE t1,t2,t3; + +--echo # +--echo # MDEV-19269: pushdown into IN subquery is not made +--echo # on the second execution of stmt +--echo # + +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (x int, y int); + +INSERT INTO t1 VALUES (1,1),(2,2); +INSERT INTO t2 VALUES (1,1),(2,2),(2,3); + +PREPARE stmt FROM " +EXPLAIN FORMAT=JSON +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE @a=1 GROUP BY t2.x);"; + +set @a=2; +execute stmt; +set @a=1; +execute stmt; + +DROP TABLE t1,t2; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 32b70b41eb3..d0fd8c5ee55 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -6028,6 +6028,7 @@ bool setup_degenerate_jtbm_semi_joins(JOIN *join, if ((subq_pred= table->jtbm_subselect)) { + subq_pred->is_jtbm_const_tab= FALSE; JOIN *subq_join= subq_pred->unit->first_select()->join; if (!subq_join->tables_list || !subq_join->table_count)