Debian Codegraph @

mysql-test/suite/rpl_nogtid/t/rpl_nogtid_stmt_default_as_expr.test

raw
1 # ==== Purpose ====
2 #
3 # Test errors/warnings for Unsafe statement generated inside DEFAULT expression
4 #
5 # ==== Implementation ====
6 #
7 # CREATE TABLE with DEFAULT expression defined by a non-deterministic function
8 #
9 # ==== References ====
10 # WL#9418 Permit default value to be a function or expression, support for LOBs
11 #
12
13 --source include/have_binlog_format_statement.inc
14 --source include/master-slave.inc
15
16 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
17
18 --echo # Create tables on master
19 CREATE TABLE v1 (i VARCHAR(36) DEFAULT (RAND()), j VARCHAR(36) DEFAULT (PI()));
20 --echo # No warning expected as both columns are defined
21 INSERT INTO v1(i, j) VALUES ("a","b");
22 --echo # No warning expected as both columns are defined
23 INSERT INTO v1 VALUES ("a","b");
24 --echo # No warning expected as j is deterministic
25 INSERT INTO v1(i) VALUES ("a");
26 --echo # No warning expected as j is deterministic
27 INSERT INTO v1(i,j) VALUES ("a",DEFAULT);
28 --echo # Warning expected as i is non-deterministic
29 INSERT INTO v1(j) VALUES ("b");
30 --echo # Warning expected as i is non-deterministic
31 INSERT INTO v1(i,j) VALUES (DEFAULT,"b");
32 --echo # Warning expected as i is non-deterministic
33 INSERT INTO v1() VALUES();
34 DROP TABLE v1;
35
36 CREATE TABLE t1(
37 id INTEGER NOT NULL AUTO_INCREMENT,
38 token VARCHAR(255) DEFAULT (UUID()),
39 PRIMARY KEY (id),
40 KEY token (token)
41 );
42 --echo # Warning expected as token is non-deterministic
43 INSERT INTO t1 VALUES (3, "abc"), (DEFAULT, DEFAULT);
44 --echo # No warning expected as token is defined
45 INSERT INTO t1 VALUES (1, "abc"), (2, "def");
46 DROP TABLE t1;
47
48
49 CREATE TABLE t2 (i VARCHAR(36) DEFAULT (UUID()));
50
51 --echo # Regular insert, no warnings
52 INSERT INTO t2(i) VALUES('MySQL');
53
54 --echo # Making sure that warnings are shown
55 INSERT INTO t2(i) VALUES(DEFAULT);
56 INSERT INTO t2() VALUES();
57
58 # random values
59 SELECT COUNT(*) from t2;
60
61 create table t1 ( i double DEFAULT ( RAND() ) ) ;
62 insert into t1 values (1),(2) ;
63 #expect warning here
64 update t1 set i = DEFAULT where i = 2 ;
65 drop table t1;
66
67 SET @start_enforce_gtid_consistency = @@GLOBAL.ENFORCE_GTID_CONSISTENCY;
68 SET GLOBAL ENFORCE_GTID_CONSISTENCY = WARN;
69 ALTER TABLE t2 ADD COLUMN c char(36) DEFAULT (PI());
70 SET GLOBAL ENFORCE_GTID_CONSISTENCY = @start_enforce_gtid_consistency;
71
72 --source include/sync_slave_sql_with_master.inc
73 --echo [ slave connection ]
74
75 SHOW TABLES;
76 SHOW CREATE TABLE t2 ;
77 SELECT COUNT(*) from t2;
78
79 --echo # clean up
80 --source include/rpl_connection_master.inc
81 DROP TABLE t2;
82 --source include/rpl_end.inc