MDEV-15579 Crash in Item_field::used_tables() called by

           Item::derived_field_transformer_for_having

The crash occurred due to an inappropriate handling of multiple equalities
when pushing conditions into materialized views/derived tables. If equalities
extracted from a multiple equality can be pushed into a materialized
view/derived table they should be plainly conjuncted with other pushed
predicates rather than form a separate AND sub-formula.

3 files changed, 211 insertions, 0 deletions

diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index c588a953a2c..e30bd4c4fe6 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -8871,3 +8871,162 @@ EXPLAIN
 }
 DROP TABLE t1,t2;
 SET  sql_mode = DEFAULT;
+#
+# MDEV-15579: incorrect removal of sub-formulas to be pushed
+#             into WHERE of materialized derived with GROUP BY
+#
+CREATE TABLE t1 (a INT, b INT, c INT, d INT);
+CREATE TABLE t2 (x INT, y INT, z INT);
+INSERT INTO t1 VALUES (1,1,66,1), (1,1,56,2), (3,2,42,3);
+INSERT INTO t2 VALUES (1,1,66), (1,12,32);
+SELECT *
+FROM t2,
+(
+SELECT a, b, max(c) AS max_c
+FROM t1
+GROUP BY a
+HAVING max_c > 37
+) AS v1
+WHERE (v1.a=1) AND (v1.b=v1.a) AND
+(v1.a=t2.x) AND (v1.max_c>30);
+x	y	z	a	b	max_c
+1	1	66	1	1	66
+1	12	32	1	1	66
+EXPLAIN SELECT *
+FROM t2,
+(
+SELECT a, b, max(c) AS max_c
+FROM t1
+GROUP BY a
+HAVING max_c > 37
+) AS v1
+WHERE (v1.a=1) AND (v1.b=v1.a) AND
+(v1.a=t2.x) AND (v1.max_c>30);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+EXPLAIN FORMAT=JSON SELECT *
+FROM t2,
+(
+SELECT a, b, max(c) AS max_c
+FROM t1
+GROUP BY a
+HAVING max_c > 37
+) AS v1
+WHERE (v1.a=1) AND (v1.b=v1.a) AND
+(v1.a=t2.x) AND (v1.max_c>30);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 2,
+      "filtered": 100,
+      "attached_condition": "t2.x = 1"
+    },
+    "block-nl-join": {
+      "table": {
+        "table_name": "<derived2>",
+        "access_type": "ALL",
+        "rows": 3,
+        "filtered": 100,
+        "attached_condition": "v1.a = 1 and v1.b = 1 and v1.max_c > 30"
+      },
+      "buffer_type": "flat",
+      "buffer_size": "256Kb",
+      "join_type": "BNL",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "having_condition": "max_c > 37 and max_c > 30 and t1.b = 1",
+          "table": {
+            "table_name": "t1",
+            "access_type": "ALL",
+            "rows": 3,
+            "filtered": 100,
+            "attached_condition": "t1.a = 1"
+          }
+        }
+      }
+    }
+  }
+}
+SELECT *
+FROM t2,
+(
+SELECT a, b, d, max(c) AS max_c
+FROM t1
+GROUP BY a,d
+HAVING max_c > 37
+) AS v1
+WHERE (v1.a=1) AND (v1.b=v1.a) AND (v1.b=v1.d) AND
+(v1.a=t2.x) AND (v1.max_c>30);
+x	y	z	a	b	d	max_c
+1	1	66	1	1	1	66
+1	12	32	1	1	1	66
+EXPLAIN SELECT *
+FROM t2,
+(
+SELECT a, b, d, max(c) AS max_c
+FROM t1
+GROUP BY a,d
+HAVING max_c > 37
+) AS v1
+WHERE (v1.a=1) AND (v1.b=v1.a) AND (v1.b=v1.d) AND
+(v1.a=t2.x) AND (v1.max_c>30);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+EXPLAIN FORMAT=JSON SELECT *
+FROM t2,
+(
+SELECT a, b, d, max(c) AS max_c
+FROM t1
+GROUP BY a,d
+HAVING max_c > 37
+) AS v1
+WHERE (v1.a=1) AND (v1.b=v1.a) AND (v1.b=v1.d) AND
+(v1.a=t2.x) AND (v1.max_c>30);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "t2",
+      "access_type": "ALL",
+      "rows": 2,
+      "filtered": 100,
+      "attached_condition": "t2.x = 1"
+    },
+    "block-nl-join": {
+      "table": {
+        "table_name": "<derived2>",
+        "access_type": "ALL",
+        "rows": 3,
+        "filtered": 100,
+        "attached_condition": "v1.a = 1 and v1.b = 1 and v1.d = 1 and v1.max_c > 30"
+      },
+      "buffer_type": "flat",
+      "buffer_size": "256Kb",
+      "join_type": "BNL",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "having_condition": "max_c > 37 and max_c > 30 and t1.b = 1",
+          "table": {
+            "table_name": "t1",
+            "access_type": "ALL",
+            "rows": 3,
+            "filtered": 100,
+            "attached_condition": "t1.a = 1 and t1.d = 1"
+          }
+        }
+      }
+    }
+  }
+}
+DROP TABLE t1,t2;
diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test
index 4ea6214075b..b1555fd7a02 100644
--- a/mysql-test/t/derived_cond_pushdown.test
+++ b/mysql-test/t/derived_cond_pushdown.test
@@ -1590,3 +1590,46 @@ eval EXPLAIN FORMAT=JSON $q;
 DROP TABLE t1,t2;
 
 SET  sql_mode = DEFAULT;
+
+--echo #
+--echo # MDEV-15579: incorrect removal of sub-formulas to be pushed
+--echo #             into WHERE of materialized derived with GROUP BY
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, c INT, d INT);
+CREATE TABLE t2 (x INT, y INT, z INT);
+
+INSERT INTO t1 VALUES (1,1,66,1), (1,1,56,2), (3,2,42,3);
+INSERT INTO t2 VALUES (1,1,66), (1,12,32);
+
+LET $query=
+SELECT *
+FROM t2,
+(
+  SELECT a, b, max(c) AS max_c
+  FROM t1
+  GROUP BY a
+  HAVING max_c > 37
+) AS v1
+WHERE (v1.a=1) AND (v1.b=v1.a) AND
+      (v1.a=t2.x) AND (v1.max_c>30);
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+LET $query=
+SELECT *
+FROM t2,
+(
+  SELECT a, b, d, max(c) AS max_c
+  FROM t1
+  GROUP BY a,d
+  HAVING max_c > 37
+) AS v1
+WHERE (v1.a=1) AND (v1.b=v1.a) AND (v1.b=v1.d) AND
+      (v1.a=t2.x) AND (v1.max_c>30);
+EVAL $query;
+EVAL EXPLAIN $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+DROP TABLE t1,t2;
diff --git a/sql/table.cc b/sql/table.cc
index b08ee380ec8..b36feadeac2 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -8207,6 +8207,7 @@ Item* TABLE_LIST::build_pushable_cond_for_table(THD *thd, Item *cond)
       return 0;		
     List_iterator<Item> li(*((Item_cond*) cond)->argument_list());
     Item *item;
+    bool is_fix_needed= false;
     while ((item=li++))
     {
       if (item->get_extraction_flag() == NO_EXTRACTION_FL)
@@ -8220,8 +8221,16 @@ Item* TABLE_LIST::build_pushable_cond_for_table(THD *thd, Item *cond)
 	return 0;
       if (!fix) 
 	continue;
+
+      if (fix->type() == Item::COND_ITEM &&
+          ((Item_cond*) fix)->functype() == Item_func::COND_AND_FUNC)
+	is_fix_needed= true;
+
       new_cond->argument_list()->push_back(fix, thd->mem_root);
     }
+    if (is_fix_needed)
+      new_cond->fix_fields(thd, 0);
+
     switch (new_cond->argument_list()->elements) 
     {
     case 0: