[Commits] 8bcec84e8c5: MDEV-19269 Pushdown into IN subquery is not made on the second execution of stmt

Galina galina.shalygina at mariadb.com
Fri Apr 19 00:03:14 EEST 2019


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)


More information about the commits mailing list