3
SET @sql_mode_session = @@SESSION.sql_mode;
4
SET @sql_mode_global = @@GLOBAL.sql_mode;
5
CREATE TEMPORARY TABLE t1(a varchar(20) not null, b varchar(20));
6
CREATE TEMPORARY TABLE t2(a varchar(20) null, b varchar(20));
7
'#--------------------FN_DYNVARS_153_01-------------------------#'
9
SET SESSION sql_mode = ANSI;
10
SELECT @@SESSION.sql_mode;
12
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
13
INSERT INTO t1 VALUES('t1a1','t1b1');
14
INSERT INTO t1 VALUES('t1a2','t1b2');
15
INSERT INTO t1 VALUES('t1a3','t1b3');
16
INSERT INTO t1 VALUES('t1a4','t1b4');
17
INSERT INTO t2 VALUES('t2a1','t2b1');
18
INSERT INTO t2 VALUES('t2a2','t2b2');
19
INSERT INTO t2 VALUES('t2a3','t2b3');
20
INSERT INTO t2 VALUES('t2a4','t2b4');
21
SELECT * FROM t1 WHERE t1.a IN (SELECT MAX(t1.b) FROM t2);
22
ERROR HY000: Invalid use of group function
23
Expected error for invalid group function
24
SET SESSION sql_mode = @sql_mode_session;
25
SET GLOBAL sql_mode = @sql_mode_global;
26
'#--------------------FN_DYNVARS_153_02-------------------------#'
27
STRICT_TRANS_TABLES mode
30
SET SESSION sql_mode = STRICT_TRANS_TABLES;
31
SELECT @@SESSION.sql_mode;
34
INSERT INTO t1 VALUES('t1a1','t1b1');
35
INSERT INTO t1 VALUES('t1a2','t1b2');
36
INSERT INTO t1 VALUES('t1a3','t1b3');
37
INSERT INTO t1 VALUES('t1a4','t1b4');
38
INSERT INTO t2 VALUES(NULL,'t2b1');
39
INSERT INTO t2 VALUES('t2a2','t2b2');
40
INSERT INTO t2 VALUES('t2a3','t2b3');
41
INSERT INTO t2 VALUES('t2a4','t2b4');
42
INSERT INTO t1 SELECT * FROM t2;
43
ERROR 23000: Column 'a' cannot be null
44
Expected error Bad NULL value
45
SELECT * FROM t1 WHERE a = 't2a2';
47
Should return no rows in columns a,b;
48
SET SESSION sql_mode = @sql_mode_session;
49
SET GLOBAL sql_mode = @sql_mode_global;
50
'#--------------------FN_DYNVARS_153_03-------------------------#'
52
SET SESSION sql_mode = TRADITIONAL;
53
SELECT @@SESSION.sql_mode;
55
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
56
INSERT INTO t1 VALUES('t1a1','t1b1');
57
INSERT INTO t1 VALUES('t1a2','t1b2');
58
INSERT INTO t1 VALUES('t1a3','t1b3');
59
INSERT INTO t1 VALUES('t1a4','t1b4');
60
INSERT INTO t2 VALUES(NULL,'t2b1');
61
INSERT INTO t2 VALUES('t2a2','t2b2');
62
INSERT INTO t2 VALUES('t2a3','t2b3');
63
INSERT INTO t2 VALUES('t2a4','t2b4');
64
INSERT INTO t1 SELECT * FROM t2;
65
ERROR 23000: Column 'a' cannot be null
66
Expected error Bad NULL value
67
SELECT * FROM t1 WHERE a = 't2a2';
69
Should return no rows in columns a,b;
70
'#-------------------FN_DYNVARS_153_04-------------------------#'
71
SET GLOBAL sql_mode = '';
72
** Connecting con_int1 using root **
73
** Connection con_int1 **
74
SELECT @@SESSION.sql_mode;
78
SET SESSION sql_mode = 'TRADITIONAL';
79
** Connecting con_int2 using root **
80
** Connection con_int2 **
81
SELECT @@SESSION.sql_mode;
85
SET SESSION sql_mode = 'ANSI';
86
** Connection con_int2 **
87
SELECT @@SESSION.sql_mode;
89
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
90
'REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ANSI' Expected
91
** Connection con_int1 **
92
SELECT @@SESSION.sql_mode;
94
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
95
'STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, TRADITIONAL' Expected
96
SELECT @@GLOBAL.sql_mode;
100
** Connection default **
101
Disconnecting Connections con_int1, con_int2
102
SET SESSION sql_mode = @sql_mode_session;
103
SET GLOBAL sql_mode = @sql_mode_global;