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.

1 files changed, 153 insertions, 0 deletions

diff --git a/mysql-test/main/sp-expr.result b/mysql-test/main/sp-expr.result
new file mode 100644
index 00000000000..ead9856f9ab
--- /dev/null
+++ b/mysql-test/main/sp-expr.result
@@ -0,0 +1,153 @@
+#
+# Start of 10.3 tests
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+BEGIN NOT ATOMIC
+CASE ((1) IN (SELECT a FROM t1)) WHEN 1 THEN SELECT 1;
+ELSE SELECT NULL;
+END CASE;
+END;
+$$
+1
+1
+BEGIN NOT ATOMIC
+CASE (EXISTS (SELECT a FROM t1)) WHEN 1 THEN SELECT 1;
+ELSE SELECT NULL;
+END CASE;
+END;
+$$
+1
+1
+BEGIN NOT ATOMIC
+IF ((1) IN (SELECT a FROM t1)) THEN SELECT 1;
+ELSE SELECT NULL;
+END IF;
+END;
+$$
+1
+1
+BEGIN NOT ATOMIC
+IF (EXISTS (SELECT a FROM t1)) THEN SELECT 1;
+ELSE SELECT NULL;
+END IF;
+END;
+$$
+1
+1
+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;
+$$
+1
+1
+BEGIN NOT ATOMIC
+REPEAT
+SELECT 1;
+UNTIL EXISTS (SELECT * FROM t1 WHERE a=1)
+END REPEAT;
+END;
+$$
+1
+1
+BEGIN NOT ATOMIC
+FOR i IN 0..(1 IN (SELECT * FROM t1))
+DO
+SELECT i;
+END FOR;
+END;
+$$
+i
+0
+i
+1
+BEGIN NOT ATOMIC
+FOR i IN 0..EXISTS (SELECT * FROM t1 WHERE a=1)
+DO
+SELECT i;
+END FOR;
+END;
+$$
+i
+0
+i
+1
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+BEGIN NOT ATOMIC
+DECLARE a INT DEFAULT ((10) IN (SELECT * FROM t1));
+SELECT a;
+END;
+$$
+a
+1
+BEGIN NOT ATOMIC
+DECLARE a INT DEFAULT EXISTS (SELECT * FROM t1);
+SELECT a;
+END;
+$$
+a
+1
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+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;
+$$
+SELECT f1();
+f1()
+1
+SELECT f2();
+f2()
+1
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+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;
+$$
+va
+2
+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;
+$$
+va
+2
+DROP TABLE t1;