diff options
Diffstat (limited to 'mysql-test/suite/compat/oracle/r/sp-anonymous.result')
| -rw-r--r-- | mysql-test/suite/compat/oracle/r/sp-anonymous.result | 220 |
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; |