Debian Codegraph @

mysql-test/suite/sys_vars/r/sql_mode_func.result

raw
1 ** Setup **
2
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-------------------------#'
8 ANSI mode
9 SET SESSION sql_mode = ANSI;
10 SELECT @@SESSION.sql_mode;
11 @@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
28 DELETE FROM t1;
29 DELETE FROM t2;
30 SET SESSION sql_mode = STRICT_TRANS_TABLES;
31 SELECT @@SESSION.sql_mode;
32 @@SESSION.sql_mode
33 STRICT_TRANS_TABLES
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';
46 a b
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-------------------------#'
51 TRADITIONAL mode
52 SET SESSION sql_mode = TRADITIONAL;
53 SELECT @@SESSION.sql_mode;
54 @@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,NO_ENGINE_SUBSTITUTION
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';
68 a b
69 Should return no rows in columns a,b;
70 '#-------------------FN_DYNVARS_153_04-------------------------#'
71 SET GLOBAL sql_mode = '';
72 connect con_int1,localhost,root,,;
73 connection con_int1;
74 SELECT @@SESSION.sql_mode;
75 @@SESSION.sql_mode
76
77 '' Expected
78 SET SESSION sql_mode = 'TRADITIONAL';
79 connect con_int2,localhost,root,,;
80 connection con_int2;
81 SELECT @@SESSION.sql_mode;
82 @@SESSION.sql_mode
83
84 '' Expected
85 SET SESSION sql_mode = 'ANSI';
86 connection con_int2;
87 SELECT @@SESSION.sql_mode;
88 @@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;
93 @@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,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
96 SELECT @@GLOBAL.sql_mode;
97 @@GLOBAL.sql_mode
98
99 '' Expected
100 connection default;
101 disconnect con_int1;
102 disconnect con_int2;
103 SET SESSION sql_mode = @sql_mode_session;
104 SET GLOBAL sql_mode = @sql_mode_global;
105 DROP TABLE t1;
106 DROP TABLE t2;