revision-id: bed6eb20889f9f5274818c323b4b386fda3ccb6b (mariadb-10.4.4-25-gbed6eb20889) parent(s): ee4a2fef18136165a3267b4429e5921fc306cc20 author: Galina Shalygina committer: Galina Shalygina timestamp: 2019-04-17 19:58:29 +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 | 25 ++++++++++++++++++++++++ mysql-test/main/in_subq_cond_pushdown.test | 29 ++++++++++++++++++++++++++++ sql/opt_subselect.cc | 1 + 3 files changed, 55 insertions(+) diff --git a/mysql-test/main/in_subq_cond_pushdown.result b/mysql-test/main/in_subq_cond_pushdown.result index eef320d2d04..5a5b52f73d4 100644 --- a/mysql-test/main/in_subq_cond_pushdown.result +++ b/mysql-test/main/in_subq_cond_pushdown.result @@ -3887,3 +3887,28 @@ 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 " +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);"; +EXECUTE stmt; +a b +EXECUTE stmt; +a b +PREPARE stmt FROM " +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 GROUP BY t2.x HAVING 1=2);"; +EXECUTE stmt; +a b +EXECUTE stmt; +a b +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..91423f6f053 100644 --- a/mysql-test/main/in_subq_cond_pushdown.test +++ b/mysql-test/main/in_subq_cond_pushdown.test @@ -860,3 +860,32 @@ 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 " +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);"; + +EXECUTE stmt; +EXECUTE stmt; + +PREPARE stmt FROM " +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 GROUP BY t2.x HAVING 1=2);"; + +EXECUTE stmt; +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)