| 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; |