[Commits] 694d1a50bd7: MDEV-19185: Pushdown constant function defined with subquery

Galina galina.shalygina at mariadb.com
Fri Apr 5 22:55:21 EEST 2019


revision-id: 694d1a50bd7754c5357dde184f87d63b7032ee5e (mariadb-10.4.3-163-g694d1a50bd7)
parent(s): c84dde148f8d82232a110f9ff7c80df94d0d6c8c
author: Galina Shalygina
committer: Galina Shalygina
timestamp: 2019-04-05 22:55:20 +0300
message:

MDEV-19185: Pushdown constant function defined with subquery

The bug occurs because of the wrong pushdown of constant function
defined with subquery from HAVING into WHERE. Subqueries can't be
pushed into WHERE.

To fix it with_subquery() call is added to check if the function contains
subquery.

---
 mysql-test/main/having_cond_pushdown.result | 16 ++++++++++++++++
 mysql-test/main/having_cond_pushdown.test   | 17 +++++++++++++++++
 sql/item.cc                                 |  5 ++---
 sql/item_func.h                             |  2 +-
 4 files changed, 36 insertions(+), 4 deletions(-)

diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result
index bec2a937e38..e9fcd633895 100644
--- a/mysql-test/main/having_cond_pushdown.result
+++ b/mysql-test/main/having_cond_pushdown.result
@@ -4625,3 +4625,19 @@ a	MAX(t1.b)	c
 1	22	3
 deallocate prepare stmt1;
 DROP TABLE t1,t3;
+#
+# MDEV-19185: pushdown constant function with subquery
+#
+CREATE TABLE t1 (pk INT, c1 VARCHAR(64));
+INSERT INTO t1 VALUES (1,'bbb'),(2,'aaa'),(3,'ccc');
+CREATE VIEW v1 AS SELECT * FROM t1;
+SELECT pk
+FROM t1
+GROUP BY pk
+HAVING (1 NOT IN (SELECT COUNT(t1.c1) FROM (v1, t1)));
+pk
+1
+2
+3
+DROP TABLE t1;
+DROP VIEW v1;
diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test
index 1e5ad610e90..a50fa11484d 100644
--- a/mysql-test/main/having_cond_pushdown.test
+++ b/mysql-test/main/having_cond_pushdown.test
@@ -1301,3 +1301,20 @@ execute stmt1;
 deallocate prepare stmt1;
 
 DROP TABLE t1,t3;
+
+
+--echo #
+--echo # MDEV-19185: pushdown constant function with subquery
+--echo #
+
+CREATE TABLE t1 (pk INT, c1 VARCHAR(64));
+INSERT INTO t1 VALUES (1,'bbb'),(2,'aaa'),(3,'ccc');
+CREATE VIEW v1 AS SELECT * FROM t1;
+
+SELECT pk
+FROM t1
+GROUP BY pk
+HAVING (1 NOT IN (SELECT COUNT(t1.c1) FROM (v1, t1)));
+
+DROP TABLE t1;
+DROP VIEW v1;
diff --git a/sql/item.cc b/sql/item.cc
index 543dc971228..e511921b30b 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -9060,9 +9060,8 @@ bool Item_args::excl_dep_on_grouping_fields(st_select_lex *sel)
 {
   for (uint i= 0; i < arg_count; i++)
   {
-    if (args[i]->type() == Item::SUBSELECT_ITEM ||
-        (args[i]->type() == Item::FUNC_ITEM &&
-         ((Item_func *)args[i])->functype() == Item_func::UDF_FUNC))
+    if (args[i]->type() == Item::FUNC_ITEM &&
+        ((Item_func *)args[i])->functype() == Item_func::UDF_FUNC)
       return false;
     if (args[i]->const_item())
       continue;
diff --git a/sql/item_func.h b/sql/item_func.h
index a3bf4d78158..44e9691c9df 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -342,7 +342,7 @@ class Item_func :public Item_func_or_sum,
 
   bool excl_dep_on_grouping_fields(st_select_lex *sel)
   {
-    if (has_rand_bit())
+    if (has_rand_bit() || with_subquery())
       return false;
     return Item_args::excl_dep_on_grouping_fields(sel);
   }


More information about the commits mailing list