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;