MDEV-28620, MDEV-28621, MDEV-28622 Server crash in /sql/item_subselect.cc *bb-10.3-MDEV-28622

            Optimizer has eliminated subquery for various reasons.
            Attempting to walk or access Item_subselect (and descendents)
            values causes issues for these removed Items.  Fixed by setting
            either fixed_const, null_value or checking eliminated.

6 files changed, 228 insertions, 29 deletions

diff --git a/mysql-test/main/subselect_elimination.result b/mysql-test/main/subselect_elimination.result
new file mode 100644
index 00000000000..e065ba90941
--- /dev/null
+++ b/mysql-test/main/subselect_elimination.result
@@ -0,0 +1,75 @@
+drop table if exists t1,t2,t3;
+#
+# MDEV-28437: Assertion `!eliminated' failed in Item_subselect::exec
+#
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (3),(4);
+SELECT 1 IN (SELECT a FROM t1 LEFT JOIN t2 ON (a = b AND EXISTS (SELECT * FROM t1)));
+1 IN (SELECT a FROM t1 LEFT JOIN t2 ON (a = b AND EXISTS (SELECT * FROM t1)))
+1
+drop table t1,t2;
+#
+# MDEV-28622 Server crash in /sql/item_subselect.cc:2996 in Item_exists_subselect::exists2in_processor(void*)
+#
+CREATE TABLE t1 (  a int) ;
+CREATE VIEW v1 (i) AS SELECT EXISTS(SELECT 1) FROM t1;
+SELECT 1 FROM v1 WHERE i NOT IN (SELECT i = 0 FROM v1 WHERE i = -1 GROUP BY i);
+1
+SELECT 1 FROM v1 WHERE i IN (SELECT i = 0 FROM v1 WHERE i = -1 GROUP BY i);
+1
+DROP VIEW v1;
+SELECT 1 FROM (SELECT EXISTS(SELECT 1) i FROM t1)dt
+WHERE 1 not in (SELECT i+1 FROM t1 where i=4 group by i );
+1
+DROP TABLE t1;
+SELECT  exists(SELECT i+1 FROM (SELECT EXISTS(SELECT 1) i FROM (select 1)t)dt where i=3 group by i );
+exists(SELECT i+1 FROM (SELECT EXISTS(SELECT 1) i FROM (select 1)t)dt where i=3 group by i )
+0
+#
+# MDEV-28621 Server crash in /sql/item_subselect.cc:812 in Item_subselect::get_cache_parameters(List<Item>&)
+#
+CREATE TABLE t1 (i int) ;
+INSERT INTO t1 VALUES (1),(2),(3);
+SELECT 1 FROM t1
+WHERE i in
+(	SELECT  a+1
+FROM
+(SELECT (SELECT i FROM (SELECT 1 FROM t1) dt) AS a FROM t1) dt2
+GROUP BY a
+);
+1
+SELECT 1 FROM t1
+WHERE i in
+(	SELECT  a+1
+FROM
+(SELECT (SELECT 1 FROM t1) AS a FROM t1) dt2
+GROUP BY a
+);
+1
+DROP TABLE t1;
+#
+# MDEV-28620 Server crash in /sql/item_subselect.cc:812 in Item_subselect::get_cache_parameters(List<Item>&)
+#
+CREATE TABLE t1 ( a int);
+INSERT INTO t1 VALUES (1);
+SELECT EXISTS
+( SELECT 1 FROM t1 GROUP BY 1 IN (SELECT a FROM t1)
+ORDER BY a + (SELECT 1 FROM t1 WHERE (1,2) NOT IN (SELECT 1,0))
+);
+EXISTS
+( SELECT 1 FROM t1 GROUP BY 1 IN (SELECT a FROM t1)
+ORDER BY a + (SELECT 1 FROM t1 WHERE (1,2) NOT IN (SELECT 1,0))
+)
+1
+SELECT EXISTS
+( SELECT 1 FROM t1 GROUP BY (SELECT a FROM t1)
+ORDER BY a + (SELECT 1 FROM t1 WHERE (1,2) NOT IN (SELECT 1,0))
+);
+EXISTS
+( SELECT 1 FROM t1 GROUP BY (SELECT a FROM t1)
+ORDER BY a + (SELECT 1 FROM t1 WHERE (1,2) NOT IN (SELECT 1,0))
+)
+1
+drop table if exists t1;
diff --git a/mysql-test/main/subselect_elimination.test b/mysql-test/main/subselect_elimination.test
new file mode 100644
index 00000000000..e84e5c28627
--- /dev/null
+++ b/mysql-test/main/subselect_elimination.test
@@ -0,0 +1,86 @@
+-- source include/have_innodb.inc
+--disable_warnings
+drop table if exists t1,t2,t3;
+--enable_warnings
+
+--echo #
+--echo # MDEV-28437: Assertion `!eliminated' failed in Item_subselect::exec
+--echo #
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (3),(4);
+#enable after fix MDEV-27871
+--disable_view_protocol
+SELECT 1 IN (SELECT a FROM t1 LEFT JOIN t2 ON (a = b AND EXISTS (SELECT * FROM t1)));
+--enable_view_protocol
+drop table t1,t2;
+
+# End of 10.2 tests
+
+--echo #
+--echo # MDEV-28622 Server crash in /sql/item_subselect.cc:2996 in Item_exists_subselect::exists2in_processor(void*)
+--echo #
+# this & below and MDEV-28437 above are related, neither have anything to do with innodb, perhaps we should shift them elsewhere.
+
+CREATE TABLE t1 (  a int) ;
+CREATE VIEW v1 (i) AS SELECT EXISTS(SELECT 1) FROM t1;
+
+SELECT 1 FROM v1 WHERE i NOT IN (SELECT i = 0 FROM v1 WHERE i = -1 GROUP BY i);
+SELECT 1 FROM v1 WHERE i IN (SELECT i = 0 FROM v1 WHERE i = -1 GROUP BY i);
+DROP VIEW v1;
+
+SELECT 1 FROM (SELECT EXISTS(SELECT 1) i FROM t1)dt
+	WHERE 1 not in (SELECT i+1 FROM t1 where i=4 group by i );
+
+DROP TABLE t1;
+
+SELECT  exists(SELECT i+1 FROM (SELECT EXISTS(SELECT 1) i FROM (select 1)t)dt where i=3 group by i );
+
+--echo #
+--echo # MDEV-28621 Server crash in /sql/item_subselect.cc:812 in Item_subselect::get_cache_parameters(List<Item>&)
+--echo #
+
+
+
+CREATE TABLE t1 (i int) ;
+INSERT INTO t1 VALUES (1),(2),(3);
+
+SELECT 1 FROM t1
+WHERE i in
+(	SELECT  a+1
+	FROM
+ 	(SELECT (SELECT i FROM (SELECT 1 FROM t1) dt) AS a FROM t1) dt2
+ 	GROUP BY a
+);
+
+SELECT 1 FROM t1
+WHERE i in
+(	SELECT  a+1
+	FROM
+ 	(SELECT (SELECT 1 FROM t1) AS a FROM t1) dt2
+ 	GROUP BY a
+);
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-28620 Server crash in /sql/item_subselect.cc:812 in Item_subselect::get_cache_parameters(List<Item>&)
+--echo #
+
+CREATE TABLE t1 ( a int);
+INSERT INTO t1 VALUES (1);
+
+SELECT EXISTS
+( SELECT 1 FROM t1 GROUP BY 1 IN (SELECT a FROM t1)
+	ORDER BY a + (SELECT 1 FROM t1 WHERE (1,2) NOT IN (SELECT 1,0))
+);
+
+SELECT EXISTS
+( SELECT 1 FROM t1 GROUP BY (SELECT a FROM t1)
+	ORDER BY a + (SELECT 1 FROM t1 WHERE (1,2) NOT IN (SELECT 1,0))
+);
+
+drop table if exists t1;
+
+# End of 10.3 tests
diff --git a/mysql-test/main/subselect_innodb.result b/mysql-test/main/subselect_innodb.result
index fd75cce00b2..4796245f8e3 100644
--- a/mysql-test/main/subselect_innodb.result
+++ b/mysql-test/main/subselect_innodb.result
@@ -661,14 +661,3 @@ group by (select a),(select 1)
 );
 1
 drop table t1;
-#
-# MDEV-28437: Assertion `!eliminated' failed in Item_subselect::exec
-#
-CREATE TABLE t1 (a INT) ENGINE=InnoDB;
-INSERT INTO t1 VALUES (1),(2);
-CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=InnoDB;
-INSERT INTO t1 VALUES (3),(4);
-SELECT 1 IN (SELECT a FROM t1 LEFT JOIN t2 ON (a = b AND EXISTS (SELECT * FROM t1)));
-1 IN (SELECT a FROM t1 LEFT JOIN t2 ON (a = b AND EXISTS (SELECT * FROM t1)))
-1
-drop table t1,t2;
diff --git a/mysql-test/main/subselect_innodb.test b/mysql-test/main/subselect_innodb.test
index edb5cefce46..8a2ef8fd9ac 100644
--- a/mysql-test/main/subselect_innodb.test
+++ b/mysql-test/main/subselect_innodb.test
@@ -666,18 +666,4 @@ select 1 from t1 where not exists
 --enable_warnings
 drop table t1;
 
---echo #
---echo # MDEV-28437: Assertion `!eliminated' failed in Item_subselect::exec
---echo #
-CREATE TABLE t1 (a INT) ENGINE=InnoDB;
-INSERT INTO t1 VALUES (1),(2);
-CREATE TABLE t2 (b INT PRIMARY KEY) ENGINE=InnoDB;
-INSERT INTO t1 VALUES (3),(4);
-#enable after fix MDEV-27871
---disable_view_protocol
-SELECT 1 IN (SELECT a FROM t1 LEFT JOIN t2 ON (a = b AND EXISTS (SELECT * FROM t1)));
---enable_view_protocol
-drop table t1,t2;
-
 # End of 10.2 tests
-
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 6472bb86a37..02f786e7090 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -153,7 +153,10 @@ void Item_subselect::cleanup()
   }
   if (engine)
     engine->cleanup();
-  reset();
+
+  if (!eliminated || !thd || !thd->lex || !thd->lex->result)
+    reset();
+
   filesort_buffer.free_sort_buffer();
   my_free(sortbuffer.str);
   sortbuffer.str= 0;
@@ -362,6 +365,8 @@ bool Item_subselect::enumerate_field_refs_processor(void *arg)
 bool Item_subselect::mark_as_eliminated_processor(void *arg)
 {
   eliminated= TRUE;
+  if (!null_value)
+    forced_const= TRUE;
   return FALSE;
 }
 
@@ -382,6 +387,8 @@ bool Item_subselect::eliminate_subselect_processor(void *arg)
   if (!unit->is_excluded())
     unit->exclude();
   eliminated= TRUE;
+  if (!null_value)
+    forced_const= TRUE;
   return FALSE;
 }
 
@@ -660,6 +667,8 @@ int walk_items_for_table_list(Item_processor processor,
 bool Item_subselect::walk(Item_processor processor, bool walk_subquery,
                           void *argument)
 {
+  if (eliminated)
+    return 0;
   if (!(unit->uncacheable & ~UNCACHEABLE_DEPENDENT) && engine->is_executed() &&
       !unit->describe)
   {
@@ -1329,12 +1338,12 @@ void Item_singlerow_subselect::bring_value()
 {
   if (!exec() && assigned())
   {
-    null_value= true;
+    null_value= TRUE;
     for (uint i= 0; i < max_columns ; i++)
     {
       if (!row[i]->null_value)
       {
-        null_value= false;
+        null_value= FALSE;
         return;
       }
     }
@@ -1348,6 +1357,15 @@ double Item_singlerow_subselect::val_real()
   DBUG_ASSERT(fixed == 1);
   if (forced_const)
     return value->val_real();
+  else
+  {
+    if (eliminated)
+    {
+      null_value= TRUE;
+      return 0;
+    }
+  }
+
   if (!exec() && !value->null_value)
   {
     null_value= FALSE;
@@ -1369,6 +1387,15 @@ longlong Item_singlerow_subselect::val_int()
     null_value= value->null_value;
     return val;
   }
+  else
+  {
+    if (eliminated)
+    {
+      null_value= TRUE;
+      return 0;
+    }
+  }
+
   if (!exec() && !value->null_value)
   {
     null_value= FALSE;
@@ -1391,6 +1418,15 @@ String *Item_singlerow_subselect::val_str(String *str)
     null_value= value->null_value;
     return res;
   }
+  else
+  {
+    if (eliminated)
+    {
+      null_value= TRUE;
+      return 0;
+    }
+  }
+
   if (!exec() && !value->null_value)
   {
     null_value= FALSE;
@@ -1413,7 +1449,16 @@ my_decimal *Item_singlerow_subselect::val_decimal(my_decimal *decimal_value)
     my_decimal *val= value->val_decimal(decimal_value);
     null_value= value->null_value;
     return val;
+  } 
+  else
+  {
+    if (eliminated)
+    {
+      null_value= TRUE;
+      return 0;
+    }
   }
+
   if (!exec() && !value->null_value)
   {
     null_value= FALSE;
@@ -1437,6 +1482,15 @@ bool Item_singlerow_subselect::val_bool()
     null_value= value->null_value;
     return val;
   }
+  else
+  {
+    if (eliminated)
+    {
+      null_value= TRUE;
+      return 0;
+    }
+  }
+
   if (!exec() && !value->null_value)
   {
     null_value= FALSE;
@@ -1460,6 +1514,15 @@ bool Item_singlerow_subselect::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate)
     null_value= value->null_value;
     return val;
   }
+  else
+  {
+    if (eliminated)
+    {
+      null_value= TRUE;
+      return 0;
+    }
+  }
+
   if (!exec() && !value->null_value)
   {
     null_value= FALSE;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 58d9b232722..7eb90fc789e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1198,7 +1198,7 @@ JOIN::prepare(TABLE_LIST *tables_init,
   */
   if (select_lex->master_unit()->item &&                               // 1)
       select_lex->first_cond_optimization &&                           // 2)
-      !thd->lex->is_view_context_analysis())                           // 3)
+      !thd->lex->is_ps_or_view_context_analysis())                     // 3)
   {
     remove_redundant_subquery_clauses(select_lex);
   }