1** Setup **
2
3SET @sql_mode_session = @@SESSION.sql_mode;
4SET @sql_mode_global = @@GLOBAL.sql_mode;
5CREATE TEMPORARY TABLE t1(a varchar(20) not null, b varchar(20));
6CREATE TEMPORARY TABLE t2(a varchar(20) null, b varchar(20));
7'#--------------------FN_DYNVARS_153_01-------------------------#'
8ANSI mode
9SET SESSION sql_mode = ANSI;
10SELECT @@SESSION.sql_mode;
11@@SESSION.sql_mode
12REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
13INSERT INTO t1 VALUES('t1a1','t1b1');
14INSERT INTO t1 VALUES('t1a2','t1b2');
15INSERT INTO t1 VALUES('t1a3','t1b3');
16INSERT INTO t1 VALUES('t1a4','t1b4');
17INSERT INTO t2 VALUES('t2a1','t2b1');
18INSERT INTO t2 VALUES('t2a2','t2b2');
19INSERT INTO t2 VALUES('t2a3','t2b3');
20INSERT INTO t2 VALUES('t2a4','t2b4');
21SELECT * FROM t1 WHERE t1.a IN (SELECT MAX(t1.b) FROM t2);
22ERROR HY000: Invalid use of group function
23Expected error for invalid group function
24SET SESSION sql_mode = @sql_mode_session;
25SET GLOBAL sql_mode = @sql_mode_global;
26'#--------------------FN_DYNVARS_153_02-------------------------#'
27STRICT_TRANS_TABLES mode
28DELETE FROM t1;
29DELETE FROM t2;
30SET SESSION sql_mode = STRICT_TRANS_TABLES;
31SELECT @@SESSION.sql_mode;
32@@SESSION.sql_mode
33STRICT_TRANS_TABLES
34INSERT INTO t1 VALUES('t1a1','t1b1');
35INSERT INTO t1 VALUES('t1a2','t1b2');
36INSERT INTO t1 VALUES('t1a3','t1b3');
37INSERT INTO t1 VALUES('t1a4','t1b4');
38INSERT INTO t2 VALUES(NULL,'t2b1');
39INSERT INTO t2 VALUES('t2a2','t2b2');
40INSERT INTO t2 VALUES('t2a3','t2b3');
41INSERT INTO t2 VALUES('t2a4','t2b4');
42INSERT INTO t1 SELECT * FROM t2;
43ERROR 23000: Column 'a' cannot be null
44Expected error Bad NULL value
45SELECT * FROM t1 WHERE a = 't2a2';
46a	b
47Should return no rows in columns a,b;
48SET SESSION sql_mode = @sql_mode_session;
49SET GLOBAL sql_mode = @sql_mode_global;
50'#--------------------FN_DYNVARS_153_03-------------------------#'
51TRADITIONAL  mode
52SET SESSION sql_mode = TRADITIONAL;
53SELECT @@SESSION.sql_mode;
54@@SESSION.sql_mode
55STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
56INSERT INTO t1 VALUES('t1a1','t1b1');
57INSERT INTO t1 VALUES('t1a2','t1b2');
58INSERT INTO t1 VALUES('t1a3','t1b3');
59INSERT INTO t1 VALUES('t1a4','t1b4');
60INSERT INTO t2 VALUES(NULL,'t2b1');
61INSERT INTO t2 VALUES('t2a2','t2b2');
62INSERT INTO t2 VALUES('t2a3','t2b3');
63INSERT INTO t2 VALUES('t2a4','t2b4');
64INSERT INTO t1 SELECT * FROM t2;
65ERROR 23000: Column 'a' cannot be null
66Expected error Bad NULL value
67SELECT * FROM t1 WHERE a = 't2a2';
68a	b
69Should return no rows in columns a,b;
70'#-------------------FN_DYNVARS_153_04-------------------------#'
71SET GLOBAL sql_mode = '';
72connect  con_int1,localhost,root,,;
73connection con_int1;
74SELECT @@SESSION.sql_mode;
75@@SESSION.sql_mode
76
77'' Expected
78SET SESSION sql_mode = 'TRADITIONAL';
79connect  con_int2,localhost,root,,;
80connection con_int2;
81SELECT @@SESSION.sql_mode;
82@@SESSION.sql_mode
83
84'' Expected
85SET SESSION sql_mode = 'ANSI';
86connection con_int2;
87SELECT @@SESSION.sql_mode;
88@@SESSION.sql_mode
89REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI
90'REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ANSI' Expected
91connection con_int1;
92SELECT @@SESSION.sql_mode;
93@@SESSION.sql_mode
94STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
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
96SELECT @@GLOBAL.sql_mode;
97@@GLOBAL.sql_mode
98
99'' Expected
100connection default;
101disconnect con_int1;
102disconnect con_int2;
103SET SESSION sql_mode = @sql_mode_session;
104SET GLOBAL sql_mode = @sql_mode_global;
105DROP TABLE t1;
106DROP TABLE t2;
107