Debian Codegraph @

mysql-test/suite/binlog/t/binlog_stm_unsafe_default_expression_in_trigger.test

raw
1 # ==== Purpose ====
2 #
3 # Test warnings for unsafe statements generated inside the DEFAULT expression
4 # of a column in a table modified by a triggered statement.
5 #
6 # ==== Requirements ====
7 #
8 # R1. If a statement triggers another statement which may modify a table that
9 # has a column with a non-deterministic DEFAULT expression, a warning
10 # should be raised for BINLOG_FORMAT=STATEMENT, specifically an unsafe
11 # statement warning - ER_BINLOG_UNSAFE_DEFAULT_EXPRESSION_IN_SUBSTATEMENT.
12 #
13 # R2. If none of the triggered statements modifies a table that has a
14 # non-deterministic DEFAULT expression, and the triggering statement is not
15 # unsafe by itself, no warning shall be raised.
16 #
17 # R3. If a statement directly modifies a table with a column that has a
18 # non-deterministic DEFAULT expression, and the statement provides an
19 # explicit value for the column, no warning shall be raised.
20 #
21 # ==== Implementation ====
22 #
23 # Test firing triggers which modify tables with non-deterministic column
24 # defaults, and verify that the expected warnings are raised.
25 #
26 # ==== References ====
27 #
28 # WL#9418: Permit default value to be a function or expression, support for LOBs
29 # Bug#28297486: WL#9418: ERROR AND WARNING RELATED ISSUES
30 #
31
32 --source include/have_binlog_format_statement.inc
33
34 CALL mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.');
35
36 CREATE TABLE t1 (a DOUBLE DEFAULT (PI()),
37 b DOUBLE DEFAULT (RAND()));
38 CREATE TABLE t2 (x INT);
39 CREATE TABLE t3 (y DOUBLE DEFAULT (PI()));
40
41 --echo # Test R1.
42
43 CREATE TRIGGER t2_trigger AFTER INSERT ON t2
44 FOR EACH ROW INSERT INTO t1 VALUES ();
45 # Expect warning due to unsafe DEFAULT expression.
46 INSERT INTO t2 VALUES ();
47 DROP TRIGGER t2_trigger;
48
49 CREATE TRIGGER t2_trigger AFTER INSERT ON t2
50 FOR EACH ROW INSERT INTO t1(a) VALUES (1);
51 # Expect warning due to unsafe DEFAULT expression.
52 INSERT INTO t2 VALUES ();
53 DROP TRIGGER t2_trigger;
54
55 CREATE TRIGGER t2_trigger AFTER INSERT ON t2
56 FOR EACH ROW INSERT INTO t1(b) VALUES (DEFAULT);
57 # Expect warning due to unsafe DEFAULT expression.
58 INSERT INTO t2 VALUES ();
59 DROP TRIGGER t2_trigger;
60
61 CREATE TRIGGER t2_trigger AFTER INSERT ON t2
62 FOR EACH ROW INSERT INTO t1(b) VALUES (1);
63 # Not really unsafe, since an explicit value is given instead of DEFAULT.
64 # A warning is raised regardless.
65 INSERT INTO t2 VALUES ();
66 DROP TRIGGER t2_trigger;
67
68 CREATE TRIGGER t2_trigger AFTER INSERT ON t2
69 FOR EACH ROW UPDATE t1 SET a = DEFAULT;
70 # Not really unsafe, since the DEFAULT expression for a is safe.
71 # A warning is raised regardless.
72 INSERT INTO t2 VALUES ();
73 DROP TRIGGER t2_trigger;
74
75 CREATE TRIGGER t2_trigger AFTER INSERT ON t2
76 FOR EACH ROW UPDATE t1 SET b = DEFAULT;
77 # Expect warning due to unsafe DEFAULT expression for b.
78 INSERT INTO t2 VALUES ();
79 DROP TRIGGER t2_trigger;
80
81 --echo # Test R2.
82
83 CREATE TRIGGER t2_trigger AFTER INSERT ON t2
84 FOR EACH ROW INSERT INTO t3 VALUES ();
85 # No warning, since the triggered statement does not touch a table with any
86 # non-deterministic DEFAULT expressions.
87 INSERT INTO t2 VALUES ();
88 DROP TRIGGER t2_trigger;
89
90 --echo # Test R3.
91
92 # Warn when the DEFAULT expression is used.
93 INSERT INTO t1 VALUES ();
94 INSERT INTO t1 (b) VALUES (DEFAULT);
95 # No warning when the DEFAULT expression is not used.
96 INSERT INTO t1 (b) VALUES (1);
97
98 # Clean up
99 DROP TABLE t1, t2, t3;