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