mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
e7f7789482
* automatically disable ps2 and cursor protocol when the
select statement returns no result set
* remove manual {disable|enable}_{ps2|cursor}_protocol from around
`select ... into` in tests
* other misc collateral test cleanups
369 lines
11 KiB
Text
369 lines
11 KiB
Text
# Tests for the task MDEV-34724: Skipping a row operation from a trigger
|
|
|
|
--echo # Test case 1: check that a row being inserted
|
|
--echo # can be filtered out by means running of a statement
|
|
--echo # SIGNAL SQLSTATE '02TRG' from the trigger body
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
--delimiter $
|
|
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF NEW.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
--delimiter ;
|
|
|
|
--echo # Execution of the following INSERT statement produces warnings
|
|
--echo # for the row (1) filtered out by the statement SIGNAL SQLSTATE '02TRG'
|
|
--echo # invoked from the trigger
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
--echo # Expected output is the rows (2), (3)
|
|
SELECT * FROM t1;
|
|
--echo # Clean up
|
|
TRUNCATE TABLE t1;
|
|
|
|
--echo # Test case 2: run the statement INSERT SELECT and check
|
|
--echo # that the rows (1) is filtered out by the trigger `t1_bi`
|
|
CREATE TABLE t2 (a INT);
|
|
INSERT INTO t2 VALUES (1), (2), (1), (3), (5);
|
|
--echo # Execution of the following INSERT ... SELECT statement produces warnings
|
|
--echo # for the row (1) filtered out by the statement SIGNAL SQLSTATE '02TRG'
|
|
--echo # invoked from the trigger
|
|
INSERT INTO t1 SELECT * FROM t2;
|
|
--echo # Expected output is the rows (2), (3), (5)
|
|
SELECT * FROM t1;
|
|
--echo # Clean up
|
|
DROP TABLE t1, t2;
|
|
|
|
--echo # Test case 3: check for cooperation of the feature
|
|
--echo # 'skipping rows from a trigger' and execution of
|
|
--echo # the statement LOAD
|
|
CREATE TABLE t1 (a INT);
|
|
--delimiter $
|
|
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF NEW.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
--delimiter ;
|
|
|
|
--echo # Test case 3.1: check for LOAD DATA INFILE
|
|
--echo # Prepare a file with dump of the table `t2` and
|
|
--echo # then use it for loading data into the table `t1`
|
|
CREATE TABLE t2 (a INT);
|
|
INSERT INTO t2 VALUES (1), (2), (1), (3), (5);
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
--eval SELECT * INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/t2' FROM t2
|
|
--echo # Execution of the following LOAD DATA INFILE statement produces warnings
|
|
--echo # for the rows (1) filtered out by the statement SIGNAL SQLSTATE '02TRG'
|
|
--echo # invoked from the trigger
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/t2' INTO TABLE t1
|
|
--echo # Querying of the table `t1` should return three rows: (2), (3), (5)
|
|
SELECT * FROM t1;
|
|
|
|
--echo # Clean up
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/t2
|
|
TRUNCATE TABLE t1;
|
|
|
|
--echo # Test case 3.2: check for LOAD XML INFILE
|
|
--echo # The same test with loading data from a file in presence of
|
|
--echo # a BEFORE INSERT trigger, but in this case the data to be loaded
|
|
--echo # is in xml format.
|
|
|
|
--echo # Prepare a file with dump of the table `t2` in xml format and
|
|
--echo # then use it for loading data into the table `t1`
|
|
# Running the $MYSQL_DUMP tool against an embedded server does not work.
|
|
--source include/not_embedded.inc
|
|
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
--exec $MYSQL_DUMP --xml test t2 > "$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml" 2>&1
|
|
|
|
--echo # Execution of the following LOAD XML INFILE statement produces warnings
|
|
--echo # for the rows (1) filtered out by the statement SIGNAL SQLSTATE '02TRG'
|
|
--echo # invoked from the trigger
|
|
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
|
|
--eval LOAD XML INFILE '$MYSQLTEST_VARDIR/tmp/loadxml-dump.xml' INTO TABLE t1 ROWS IDENTIFIED BY '<row>'
|
|
|
|
SELECT * FROM t1;
|
|
|
|
--echo # Clean up
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/loadxml-dump.xml
|
|
DROP TABLE t1, t2;
|
|
|
|
--echo # Test case 4: check that a row being deleted
|
|
--echo # can be filtered out by means running of a statement
|
|
--echo # SIGNAL SQLSTATE '02TRG' from the trigger body
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1), (2), (3), (1);
|
|
--delimiter $
|
|
CREATE TRIGGER t1_bi BEFORE DELETE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF OLD.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
--delimiter ;
|
|
|
|
--echo # No rows deleted in result of running the following statement
|
|
--echo # Execution of the following DELETE statement produces warnings
|
|
--echo # for the row (1) filtered out by the statement SIGNAL SQLSTATE '02TRG'
|
|
--echo # invoked from the trigger
|
|
DELETE FROM t1 WHERE a = 1;
|
|
--echo # Show that the rows satisfying the condition a = 1 are retained
|
|
--echo # in the table. Expected output is the rows (1), (2), (3), (1)
|
|
SELECT * FROM t1;
|
|
|
|
--echo # Shows that rows with a column value different
|
|
--echo # from '1' are deleted successfully
|
|
DELETE FROM t1 WHERE a = 2;
|
|
--echo # Expected output is the rows (1), (3), (1)
|
|
SELECT * FROM t1;
|
|
|
|
--echo # Check that the DELETE statement without condition takes into
|
|
--echo # account the fact that some of rows should be skipped.
|
|
DELETE FROM t1;
|
|
--echo # Expected output is the rows (1), (1) since they are explicilty
|
|
--echo # skipped by the trigger logic
|
|
SELECT * FROM t1;
|
|
|
|
--echo # Clean up
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test case 5: check that AFTER INSERT/UPDATE/DELETE trigger is not fired
|
|
--echo # in case a row skipped by corresponding BEFORE trigger
|
|
--echo #
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (a INT);
|
|
--echo # Test case 5.1: check for the pair BEFORE INSERT/AFTER INSERT
|
|
--delimiter $
|
|
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF NEW.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
--delimiter ;
|
|
|
|
--delimiter $
|
|
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO t2 VALUES (NEW.a);
|
|
END
|
|
$
|
|
--delimiter ;
|
|
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
--echo # Clean up
|
|
DROP TABLE t1, t2;
|
|
|
|
--echo # Test case 5.2: check for the pair BEFORE DELETE/AFTER DELETE
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (a INT);
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
--delimiter $
|
|
CREATE TRIGGER t1_bd BEFORE DELETE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF OLD.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
--delimiter ;
|
|
|
|
--delimiter $
|
|
CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO t2 VALUES (OLD.a);
|
|
END
|
|
$
|
|
--delimiter ;
|
|
|
|
DELETE FROM t1;
|
|
--echo # The row (1) is skipped by implementation of the trigger t1_bd,
|
|
--echo # therefore the row (1) isn't inserted into the table t2 since
|
|
--echo # the trigger t1_ad isn't fired for the row (1)
|
|
SELECT * FROM t1;
|
|
SELECT * FROM t2;
|
|
--echo # Clean up
|
|
DROP TABLE t1, t2;
|
|
|
|
--echo # Test case 5.3: check for the pair BEFORE UPDATE/AFTER UPDATE
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (old_a INT, new_a INT);
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
--delimiter $
|
|
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF OLD.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
|
|
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
INSERT INTO t2 VALUES (OLD.a, NEW.a);
|
|
END
|
|
$
|
|
--delimiter ;
|
|
|
|
--echo # The following statement UPDATE doens't modify the row (1)
|
|
--echo # since this row is explicitly ignored by implementation of
|
|
--echo # trigger t1_bu, therefore the trigger t1_au is not fired
|
|
--echo # for this row and the row (1, 11) not inserted into the table t2
|
|
UPDATE t1 SET a = a + 10;
|
|
|
|
--echo # Expected output of the following statement SELECT is (1), (12), (13)
|
|
SELECT * FROM t1;
|
|
--echo # Expected output of query from the table t2 is (2, 12), (3, 13)
|
|
SELECT * FROM t2;
|
|
|
|
--echo # Clean up
|
|
DROP TABLE t1, t2;
|
|
|
|
--echo # Test case 6: check cooperation of UPDATE with the
|
|
--echo # 'skipping a row from a trigger' feature
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1), (2), (1), (3), (5);
|
|
|
|
--delimiter $
|
|
CREATE TRIGGER t1_bd BEFORE UPDATE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF OLD.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
--delimiter ;
|
|
|
|
--echo # Check for update with condition
|
|
UPDATE t1 SET a = 1000 WHERE a = 1;
|
|
--echo # Expected result is the rows (1), (2), (1), (3), (5)
|
|
SELECT * FROM t1;
|
|
|
|
--echo # Check for unconditional update
|
|
UPDATE t1 SET a = a + 100;
|
|
--echo # Expected result is the rows (1), (102), (1), (103), (105)
|
|
SELECT * FROM t1;
|
|
|
|
--echo # Multi-update
|
|
TRUNCATE TABLE t1;
|
|
INSERT INTO t1 VALUES (1), (2), (1), (3), (5);
|
|
|
|
CREATE TABLE t2 (a INT);
|
|
INSERT INTO t2 VALUES (1), (3);
|
|
--echo # In multi-update the trigger skips an update of
|
|
--echo # the first table only (the one that has an associated trigger),
|
|
--echo # the second table (without a trigger) is still updated
|
|
UPDATE t1, t2 SET t1.a = t1.a + 300, t2.a = t2.a + 300 WHERE t1.a = t2.a;
|
|
--echo # Expected results is the rows (1), (2), (1), (303), (5)
|
|
SELECT * FROM t1;
|
|
--echo # Expected results is the rows (301), (303)
|
|
SELECT * FROM t2;
|
|
--echo # Clean up
|
|
DROP TABLE t1, t2;
|
|
|
|
--echo # Test case 7: check that MESSAGE_TEXT and MYSQL_ERRNOR still
|
|
--echo # can be assigned by a user for SQLSTATE '02TRG'
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
--delimiter $
|
|
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF NEW.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG' SET MYSQL_ERRNO=1, MESSAGE_TEXT='This value is intentionally ignored';
|
|
END IF;
|
|
END
|
|
$
|
|
--delimiter ;
|
|
|
|
--echo # Execution of the following INSERT statement produces warnings
|
|
--echo # for the row (1) filtered out by the statement SIGNAL SQLSTATE '02TRG'
|
|
--echo # invoked from the trigger. The errno has value 1 and the message is
|
|
--echo # is the text message 'This value is intentionally ignored'
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
--echo # Expected output is the rows (2), (3)
|
|
SELECT * FROM t1;
|
|
--echo # Clean up
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test case 8: check that SQLSTATE '02TRG' doesn't have any special
|
|
--echo # meaning in AFTER triggers
|
|
|
|
--echo # Test case 8.1: check it for AFTER INSERT trigger
|
|
CREATE TABLE t1 (a INT);
|
|
--delimiter $
|
|
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF NEW.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
--delimiter ;
|
|
|
|
--echo # There is no a handler for the signal raised from inside
|
|
--echo # the trigger t1_ai, so the statement INSERT INTO fails
|
|
--echo # with the error ER_SIGNAL_NOT_FOUND
|
|
--error ER_SIGNAL_NOT_FOUND
|
|
INSERT INTO t1 VALUES (1);
|
|
|
|
SELECT * FROM t1;
|
|
--echo # Clean up
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test case 8.2: check it for AFTER UPDATE trigger
|
|
CREATE TABLE t1 (a INT);
|
|
--delimiter $
|
|
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF OLD.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
--delimiter ;
|
|
|
|
INSERT INTO t1 VALUES (1);
|
|
--echo # There is no a handler for the signal raised from inside
|
|
--echo # the trigger t1_au, so the statement UPDATE fails
|
|
--echo # with the error ER_SIGNAL_NOT_FOUND
|
|
--error ER_SIGNAL_NOT_FOUND
|
|
UPDATE t1 SET a = 10;
|
|
SELECT * FROM t1;
|
|
--echo # Clean up
|
|
DROP TABLE t1;
|
|
|
|
--echo # Test case 8.3: check it for AFTER DELETE trigger
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1);
|
|
--delimiter $
|
|
CREATE TRIGGER t1_ad AFTER DELETE ON t1 FOR EACH ROW
|
|
BEGIN
|
|
IF OLD.a = 1 THEN
|
|
SIGNAL SQLSTATE '02TRG';
|
|
END IF;
|
|
END
|
|
$
|
|
--delimiter ;
|
|
|
|
--echo # There is no a handler for the signal raised from inside
|
|
--echo # the trigger t1_ad, so the statement DELETE fails
|
|
--echo # with the error ER_SIGNAL_NOT_FOUND
|
|
--error ER_SIGNAL_NOT_FOUND
|
|
DELETE FROM t1;
|
|
SELECT * FROM t1;
|
|
|
|
--echo # Clean up
|
|
DROP TABLE t1;
|
|
|
|
--echo # End of 11.8 tests
|