# 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