summaryrefslogtreecommitdiff
path: root/mysql-test/main
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/main')
-rw-r--r--mysql-test/main/subselect_elimination.result75
-rw-r--r--mysql-test/main/subselect_elimination.test86
-rw-r--r--mysql-test/main/subselect_innodb.result11
-rw-r--r--mysql-test/main/subselect_innodb.test14
4 files changed, 161 insertions, 25 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
-
View Lorry Controller Status