Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 220 insertions, 0 deletions

diff --git a/mysql-test/suite/compat/oracle/r/sp-anonymous.result b/mysql-test/suite/compat/oracle/r/sp-anonymous.result
new file mode 100644
index 00000000..26bce0f4
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/sp-anonymous.result
@@ -0,0 +1,220 @@
+SET sql_mode=ORACLE;
+#
+# MDEV-10655 Anonymous blocks
+#
+# Testing BEGIN NOT ATOMIC with no declarations
+BEGIN NOT ATOMIC
+SELECT 1 AS a;
+END
+/
+a
+1
+# Testing BEGIN NOT ATOMIC with declarations
+# DECLARE starts a new block and thus must be followed by BEGIN .. END
+BEGIN NOT ATOMIC
+DECLARE
+i INT DEFAULT 5;
+x INT DEFAULT 10;
+BEGIN
+<<label>>
+WHILE i > 3 LOOP
+i:= i - 1;
+SELECT i;
+END LOOP label;
+END;
+END
+/
+i
+4
+i
+3
+# Anonymous blocks with no declarations and no exceptions
+BEGIN
+SELECT 1 AS a;
+END
+$$
+a
+1
+SET AUTOCOMMIT=OFF;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10);
+BEGIN
+INSERT INTO t1 VALUES(20);
+INSERT INTO t1 VALUES(30);
+ROLLBACK;
+END;
+$$
+SELECT * FROM t1;
+a
+DROP TABLE t1;
+SET AUTOCOMMIT=DEFAULT;
+SET AUTOCOMMIT=OFF;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10);
+BEGIN
+INSERT INTO t1 VALUES(20);
+INSERT INTO t1 VALUES(30);
+END;
+$$
+ROLLBACK;
+SELECT * FROM t1;
+a
+DROP TABLE t1;
+SET AUTOCOMMIT=DEFAULT;
+SET AUTOCOMMIT=OFF;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10);
+BEGIN
+INSERT INTO t1 VALUES(20);
+INSERT INTO t1 VALUES(30);
+COMMIT;
+END;
+$$
+SELECT * FROM t1;
+a
+10
+20
+30
+DROP TABLE t1;
+SET AUTOCOMMIT=DEFAULT;
+SET AUTOCOMMIT=OFF;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10);
+BEGIN
+INSERT INTO t1 VALUES(20);
+INSERT INTO t1 VALUES(30);
+END;
+$$
+COMMIT;
+SELECT * FROM t1;
+a
+10
+20
+30
+DROP TABLE t1;
+SET AUTOCOMMIT=DEFAULT;
+SET AUTOCOMMIT=OFF;
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10);
+BEGIN
+INSERT INTO t1 VALUES(20);
+INSERT INTO t1 VALUES(20);
+END;
+$$
+ERROR 23000: Duplicate entry '20' for key 'PRIMARY'
+COMMIT;
+SELECT * FROM t1;
+a
+10
+20
+DROP TABLE t1;
+SET AUTOCOMMIT=DEFAULT;
+# Anonymous blocks with no declarations, with exceptions
+SET AUTOCOMMIT=OFF;
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10);
+BEGIN
+INSERT INTO t1 VALUES(20);
+INSERT INTO t1 VALUES(20);
+EXCEPTION
+WHEN DUP_VAL_ON_INDEX THEN NULL;
+END;
+$$
+COMMIT;
+SELECT * FROM t1;
+a
+10
+20
+DROP TABLE t1;
+SET AUTOCOMMIT=DEFAULT;
+# Anonymous blocks with declarations, with no exceptions
+SET AUTOCOMMIT=OFF;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10);
+DECLARE
+a20 INT:=20;
+a30 INT:=30;
+BEGIN
+INSERT INTO t1 VALUES(a20);
+INSERT INTO t1 VALUES(a30);
+ROLLBACK;
+END;
+$$
+SELECT * FROM t1;
+a
+DROP TABLE t1;
+SET AUTOCOMMIT=DEFAULT;
+SET AUTOCOMMIT=OFF;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10);
+DECLARE
+a20 INT:=20;
+a30 INT:=30;
+BEGIN
+INSERT INTO t1 VALUES(a20);
+INSERT INTO t1 VALUES(a30);
+END;
+$$
+ROLLBACK;
+SELECT * FROM t1;
+a
+DROP TABLE t1;
+SET AUTOCOMMIT=DEFAULT;
+SET AUTOCOMMIT=OFF;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10);
+DECLARE
+a20 INT:=20;
+a30 INT:=30;
+BEGIN
+INSERT INTO t1 VALUES(a20);
+INSERT INTO t1 VALUES(a30);
+COMMIT;
+END;
+$$
+SELECT * FROM t1;
+a
+10
+20
+30
+DROP TABLE t1;
+SET AUTOCOMMIT=DEFAULT;
+SET AUTOCOMMIT=OFF;
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10);
+DECLARE
+a20 INT:=20;
+a30 INT:=30;
+BEGIN
+INSERT INTO t1 VALUES(a20);
+INSERT INTO t1 VALUES(a30);
+END;
+$$
+COMMIT;
+SELECT * FROM t1;
+a
+10
+20
+30
+DROP TABLE t1;
+SET AUTOCOMMIT=DEFAULT;
+# Anonymous blocks with declarations, with exceptions
+SET AUTOCOMMIT=OFF;
+CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (10);
+DECLARE
+a20 INT:=20;
+BEGIN
+INSERT INTO t1 VALUES(a20);
+INSERT INTO t1 VALUES(a20);
+EXCEPTION
+WHEN DUP_VAL_ON_INDEX THEN NULL;
+END;
+$$
+COMMIT;
+SELECT * FROM t1;
+a
+10
+20
+DROP TABLE t1;
+SET AUTOCOMMIT=DEFAULT;