From c058117c6c857ecb41960bd705104adc42348645 Mon Sep 17 00:00:00 2001
From: Alexander Barkov <bar@mariadb.com>
Date: Fri, 20 Apr 2018 23:15:27 +0400
Subject: Adding more tests for IN/EXIST subqueries for better coverage

Adding tests with IN/EXISTS subqueries in various rarely unexpected places:

- non-SELECT statements
- SP control
- cursors

etc.
---
 mysql-test/main/sp-expr.test | 159 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 159 insertions(+)
 create mode 100644 mysql-test/main/sp-expr.test

(limited to 'mysql-test/main/sp-expr.test')

diff --git a/mysql-test/main/sp-expr.test b/mysql-test/main/sp-expr.test
new file mode 100644
index 00000000000..a9b1afede88
--- /dev/null
+++ b/mysql-test/main/sp-expr.test
@@ -0,0 +1,159 @@
+# Testing expressions of different kinds in various parts of SP syntax
+
+--echo #
+--echo # Start of 10.3 tests
+--echo #
+
+#
+# Subselects in SP control structures
+#
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+DELIMITER $$;
+
+BEGIN NOT ATOMIC
+  CASE ((1) IN (SELECT a FROM t1)) WHEN 1 THEN SELECT 1;
+  ELSE SELECT NULL;
+  END CASE;
+END;
+$$
+BEGIN NOT ATOMIC
+  CASE (EXISTS (SELECT a FROM t1)) WHEN 1 THEN SELECT 1;
+  ELSE SELECT NULL;
+  END CASE;
+END;
+$$
+
+BEGIN NOT ATOMIC
+  IF ((1) IN (SELECT a FROM t1)) THEN SELECT 1;
+  ELSE SELECT NULL;
+  END IF;
+END;
+$$
+BEGIN NOT ATOMIC
+  IF (EXISTS (SELECT a FROM t1)) THEN SELECT 1;
+  ELSE SELECT NULL;
+  END IF;
+END;
+$$
+
+BEGIN NOT ATOMIC
+  WHILE ((1234) IN (SELECT * FROM t1)) DO
+    SELECT 1;
+  END WHILE;
+END;
+$$
+BEGIN NOT ATOMIC
+  WHILE (EXISTS (SELECT * FROM t1 WHERE a=1234)) DO
+    SELECT 1;
+  END WHILE;
+END;
+$$
+
+BEGIN NOT ATOMIC
+  REPEAT
+    SELECT 1;
+  UNTIL (1 IN (SELECT * FROM t1))
+  END REPEAT;
+END;
+$$
+BEGIN NOT ATOMIC
+  REPEAT
+    SELECT 1;
+  UNTIL EXISTS (SELECT * FROM t1 WHERE a=1)
+  END REPEAT;
+END;
+$$
+
+BEGIN NOT ATOMIC
+  FOR i IN 0..(1 IN (SELECT * FROM t1))
+  DO
+    SELECT i;
+  END FOR;
+END;
+$$
+BEGIN NOT ATOMIC
+  FOR i IN 0..EXISTS (SELECT * FROM t1 WHERE a=1)
+  DO
+    SELECT i;
+  END FOR;
+END;
+$$
+DELIMITER ;$$
+DROP TABLE t1;
+
+
+#
+# Subselects as SP variable default values
+#
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE a INT DEFAULT ((10) IN (SELECT * FROM t1));
+  SELECT a;
+END;
+$$
+BEGIN NOT ATOMIC
+  DECLARE a INT DEFAULT EXISTS (SELECT * FROM t1);
+  SELECT a;
+END;
+$$
+DELIMITER ;$$
+DROP TABLE t1;
+
+
+#
+# Subselects SP function return values
+#
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+DELIMITER $$;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+  RETURN ((1) IN (SELECT * FROM t1));
+END;
+$$
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+  RETURN EXISTS (SELECT * FROM t1 WHERE a=1);
+END;
+$$
+DELIMITER ;$$
+SELECT f1();
+SELECT f2();
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP TABLE t1;
+
+
+#
+# Subselects in CURSOR parameters
+#
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+DELIMITER $$;
+BEGIN NOT ATOMIC
+  DECLARE va INT;
+  DECLARE cur CURSOR(amin INT) FOR SELECT a FROM t1 WHERE a>amin ORDER BY a;
+  OPEN cur(1 IN (SELECT * FROM t1));
+  FETCH cur INTO va;
+  SELECT va;
+  CLOSE cur;
+END;
+$$
+BEGIN NOT ATOMIC
+  DECLARE va INT;
+  DECLARE cur CURSOR(amin INT) FOR SELECT a FROM t1 WHERE a>amin ORDER BY a;
+  OPEN cur(EXISTS (SELECT * FROM t1));
+  FETCH cur INTO va;
+  SELECT va;
+  CLOSE cur;
+END;
+$$
+DELIMITER ;$$
+DROP TABLE t1;
-- 
cgit v1.2.1