EVOLUTION-MANAGER

Edit File: ev51914.result

connection node_1; SAVEPOINT in a stored function should be forbidden CREATE FUNCTION f1 () RETURNS INT BEGIN SAVEPOINT s; RETURN 1; END| SELECT f1(); f1() 1 DROP FUNCTION f1; ROLLBACK TO SAVEPOINT in a stored function should be forbidden CREATE FUNCTION f2 () RETURNS INT BEGIN ROLLBACK TO SAVEPOINT s; RETURN 1; END| BEGIN; SAVEPOINT s; SELECT f2(); ERROR 42000: SAVEPOINT s does not exist COMMIT; DROP FUNCTION f2; BEGIN; SAVEPOINT S; ROLLBACK TO SAVEPOINT S; COMMIT; CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; CREATE TABLE t2 (a INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; INSERT INTO t1 values (110), (111), (112), (113), (114); Direct SAVEPOINT in a trigger should be forbidden connection node_2; CREATE TRIGGER i1_t1 BEFORE INSERT ON t1 FOR EACH ROW SAVEPOINT s; connection node_1; INSERT INTO t1 VALUES (1); DROP TRIGGER i1_t1; CREATE TRIGGER i2_t1 AFTER INSERT ON t1 FOR EACH ROW SAVEPOINT s; INSERT INTO t1 VALUES (2); DROP TRIGGER i2_t1; INSERT INTO t1 VALUES (3); CREATE TRIGGER u1_t1 BEFORE UPDATE ON t1 FOR EACH ROW SAVEPOINT s; UPDATE t1 SET a=4 WHERE a=3; DROP TRIGGER u1_t1; CREATE TRIGGER u2_t1 AFTER UPDATE ON t1 FOR EACH ROW SAVEPOINT s; UPDATE t1 SET a=4 WHERE a=3; DROP TRIGGER u2_t1; CREATE TRIGGER d1_t1 BEFORE DELETE ON t1 FOR EACH ROW SAVEPOINT s; DELETE FROM t1; DROP TRIGGER d1_t1; CREATE TRIGGER d1_t1 AFTER DELETE ON t1 FOR EACH ROW SAVEPOINT s; DELETE FROM t1; DROP TRIGGER d1_t1; SAVEPOINT in a compound statement in a trigger should be forbidden CREATE TRIGGER i3_t1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN SAVEPOINT s; END| INSERT INTO t1 VALUES (5); DROP TRIGGER i3_t1; SAVEPOINT in a PS call in a trigger should be forbidden CREATE TRIGGER i4_t1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN PREPARE set_savepoint FROM "SAVEPOINT s"; EXECUTE set_savepoint; DEALLOCATE PREPARE set_savepoint; END| ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger connection node_2; SAVEPOINT in SP called from a trigger should be forbidden CREATE PROCEDURE p1() BEGIN SAVEPOINT s; END| connection node_1; CREATE TRIGGER i5_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p1; INSERT INTO t1 VALUES (6); DROP TRIGGER i5_t1; SAVEPOINT in a SP called from a PS called from a trigger be forbidden PREPARE call_p1 FROM "CALL p1"; CREATE TRIGGER i6_t1 BEFORE INSERT ON t1 FOR EACH ROW EXECUTE call_p1; ERROR 0A000: Dynamic SQL is not allowed in stored function or trigger SAVEPOINT in a function called from a trigger should be forbidden CREATE FUNCTION f1 () RETURNS INT BEGIN SAVEPOINT s; RETURN 1; END| CREATE TRIGGER i7_t1 BEFORE INSERT ON t1 FOR EACH ROW SET @foo = f1(); INSERT INTO t1 VALUES (7); DROP TRIGGER i7_t1; SAVEPOINT in a SP called from a SP called from a trigger should be forbidden CREATE PROCEDURE p2() BEGIN CALL p1(); END| CREATE TRIGGER i8_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p2; INSERT INTO t1 VALUES (8); DROP TRIGGER i8_t1; SAVEPOINT in a SP called from a trigger called from a SP should be forbidden CREATE TRIGGER i9_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p1; CREATE PROCEDURE p3() BEGIN INSERT INTO t1 VALUES (9); END| CALL p3(); DROP TRIGGER i9_t1; ROLLBACK TO SAVEPOINT in trigger as a trivial statement should be forbidden CREATE TRIGGER i4_t1 BEFORE INSERT ON t1 FOR EACH ROW ROLLBACK TO SAVEPOINT s; BEGIN; SAVEPOINT s; INSERT INTO t1 VALUES (5); ERROR 42000: SAVEPOINT s does not exist COMMIT; DROP TRIGGER i4_t1; ROLLBACK TO SAVEPOINT in a trigger in a SP call should be forbidden CREATE PROCEDURE p4() BEGIN ROLLBACK TO SAVEPOINT s; END| CREATE TRIGGER i5_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p4; BEGIN; SAVEPOINT s; INSERT INTO t1 VALUES (6); ERROR 42000: SAVEPOINT s does not exist COMMIT; DROP TRIGGER i5_t1; SAVEPOINT in a SP next to a trigger should work CREATE TRIGGER i6_t1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a = NEW.a + 1; CREATE PROCEDURE p5() BEGIN SAVEPOINT s; INSERT INTO t1 VALUES (10); ROLLBACK TO SAVEPOINT s; END| BEGIN; CALL p5(); COMMIT; DROP TRIGGER i6_t1; connection node_2; create trigger t1 before insert on t1 for each row begin insert into t2 values (NULL); end| connection node_1; INSERT INTO t1 VALUES (201), (202), (203); connection node_1; SELECT * FROM t1; a 5 6 7 8 9 201 202 203 SELECT COUNT(*) FROM t2; COUNT(*) 3 connection node_2; SELECT * FROM t1; a 5 6 7 8 9 201 202 203 SELECT COUNT(*) FROM t2; COUNT(*) 3 connection node_1; DEALLOCATE PREPARE call_p1; connection node_2; DROP TABLE t1, t2; DROP PROCEDURE p1; DROP PROCEDURE p2; DROP PROCEDURE p3; DROP PROCEDURE p4; DROP PROCEDURE p5; DROP FUNCTION f1;