MDEV-18383: Missing rows with pushdown condition defined with IF-function

using Item_cond

This bug is similar to the bug MDEV-16765.
It appears because of the wrong pushdown into HAVING clause while this
pushdown shouldn't be made at all.
This happens because function that checks if Item_cond can be pushed
always returns that it can be pushed.

To fix it new method Item_cond::excl_dep_on_table() was added.

4 files changed, 61 insertions, 0 deletions

diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index 14c8e4d5e8f..00acdf241a6 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -10480,4 +10480,25 @@ EXPLAIN
 }
 DROP VIEW v1,v2;
 DROP TABLE t1;
+#
+# MDEV-18383: pushdown condition with the IF structure
+#             defined with Item_cond item
+#
+CREATE TABLE t1(a INT, b INT);
+CREATE TABLE t2(c INT, d INT);
+INSERT INTO t1 VALUES (1,2),(3,4),(5,6);
+INSERT INTO t2 VALUES (1,3),(3,7),(5,1);
+SELECT *
+FROM t1,
+(
+SELECT MAX(d) AS max_d,c
+FROM t2
+GROUP BY c
+) AS tab
+WHERE t1.a=tab.c AND
+IF(2,t1.a=1 OR t1.b>5,1=1);
+a	b	max_d	c
+1	2	3	1
+5	6	1	5
+DROP TABLE t1,t2;
 # End of 10.2 tests
diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test
index 25cb29e13db..313d77d2332 100644
--- a/mysql-test/t/derived_cond_pushdown.test
+++ b/mysql-test/t/derived_cond_pushdown.test
@@ -2102,4 +2102,26 @@ eval EXPLAIN FORMAT=JSON $q2;
 DROP VIEW v1,v2;
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-18383: pushdown condition with the IF structure
+--echo #             defined with Item_cond item
+--echo #
+
+CREATE TABLE t1(a INT, b INT);
+CREATE TABLE t2(c INT, d INT);
+INSERT INTO t1 VALUES (1,2),(3,4),(5,6);
+INSERT INTO t2 VALUES (1,3),(3,7),(5,1);
+
+SELECT *
+FROM t1,
+(
+  SELECT MAX(d) AS max_d,c
+  FROM t2
+  GROUP BY c
+) AS tab
+WHERE t1.a=tab.c AND
+      IF(2,t1.a=1 OR t1.b>5,1=1);
+
+DROP TABLE t1,t2;
+
 --echo # End of 10.2 tests
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 7357e57733a..badb0191471 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -4994,6 +4994,23 @@ Item *Item_cond::build_clone(THD *thd, MEM_ROOT *mem_root)
 }
 
 
+bool Item_cond::excl_dep_on_table(table_map tab_map)
+{
+  if (used_tables() & OUTER_REF_TABLE_BIT)
+    return false;
+  if (!(used_tables() & ~tab_map))
+    return true;
+  List_iterator_fast<Item> li(list);
+  Item *item;
+  while ((item= li++))
+  {
+    if (!item->excl_dep_on_table(tab_map))
+      return false;
+  }
+  return true;
+}
+
+
 bool Item_cond::excl_dep_on_grouping_fields(st_select_lex *sel)
 {
   List_iterator_fast<Item> li(list);
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 1b119c743d3..d083248f6cd 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -2229,6 +2229,7 @@ public:
                 Item_transformer transformer, uchar *arg_t);
   bool eval_not_null_tables(void *opt_arg);
   Item *build_clone(THD *thd, MEM_ROOT *mem_root);
+  bool excl_dep_on_table(table_map tab_map);
   bool excl_dep_on_grouping_fields(st_select_lex *sel);
 };