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,ONLY_FULL_GROUP_BY,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 Warnings:
32 Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
33 SELECT @@SESSION.sql_mode;
34 @@SESSION.sql_mode
35 STRICT_TRANS_TABLES
36 INSERT INTO t1 VALUES('t1a1','t1b1');
37 INSERT INTO t1 VALUES('t1a2','t1b2');
38 INSERT INTO t1 VALUES('t1a3','t1b3');
39 INSERT INTO t1 VALUES('t1a4','t1b4');
40 INSERT INTO t2 VALUES(NULL,'t2b1');
41 INSERT INTO t2 VALUES('t2a2','t2b2');
42 INSERT INTO t2 VALUES('t2a3','t2b3');
43 INSERT INTO t2 VALUES('t2a4','t2b4');
44 INSERT INTO t1 SELECT * FROM t2;
45 ERROR 23000: Column 'a' cannot be null
46 Expected error Bad NULL value
47 SELECT * FROM t1 WHERE a = 't2a2';
48 a b
49 Should return no rows in columns a,b;
50 SET SESSION sql_mode = @sql_mode_session;
51 SET GLOBAL sql_mode = @sql_mode_global;
52 '#--------------------FN_DYNVARS_153_03-------------------------#'
53 TRADITIONAL mode
54 SET SESSION sql_mode = TRADITIONAL;
55 SELECT @@SESSION.sql_mode;
56 @@SESSION.sql_mode
57 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION
58 INSERT INTO t1 VALUES('t1a1','t1b1');
59 INSERT INTO t1 VALUES('t1a2','t1b2');
60 INSERT INTO t1 VALUES('t1a3','t1b3');
61 INSERT INTO t1 VALUES('t1a4','t1b4');
62 INSERT INTO t2 VALUES(NULL,'t2b1');
63 INSERT INTO t2 VALUES('t2a2','t2b2');
64 INSERT INTO t2 VALUES('t2a3','t2b3');
65 INSERT INTO t2 VALUES('t2a4','t2b4');
66 INSERT INTO t1 SELECT * FROM t2;
67 ERROR 23000: Column 'a' cannot be null
68 Expected error Bad NULL value
69 SELECT * FROM t1 WHERE a = 't2a2';
70 a b
71 Should return no rows in columns a,b;
72 '#-------------------FN_DYNVARS_153_04-------------------------#'
73 SET GLOBAL sql_mode = '';
74 ** Connecting con_int1 using root **
75 ** Connection con_int1 **
76 SELECT @@SESSION.sql_mode;
77 @@SESSION.sql_mode
78
79 '' Expected
80 SET SESSION sql_mode = 'TRADITIONAL';
81 ** Connecting con_int2 using root **
82 ** Connection con_int2 **
83 SELECT @@SESSION.sql_mode;
84 @@SESSION.sql_mode
85
86 '' Expected
87 SET SESSION sql_mode = 'ANSI';
88 ** Connection con_int2 **
89 SELECT @@SESSION.sql_mode;
90 @@SESSION.sql_mode
91 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ANSI
92 'REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ANSI' Expected
93 ** Connection con_int1 **
94 SELECT @@SESSION.sql_mode;
95 @@SESSION.sql_mode
96 STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION
97 'STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL' Expected
98 SELECT @@GLOBAL.sql_mode;
99 @@GLOBAL.sql_mode
100
101 '' Expected
102 ** Connection default **
103 Disconnecting Connections con_int1, con_int2
104 SET SESSION sql_mode = @sql_mode_session;
105 SET GLOBAL sql_mode = @sql_mode_global;
106 DROP TABLE t1;
107 DROP TABLE t2;