Merge 10.3.7 into 10.4shagalla-10.4

1 files changed, 158 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;