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.

4 files changed, 478 insertions, 0 deletions

diff --git a/mysql-test/suite/compat/oracle/r/sp-expr.result b/mysql-test/suite/compat/oracle/r/sp-expr.result
new file mode 100644
index 00000000000..bb0c1a5c5c8
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/sp-expr.result
@@ -0,0 +1,158 @@
+SET sql_mode=ORACLE;
+#
+# Start of 10.3 tests
+#
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+BEGIN
+CASE ((1) IN (SELECT a FROM t1)) WHEN 1 THEN SELECT 1;
+ELSE SELECT NULL;
+END CASE;
+END;
+$$
+1
+1
+BEGIN
+CASE (EXISTS (SELECT a FROM t1)) WHEN 1 THEN SELECT 1;
+ELSE SELECT NULL;
+END CASE;
+END;
+$$
+1
+1
+BEGIN
+IF ((1) IN (SELECT a FROM t1)) THEN SELECT 1;
+ELSE SELECT NULL;
+END IF;
+END;
+$$
+1
+1
+BEGIN
+IF (EXISTS (SELECT a FROM t1)) THEN SELECT 1;
+ELSE SELECT NULL;
+END IF;
+END;
+$$
+1
+1
+BEGIN
+WHILE ((1234) IN (SELECT * FROM t1)) LOOP
+SELECT 1;
+END LOOP;
+END;
+$$
+BEGIN
+WHILE (EXISTS (SELECT * FROM t1 WHERE a=1234)) LOOP
+SELECT 1;
+END LOOP;
+END;
+$$
+BEGIN
+REPEAT
+SELECT 1;
+UNTIL (1 IN (SELECT * FROM t1))
+END REPEAT;
+END;
+$$
+1
+1
+BEGIN
+REPEAT
+SELECT 1;
+UNTIL EXISTS (SELECT * FROM t1 WHERE a=1)
+END REPEAT;
+END;
+$$
+1
+1
+BEGIN
+FOR i IN 0..(1 IN (SELECT * FROM t1))
+LOOP
+SELECT i;
+END LOOP;
+END;
+$$
+i
+0
+i
+1
+BEGIN
+FOR i IN 0..EXISTS (SELECT * FROM t1 WHERE a=1)
+LOOP
+SELECT i;
+END LOOP;
+END;
+$$
+i
+0
+i
+1
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+DECLARE
+a INT DEFAULT ((10) IN (SELECT * FROM t1));
+BEGIN
+SELECT a;
+END;
+$$
+a
+1
+DECLARE
+a INT DEFAULT EXISTS (SELECT * FROM t1);
+BEGIN
+SELECT a;
+END;
+$$
+a
+1
+DROP TABLE t1;
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1);
+CREATE FUNCTION f1() RETURN INT AS
+BEGIN
+RETURN ((1) IN (SELECT * FROM t1));
+END;
+$$
+CREATE FUNCTION f2() RETURN INT AS
+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);
+DECLARE
+va INT;
+CURSOR cur(amin INT) IS SELECT a FROM t1 WHERE a>amin ORDER BY a;
+BEGIN
+OPEN cur(1 IN (SELECT * FROM t1));
+FETCH cur INTO va;
+SELECT va;
+CLOSE cur;
+END;
+$$
+va
+2
+DECLARE
+va INT;
+CURSOR cur(amin INT) IS SELECT a FROM t1 WHERE a>amin ORDER BY a;
+BEGIN
+OPEN cur(EXISTS (SELECT * FROM t1));
+FETCH cur INTO va;
+SELECT va;
+CLOSE cur;
+END;
+$$
+va
+2
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/r/statement-expr.result b/mysql-test/suite/compat/oracle/r/statement-expr.result
new file mode 100644
index 00000000000..ea3bd5232d3
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/statement-expr.result
@@ -0,0 +1,69 @@
+SET sql_mode=ORACLE;
+#
+# Start of 10.3 tests
+#
+CREATE TABLE t1 (id INT, id1 INT);
+INSERT INTO t1 VALUES (1,7);
+INSERT INTO t1 VALUES (1,8);
+SELECT ROW(1,7) IN (SELECT id, id1 FROM t1 WHERE id1= 8);
+ROW(1,7) IN (SELECT id, id1 FROM t1 WHERE id1= 8)
+0
+EXECUTE IMMEDIATE 'SELECT ROW(1, 7) IN (SELECT id, id1 FROM t1 WHERE id1= 8)';
+ROW(1, 7) IN (SELECT id, id1 FROM t1 WHERE id1= 8)
+0
+DROP TABLE t1;
+EXECUTE IMMEDIATE 'SELECT ?' USING (1 IN (SELECT * FROM t1));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM t1))' at line 1
+EXECUTE IMMEDIATE 'SELECT ?' USING (SELECT * FROM t1);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM t1)' at line 1
+CREATE TABLE t1 (id INT);
+INSERT INTO t1 VALUES (10);
+CREATE PROCEDURE p1(a INT) AS BEGIN NULL; END;
+$$
+CALL p1((1) IN (SELECT * FROM t1));
+CALL p1(EXISTS (SELECT * FROM t1));
+DROP PROCEDURE p1;
+DROP TABLE t1;
+SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO=(1 IN (SELECT * FROM t1));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(1 IN (SELECT * FROM t1))' at line 1
+SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO=EXISTS (SELECT * FROM t1);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXISTS (SELECT * FROM t1)' at line 1
+BEGIN NOT ATOMIC
+DECLARE CONTINUE HANDLER FOR SQLWARNING
+RESIGNAL SET MYSQL_ERRNO=(1 IN (SELECT * FROM t1));
+SIGNAL SQLSTATE '01000';
+END;
+$$
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(1 IN (SELECT * FROM t1));
+SIGNAL SQLSTATE '01000';
+END' at line 3
+BEGIN NOT ATOMIC
+DECLARE CONTINUE HANDLER FOR SQLWARNING
+RESIGNAL SET MYSQL_ERRNO=EXISTS (SELECT * FROM t1);
+SIGNAL SQLSTATE '01000';
+END;
+$$
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXISTS (SELECT * FROM t1);
+SIGNAL SQLSTATE '01000';
+END' at line 3
+PREPARE stmt FROM (1 IN (SELECT * FROM t1));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM t1))' at line 1
+PREPARE stmt FROM EXISTS (SELECT * FROM t1);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(SELECT * FROM t1)' at line 1
+EXECUTE IMMEDIATE (1 IN (SELECT * FROM t1));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM t1))' at line 1
+EXECUTE IMMEDIATE EXISTS (SELECT * FROM t1);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(SELECT * FROM t1)' at line 1
+GET DIAGNOSTICS CONDITION (1 IN (SELECT * FROM t1)) @errno=MYSQL_ERRNO;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(1 IN (SELECT * FROM t1)) @errno=MYSQL_ERRNO' at line 1
+GET DIAGNOSTICS CONDITION EXISTS (SELECT * FROM t1) @errno=MYSQL_ERRNO;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXISTS (SELECT * FROM t1) @errno=MYSQL_ERRNO' at line 1
+PURGE BINARY LOGS BEFORE (1 IN (SELECT * FROM t1));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM t1))' at line 1
+PURGE BINARY LOGS BEFORE EXISTS (SELECT * FROM t1);
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(SELECT * FROM t1)' at line 1
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+DO 1 IN (SELECT * FROM t1);
+DO EXISTS (SELECT * FROM t1);
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/t/sp-expr.test b/mysql-test/suite/compat/oracle/t/sp-expr.test
new file mode 100644
index 00000000000..06a5c59b80c
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/sp-expr.test
@@ -0,0 +1,165 @@
+# Testing expressions of different kinds in various parts of SP syntax
+
+SET sql_mode=ORACLE;
+
+--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
+  CASE ((1) IN (SELECT a FROM t1)) WHEN 1 THEN SELECT 1;
+  ELSE SELECT NULL;
+  END CASE;
+END;
+$$
+BEGIN
+  CASE (EXISTS (SELECT a FROM t1)) WHEN 1 THEN SELECT 1;
+  ELSE SELECT NULL;
+  END CASE;
+END;
+$$
+
+BEGIN
+  IF ((1) IN (SELECT a FROM t1)) THEN SELECT 1;
+  ELSE SELECT NULL;
+  END IF;
+END;
+$$
+BEGIN
+  IF (EXISTS (SELECT a FROM t1)) THEN SELECT 1;
+  ELSE SELECT NULL;
+  END IF;
+END;
+$$
+
+BEGIN
+  WHILE ((1234) IN (SELECT * FROM t1)) LOOP
+    SELECT 1;
+  END LOOP;
+END;
+$$
+BEGIN
+  WHILE (EXISTS (SELECT * FROM t1 WHERE a=1234)) LOOP
+    SELECT 1;
+  END LOOP;
+END;
+$$
+
+BEGIN
+  REPEAT
+    SELECT 1;
+  UNTIL (1 IN (SELECT * FROM t1))
+  END REPEAT;
+END;
+$$
+BEGIN
+  REPEAT
+    SELECT 1;
+  UNTIL EXISTS (SELECT * FROM t1 WHERE a=1)
+  END REPEAT;
+END;
+$$
+
+BEGIN
+  FOR i IN 0..(1 IN (SELECT * FROM t1))
+  LOOP
+    SELECT i;
+  END LOOP;
+END;
+$$
+BEGIN
+  FOR i IN 0..EXISTS (SELECT * FROM t1 WHERE a=1)
+  LOOP
+    SELECT i;
+  END LOOP;
+END;
+$$
+DELIMITER ;$$
+DROP TABLE t1;
+
+
+#
+# Subselects as SP variable default values
+#
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (10);
+DELIMITER $$;
+DECLARE
+  a INT DEFAULT ((10) IN (SELECT * FROM t1));
+BEGIN
+  SELECT a;
+END;
+$$
+DECLARE
+  a INT DEFAULT EXISTS (SELECT * FROM t1);
+BEGIN
+  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() RETURN INT AS
+BEGIN
+  RETURN ((1) IN (SELECT * FROM t1));
+END;
+$$
+CREATE FUNCTION f2() RETURN INT AS
+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 $$;
+DECLARE
+  va INT;
+  CURSOR cur(amin INT) IS SELECT a FROM t1 WHERE a>amin ORDER BY a;
+BEGIN
+  OPEN cur(1 IN (SELECT * FROM t1));
+  FETCH cur INTO va;
+  SELECT va;
+  CLOSE cur;
+END;
+$$
+DECLARE
+  va INT;
+  CURSOR cur(amin INT) IS SELECT a FROM t1 WHERE a>amin ORDER BY a;
+BEGIN
+  OPEN cur(EXISTS (SELECT * FROM t1));
+  FETCH cur INTO va;
+  SELECT va;
+  CLOSE cur;
+END;
+$$
+DELIMITER ;$$
+DROP TABLE t1;
diff --git a/mysql-test/suite/compat/oracle/t/statement-expr.test b/mysql-test/suite/compat/oracle/t/statement-expr.test
new file mode 100644
index 00000000000..f4a6a25eff3
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/statement-expr.test
@@ -0,0 +1,86 @@
+# Testing expressions of different kinds in various non-SELECT statements
+
+SET sql_mode=ORACLE;
+
+--echo #
+--echo # Start of 10.3 tests
+--echo #
+
+#
+# Subselects in non-SELECT statements
+#
+
+CREATE TABLE t1 (id INT, id1 INT);
+INSERT INTO t1 VALUES (1,7);
+INSERT INTO t1 VALUES (1,8);
+SELECT ROW(1,7) IN (SELECT id, id1 FROM t1 WHERE id1= 8);
+EXECUTE IMMEDIATE 'SELECT ROW(1, 7) IN (SELECT id, id1 FROM t1 WHERE id1= 8)';
+DROP TABLE t1;
+
+--error ER_PARSE_ERROR
+EXECUTE IMMEDIATE 'SELECT ?' USING (1 IN (SELECT * FROM t1));
+--error ER_PARSE_ERROR
+EXECUTE IMMEDIATE 'SELECT ?' USING (SELECT * FROM t1);
+
+
+CREATE TABLE t1 (id INT);
+INSERT INTO t1 VALUES (10);
+DELIMITER $$;
+CREATE PROCEDURE p1(a INT) AS BEGIN NULL; END;
+$$
+DELIMITER ;$$
+CALL p1((1) IN (SELECT * FROM t1));
+CALL p1(EXISTS (SELECT * FROM t1));
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+--error ER_PARSE_ERROR
+SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO=(1 IN (SELECT * FROM t1));
+--error ER_PARSE_ERROR
+SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO=EXISTS (SELECT * FROM t1);
+
+DELIMITER $$;
+--error ER_PARSE_ERROR
+BEGIN NOT ATOMIC
+  DECLARE CONTINUE HANDLER FOR SQLWARNING
+    RESIGNAL SET MYSQL_ERRNO=(1 IN (SELECT * FROM t1));
+  SIGNAL SQLSTATE '01000';
+END;
+$$
+--error ER_PARSE_ERROR
+BEGIN NOT ATOMIC
+  DECLARE CONTINUE HANDLER FOR SQLWARNING
+    RESIGNAL SET MYSQL_ERRNO=EXISTS (SELECT * FROM t1);
+  SIGNAL SQLSTATE '01000';
+END;
+$$
+DELIMITER ;$$
+
+
+--error ER_PARSE_ERROR
+PREPARE stmt FROM (1 IN (SELECT * FROM t1));
+--error ER_PARSE_ERROR
+PREPARE stmt FROM EXISTS (SELECT * FROM t1);
+
+--error ER_PARSE_ERROR
+EXECUTE IMMEDIATE (1 IN (SELECT * FROM t1));
+--error ER_PARSE_ERROR
+EXECUTE IMMEDIATE EXISTS (SELECT * FROM t1);
+
+--error ER_PARSE_ERROR
+GET DIAGNOSTICS CONDITION (1 IN (SELECT * FROM t1)) @errno=MYSQL_ERRNO;
+--error ER_PARSE_ERROR
+GET DIAGNOSTICS CONDITION EXISTS (SELECT * FROM t1) @errno=MYSQL_ERRNO;
+
+--error ER_PARSE_ERROR
+PURGE BINARY LOGS BEFORE (1 IN (SELECT * FROM t1));
+--error ER_PARSE_ERROR
+PURGE BINARY LOGS BEFORE EXISTS (SELECT * FROM t1);
+
+CREATE TABLE t1 (a INT);
+INSERT INTO t1 VALUES (1),(2),(3);
+DO 1 IN (SELECT * FROM t1);
+DO EXISTS (SELECT * FROM t1);
+DROP TABLE t1;
+
+