MDEV-16517: Server crash in Item_func_in::val_int() when

            IN predicate defined with non-constant values is pushed down

The problem appears because of wrong changes made in MDEV-16090 in the
Item_func_in::build_clone() method.
For the clone of the IN predicate it copied 'cmp_fields' array values
that become dirty after Item::cleanup_excluding_const_fields_processor
has worked in pushdown. That causes crash.
There is no need to copy 'cmp_fields' field, the array values should be
NULLs in order to fix_fields() for the cloned IN predicate can set them
correctly. fix_fields() computes values for 'cmp_fields' array only
if they were not set earlier.

3 files changed, 179 insertions, 1 deletions

diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result
index 83e70dd634c..a2e84a1e419 100644
--- a/mysql-test/r/derived_cond_pushdown.result
+++ b/mysql-test/r/derived_cond_pushdown.result
@@ -9669,3 +9669,142 @@ EXPLAIN
   }
 }
 DROP TABLE t1;
+#
+# MDEV-16517: pushdown condition with the IN predicate defined
+#             with non-constant values
+#
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,2),(1,3);
+SELECT * FROM
+(
+SELECT t1.a
+FROM t1
+WHERE 1 IN (0,t1.a)
+GROUP BY t1.a
+) AS dt1
+JOIN
+(
+SELECT t1.a
+FROM t1
+WHERE 1 IN (0,t1.a)
+) AS dt2
+ON dt1.a = dt2.a;
+a	a
+1	1
+1	1
+EXPLAIN FORMAT=JSON SELECT * FROM
+(
+SELECT t1.a
+FROM t1
+WHERE 1 IN (0,t1.a)
+GROUP BY t1.a
+) AS dt1
+JOIN
+(
+SELECT t1.a
+FROM t1
+WHERE 1 IN (0,t1.a)
+) AS dt2
+ON dt1.a = dt2.a;
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 2,
+      "filtered": 100,
+      "attached_condition": "1 in (0,dt1.a)",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "filesort": {
+            "sort_key": "t1.a",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 2,
+                "filtered": 100,
+                "attached_condition": "1 in (0,t1.a) and 1 in (0,t1.a)"
+              }
+            }
+          }
+        }
+      }
+    },
+    "block-nl-join": {
+      "table": {
+        "table_name": "t1",
+        "access_type": "ALL",
+        "rows": 2,
+        "filtered": 100
+      },
+      "buffer_type": "flat",
+      "buffer_size": "256Kb",
+      "join_type": "BNL",
+      "attached_condition": "t1.a = dt1.a"
+    }
+  }
+}
+SELECT * FROM
+(
+SELECT t1.a,MAX(t1.b)
+FROM t1
+GROUP BY t1.a
+) AS dt, t1
+WHERE dt.a=t1.a AND dt.a IN (1,t1.a);
+a	MAX(t1.b)	a	b
+1	3	1	2
+1	3	1	3
+EXPLAIN FORMAT=JSON SELECT * FROM
+(
+SELECT t1.a,MAX(t1.b)
+FROM t1
+GROUP BY t1.a
+) AS dt, t1
+WHERE dt.a=t1.a AND dt.a IN (1,t1.a);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "table": {
+      "table_name": "<derived2>",
+      "access_type": "ALL",
+      "rows": 2,
+      "filtered": 100,
+      "attached_condition": "dt.a in (1,dt.a)",
+      "materialized": {
+        "query_block": {
+          "select_id": 2,
+          "filesort": {
+            "sort_key": "t1.a",
+            "temporary_table": {
+              "table": {
+                "table_name": "t1",
+                "access_type": "ALL",
+                "rows": 2,
+                "filtered": 100,
+                "attached_condition": "t1.a in (1,t1.a)"
+              }
+            }
+          }
+        }
+      }
+    },
+    "block-nl-join": {
+      "table": {
+        "table_name": "t1",
+        "access_type": "ALL",
+        "rows": 2,
+        "filtered": 100
+      },
+      "buffer_type": "flat",
+      "buffer_size": "256Kb",
+      "join_type": "BNL",
+      "attached_condition": "t1.a = dt.a"
+    }
+  }
+}
+DROP TABLE t1;
diff --git a/mysql-test/t/derived_cond_pushdown.test b/mysql-test/t/derived_cond_pushdown.test
index 718140d3a77..3103ddb5c55 100644
--- a/mysql-test/t/derived_cond_pushdown.test
+++ b/mysql-test/t/derived_cond_pushdown.test
@@ -1801,3 +1801,42 @@ EVAL $query;
 EVAL EXPLAIN FORMAT=JSON $query;
 
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-16517: pushdown condition with the IN predicate defined
+--echo #             with non-constant values
+--echo #
+
+CREATE TABLE t1 (a INT, b INT);
+INSERT INTO t1 VALUES (1,2),(1,3);
+
+LET $query=
+SELECT * FROM
+(
+  SELECT t1.a
+  FROM t1
+  WHERE 1 IN (0,t1.a)
+  GROUP BY t1.a
+) AS dt1
+JOIN
+(
+  SELECT t1.a
+  FROM t1
+  WHERE 1 IN (0,t1.a)
+) AS dt2
+ON dt1.a = dt2.a;
+EVAL $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+LET $query=
+SELECT * FROM
+(
+  SELECT t1.a,MAX(t1.b)
+  FROM t1
+  GROUP BY t1.a
+) AS dt, t1
+WHERE dt.a=t1.a AND dt.a IN (1,t1.a);
+EVAL $query;
+EVAL EXPLAIN FORMAT=JSON $query;
+
+DROP TABLE t1;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index b86c0079bce..f176a0a8193 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -4438,7 +4438,7 @@ Item *Item_func_in::build_clone(THD *thd, MEM_ROOT *mem_root)
   {
     if (array && clone->create_array(thd))
         return NULL;
-    memcpy(&clone->cmp_items, &cmp_items, sizeof(cmp_items));
+    bzero(&clone->cmp_items, sizeof(cmp_items));
   }
   return clone;
 }