1drop table if exists t1, t2; 2select null,\N,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null; 3NULL NULL isnull(null) isnull(1/0) isnull(1/0 = null) ifnull(null,1) ifnull(null,"TRUE") ifnull("TRUE","ERROR") 1/0 is null 1 is not null 4NULL NULL 1 1 1 1 TRUE TRUE 1 1 5Warnings: 6Warning 1365 Division by 0 7Warning 1365 Division by 0 8Warning 1365 Division by 0 9explain extended select null,\N,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null; 10id select_type table type possible_keys key key_len ref rows filtered Extra 111 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 12Warnings: 13Note 1003 select NULL AS `NULL`,NULL AS `NULL`,NULL is null AS `isnull(null)`,1 / 0 is null AS `isnull(1/0)`,1 / 0 = NULL is null AS `isnull(1/0 = null)`,ifnull(NULL,1) AS `ifnull(null,1)`,ifnull(NULL,'TRUE') AS `ifnull(null,"TRUE")`,ifnull('TRUE','ERROR') AS `ifnull("TRUE","ERROR")`,1 / 0 is null AS `1/0 is null`,1 is not null AS `1 is not null` 14select 1 | NULL,1 & NULL,1+NULL,1-NULL; 151 | NULL 1 & NULL 1+NULL 1-NULL 16NULL NULL NULL NULL 17select NULL=NULL,NULL<>NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0; 18NULL=NULL NULL<>NULL IFNULL(NULL,1.1)+0 IFNULL(NULL,1) | 0 19NULL NULL 1.1 1 20select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null; 21strcmp("a",NULL) (1<NULL)+0.0 NULL regexp "a" null like "a%" "a%" like null 22NULL NULL NULL NULL NULL 23select concat("a",NULL),replace(NULL,"a","b"),replace("string","i",NULL),replace("string",NULL,"i"),insert("abc",1,1,NULL),left(NULL,1); 24concat("a",NULL) replace(NULL,"a","b") replace("string","i",NULL) replace("string",NULL,"i") insert("abc",1,1,NULL) left(NULL,1) 25NULL NULL NULL NULL NULL NULL 26select repeat("a",0),repeat("ab",5+5),repeat("ab",-1),reverse(NULL); 27repeat("a",0) repeat("ab",5+5) repeat("ab",-1) reverse(NULL) 28 abababababababababab NULL 29select field(NULL,"a","b","c"); 30field(NULL,"a","b","c") 310 32select 2 between null and 1,2 between 3 AND NULL,NULL between 1 and 2,2 between NULL and 3, 2 between 1 AND null; 332 between null and 1 2 between 3 AND NULL NULL between 1 and 2 2 between NULL and 3 2 between 1 AND null 340 0 NULL NULL NULL 35explain extended select 2 between null and 1,2 between 3 AND NULL,NULL between 1 and 2,2 between NULL and 3, 2 between 1 AND null; 36id select_type table type possible_keys key key_len ref rows filtered Extra 371 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 38Warnings: 39Note 1003 select 2 between NULL and 1 AS `2 between null and 1`,2 between 3 and NULL AS `2 between 3 AND NULL`,NULL between 1 and 2 AS `NULL between 1 and 2`,2 between NULL and 3 AS `2 between NULL and 3`,2 between 1 and NULL AS `2 between 1 AND null` 40SELECT NULL AND NULL, 1 AND NULL, NULL AND 1, NULL OR NULL, 0 OR NULL, NULL OR 0; 41NULL AND NULL 1 AND NULL NULL AND 1 NULL OR NULL 0 OR NULL NULL OR 0 42NULL NULL NULL NULL NULL NULL 43SELECT (NULL OR NULL) IS NULL; 44(NULL OR NULL) IS NULL 451 46select NULL AND 0, 0 and NULL; 47NULL AND 0 0 and NULL 480 0 49select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton(""); 50inet_ntoa(null) inet_aton(null) inet_aton("122.256") inet_aton("122.226.") inet_aton("") 51NULL NULL NULL NULL NULL 52explain extended select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton(""); 53id select_type table type possible_keys key key_len ref rows filtered Extra 541 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 55Warnings: 56Note 1003 select inet_ntoa(NULL) AS `inet_ntoa(null)`,inet_aton(NULL) AS `inet_aton(null)`,inet_aton('122.256') AS `inet_aton("122.256")`,inet_aton('122.226.') AS `inet_aton("122.226.")`,inet_aton('') AS `inet_aton("")` 57select not null is true, not null or true, not null and false, not null <=> null; 58not null is true not null or true not null and false not null <=> null 591 1 0 0 60create table t1 (x int); 61insert into t1 values (null); 62select * from t1 where x != 0; 63x 64drop table t1; 65CREATE TABLE t1 ( 66indexed_field int default NULL, 67KEY indexed_field (indexed_field) 68); 69INSERT INTO t1 VALUES (NULL),(NULL); 70SELECT * FROM t1 WHERE indexed_field=NULL; 71indexed_field 72SELECT * FROM t1 WHERE indexed_field IS NULL; 73indexed_field 74NULL 75NULL 76SELECT * FROM t1 WHERE indexed_field<=>NULL; 77indexed_field 78NULL 79NULL 80DROP TABLE t1; 81create table t1 (a int, b int) engine=myisam; 82insert into t1 values(20,null); 83select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 84t2.b=t3.a; 85b ifnull(t2.b,"this is null") 86NULL this is null 87select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 88t2.b=t3.a order by 1; 89b ifnull(t2.b,"this is null") 90NULL this is null 91insert into t1 values(10,null); 92select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on 93t2.b=t3.a order by 1; 94b ifnull(t2.b,"this is null") 95NULL this is null 96NULL this is null 97drop table t1; 98CREATE TABLE t1 (a varchar(16) NOT NULL default '', b smallint(6) NOT NULL default 0, c datetime NOT NULL default '0000-00-00 00:00:00', d smallint(6) NOT NULL default 0); 99INSERT IGNORE INTO t1 SET a = "", d= "2003-01-14 03:54:55"; 100Warnings: 101Warning 1265 Data truncated for column 'd' at row 1 102UPDATE IGNORE t1 SET d=1/NULL; 103Warnings: 104Warning 1048 Column 'd' cannot be null 105UPDATE IGNORE t1 SET d=NULL; 106Warnings: 107Warning 1048 Column 'd' cannot be null 108INSERT INTO t1 (a) values (null); 109ERROR 23000: Column 'a' cannot be null 110INSERT INTO t1 (a) values (1/null); 111ERROR 23000: Column 'a' cannot be null 112INSERT IGNORE INTO t1 (a) values (null),(null); 113Warnings: 114Warning 1048 Column 'a' cannot be null 115Warning 1048 Column 'a' cannot be null 116INSERT INTO t1 (b) values (null); 117ERROR 23000: Column 'b' cannot be null 118INSERT INTO t1 (b) values (1/null); 119ERROR 23000: Column 'b' cannot be null 120INSERT IGNORE INTO t1 (b) values (null),(null); 121Warnings: 122Warning 1048 Column 'b' cannot be null 123Warning 1048 Column 'b' cannot be null 124INSERT INTO t1 (c) values (null); 125ERROR 23000: Column 'c' cannot be null 126INSERT INTO t1 (c) values (1/null); 127ERROR 23000: Column 'c' cannot be null 128INSERT IGNORE INTO t1 (c) values (null),(null); 129Warnings: 130Warning 1048 Column 'c' cannot be null 131Warning 1048 Column 'c' cannot be null 132INSERT INTO t1 (d) values (null); 133ERROR 23000: Column 'd' cannot be null 134INSERT INTO t1 (d) values (1/null); 135ERROR 23000: Column 'd' cannot be null 136INSERT IGNORE INTO t1 (d) values (null),(null); 137Warnings: 138Warning 1048 Column 'd' cannot be null 139Warning 1048 Column 'd' cannot be null 140select * from t1; 141a b c d 142 0 0000-00-00 00:00:00 0 143 0 0000-00-00 00:00:00 0 144 0 0000-00-00 00:00:00 0 145 0 0000-00-00 00:00:00 0 146 0 0000-00-00 00:00:00 0 147 0 0000-00-00 00:00:00 0 148 0 0000-00-00 00:00:00 0 149 0 0000-00-00 00:00:00 0 150 0 0000-00-00 00:00:00 0 151drop table t1; 152create table t1 (a int not null, b int not null, index idx(a)); 153insert into t1 values 154(1,1), (2,2), (3,3), (4,4), (5,5), (6,6), 155(7,7), (8,8), (9,9), (10,10), (11,11), (12,12); 156explain select * from t1 where a between 2 and 3; 157id select_type table type possible_keys key key_len ref rows Extra 1581 SIMPLE t1 range idx idx 4 NULL 2 Using index condition 159explain select * from t1 where a between 2 and 3 or b is null; 160id select_type table type possible_keys key key_len ref rows Extra 1611 SIMPLE t1 range idx idx 4 NULL 2 Using index condition 162drop table t1; 163select cast(NULL as signed); 164cast(NULL as signed) 165NULL 166create table t1(i int, key(i)); 167insert into t1 values(1); 168insert into t1 select i*2 from t1; 169insert into t1 select i*2 from t1; 170insert into t1 select i*2 from t1; 171insert into t1 select i*2 from t1; 172insert into t1 select i*2 from t1; 173insert into t1 select i*2 from t1; 174insert into t1 select i*2 from t1; 175insert into t1 select i*2 from t1; 176insert into t1 select i*2 from t1; 177insert into t1 values(null); 178explain select * from t1 where i=2 or i is null; 179id select_type table type possible_keys key key_len ref rows Extra 1801 SIMPLE t1 ref_or_null i i 5 const 9 Using where; Using index 181select count(*) from t1 where i=2 or i is null; 182count(*) 18310 184SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 185alter table t1 change i i int not null; 186Warnings: 187Warning 1265 Data truncated for column 'i' at row 513 188explain select * from t1 where i=2 or i is null; 189id select_type table type possible_keys key key_len ref rows Extra 1901 SIMPLE t1 ref i i 4 const 8 Using index 191select count(*) from t1 where i=2 or i is null; 192count(*) 1939 194drop table t1; 195set names latin2; 196create table t1 select 197null as c00, 198if(1, null, 'string') as c01, 199if(0, null, 'string') as c02, 200ifnull(null, 'string') as c03, 201ifnull('string', null) as c04, 202case when 0 then null else 'string' end as c05, 203case when 1 then null else 'string' end as c06, 204coalesce(null, 'string') as c07, 205coalesce('string', null) as c08, 206least('string',null) as c09, 207least(null, 'string') as c10, 208greatest('string',null) as c11, 209greatest(null, 'string') as c12, 210nullif('string', null) as c13, 211nullif(null, 'string') as c14, 212trim('string' from null) as c15, 213trim(null from 'string') as c16, 214substring_index('string', null, 1) as c17, 215substring_index(null, 'string', 1) as c18, 216elt(1, null, 'string') as c19, 217elt(1, 'string', null) as c20, 218concat('string', null) as c21, 219concat(null, 'string') as c22, 220concat_ws('sep', 'string', null) as c23, 221concat_ws('sep', null, 'string') as c24, 222concat_ws(null, 'string', 'string') as c25, 223make_set(3, 'string', null) as c26, 224make_set(3, null, 'string') as c27, 225export_set(3, null, 'off', 'sep') as c29, 226export_set(3, 'on', null, 'sep') as c30, 227export_set(3, 'on', 'off', null) as c31, 228replace(null, 'from', 'to') as c32, 229replace('str', null, 'to') as c33, 230replace('str', 'from', null) as c34, 231insert('str', 1, 2, null) as c35, 232insert(null, 1, 2, 'str') as c36, 233lpad('str', 10, null) as c37, 234rpad(null, 10, 'str') as c38; 235show create table t1; 236Table Create Table 237t1 CREATE TABLE `t1` ( 238 `c00` binary(0) DEFAULT NULL, 239 `c01` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 240 `c02` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 241 `c03` varchar(6) CHARACTER SET latin2 NOT NULL, 242 `c04` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 243 `c05` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 244 `c06` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 245 `c07` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 246 `c08` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 247 `c09` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 248 `c10` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 249 `c11` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 250 `c12` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 251 `c13` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 252 `c14` binary(0) DEFAULT NULL, 253 `c15` char(0) CHARACTER SET latin2 DEFAULT NULL, 254 `c16` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 255 `c17` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 256 `c18` char(0) CHARACTER SET latin2 DEFAULT NULL, 257 `c19` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 258 `c20` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 259 `c21` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 260 `c22` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 261 `c23` varchar(9) CHARACTER SET latin2 DEFAULT NULL, 262 `c24` varchar(9) CHARACTER SET latin2 DEFAULT NULL, 263 `c25` varchar(12) CHARACTER SET latin2 DEFAULT NULL, 264 `c26` varchar(7) CHARACTER SET latin2 DEFAULT NULL, 265 `c27` varchar(7) CHARACTER SET latin2 DEFAULT NULL, 266 `c29` varchar(381) CHARACTER SET latin2 DEFAULT NULL, 267 `c30` varchar(317) CHARACTER SET latin2 DEFAULT NULL, 268 `c31` varchar(192) CHARACTER SET latin2 DEFAULT NULL, 269 `c32` char(0) CHARACTER SET latin2 DEFAULT NULL, 270 `c33` varchar(6) CHARACTER SET latin2 DEFAULT NULL, 271 `c34` varchar(3) CHARACTER SET latin2 DEFAULT NULL, 272 `c35` varchar(3) CHARACTER SET latin2 DEFAULT NULL, 273 `c36` varchar(3) CHARACTER SET latin2 DEFAULT NULL, 274 `c37` varchar(10) CHARACTER SET latin2 DEFAULT NULL, 275 `c38` varchar(10) CHARACTER SET latin2 DEFAULT NULL 276) ENGINE=MyISAM DEFAULT CHARSET=latin1 277drop table t1; 278select 279case 'str' when 'STR' then 'str' when null then 'null' end as c01, 280case 'str' when null then 'null' when 'STR' then 'str' end as c02, 281field(null, 'str1', 'str2') as c03, 282field('str1','STR1', null) as c04, 283field('str1', null, 'STR1') as c05, 284'string' in ('STRING', null) as c08, 285'string' in (null, 'STRING') as c09; 286c01 c02 c03 c04 c05 c08 c09 287str str 0 1 2 1 1 288set names latin1; 289create table bug19145a (e enum('a','b','c') default 'b' , s set('x', 'y', 'z') default 'y' ) engine=MyISAM; 290create table bug19145b (e enum('a','b','c') default null, s set('x', 'y', 'z') default null) engine=MyISAM; 291create table bug19145c (e enum('a','b','c') not null default 'b' , s set('x', 'y', 'z') not null default 'y' ) engine=MyISAM; 292create table bug19145setnotnulldefaultnull (e enum('a','b','c') default null, s set('x', 'y', 'z') not null default null) engine=MyISAM; 293ERROR 42000: Invalid default value for 's' 294create table bug19145enumnotnulldefaultnull (e enum('a','b','c') not null default null, s set('x', 'y', 'z') default null) engine=MyISAM; 295ERROR 42000: Invalid default value for 'e' 296alter table bug19145a alter column e set default null; 297alter table bug19145a alter column s set default null; 298alter table bug19145a add column (i int); 299alter table bug19145b alter column e set default null; 300alter table bug19145b alter column s set default null; 301alter table bug19145b add column (i int); 302alter table bug19145c alter column e set default null; 303ERROR 42000: Invalid default value for 'e' 304alter table bug19145c alter column s set default null; 305ERROR 42000: Invalid default value for 's' 306alter table bug19145c add column (i int); 307show create table bug19145a; 308Table Create Table 309bug19145a CREATE TABLE `bug19145a` ( 310 `e` enum('a','b','c') DEFAULT NULL, 311 `s` set('x','y','z') DEFAULT NULL, 312 `i` int(11) DEFAULT NULL 313) ENGINE=MyISAM DEFAULT CHARSET=latin1 314show create table bug19145b; 315Table Create Table 316bug19145b CREATE TABLE `bug19145b` ( 317 `e` enum('a','b','c') DEFAULT NULL, 318 `s` set('x','y','z') DEFAULT NULL, 319 `i` int(11) DEFAULT NULL 320) ENGINE=MyISAM DEFAULT CHARSET=latin1 321show create table bug19145c; 322Table Create Table 323bug19145c CREATE TABLE `bug19145c` ( 324 `e` enum('a','b','c') NOT NULL DEFAULT 'b', 325 `s` set('x','y','z') NOT NULL DEFAULT 'y', 326 `i` int(11) DEFAULT NULL 327) ENGINE=MyISAM DEFAULT CHARSET=latin1 328drop table bug19145a; 329drop table bug19145b; 330drop table bug19145c; 331# End of 4.1 tests 332# 333# Bug #31471: decimal_bin_size: Assertion `scale >= 0 && 334# precision > 0 && scale <= precision' 335# 336CREATE TABLE t1 (a DECIMAL (1, 0) ZEROFILL, b DECIMAL (1, 0) ZEROFILL); 337INSERT INTO t1 (a, b) VALUES (0, 0); 338CREATE TABLE t2 SELECT IFNULL(a, b) FROM t1; 339DESCRIBE t2; 340Field Type Null Key Default Extra 341IFNULL(a, b) decimal(1,0) unsigned YES NULL 342DROP TABLE t2; 343CREATE TABLE t2 SELECT IFNULL(a, NULL) FROM t1; 344DESCRIBE t2; 345Field Type Null Key Default Extra 346IFNULL(a, NULL) decimal(1,0) YES NULL 347DROP TABLE t2; 348CREATE TABLE t2 SELECT IFNULL(NULL, b) FROM t1; 349DESCRIBE t2; 350Field Type Null Key Default Extra 351IFNULL(NULL, b) decimal(1,0) YES NULL 352DROP TABLE t1, t2; 353# End of 5.0 tests 354# 355# MDEV-4895 Valgrind warnings (Conditional jump or move depends on uninitialised value) in Field_datetime::get_date on GREATEST(..) IS NULL 356# 357CREATE TABLE t1 (dt DATETIME NOT NULL); 358INSERT INTO t1 VALUES (NOW()),(NOW()); 359EXPLAIN 360SELECT * FROM t1 WHERE concat( dt, '2012-12-21 12:12:12' ) IS NULL; 361id select_type table type possible_keys key key_len ref rows Extra 3621 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 363SELECT * FROM t1 WHERE concat( dt, '2012-12-21 12:12:12' ) IS NULL; 364dt 365DROP TABLE t1; 366CREATE TABLE t1 (dt INT NOT NULL); 367INSERT INTO t1 VALUES (1),(2); 368EXPLAIN 369SELECT * FROM t1 WHERE concat( dt, '1' ) IS NULL; 370id select_type table type possible_keys key key_len ref rows Extra 3711 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 372SELECT * FROM t1 WHERE concat( dt, '1' ) IS NULL; 373dt 374DROP TABLE t1; 375CREATE TABLE t1 (dt INT NOT NULL); 376INSERT INTO t1 VALUES (1),(2); 377EXPLAIN 378SELECT * FROM t1 WHERE NOT (concat( dt, '1' ) IS NOT NULL); 379id select_type table type possible_keys key key_len ref rows Extra 3801 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where 381SELECT * FROM t1 WHERE NOT (concat( dt, '1' ) IS NOT NULL); 382dt 383DROP TABLE t1; 384# 385# Bug mdev-5132: crash when exeicuting a join query 386# with IS NULL and IS NOT NULL in where 387# 388CREATE TABLE t1 (a DATE, b INT, c INT, KEY(a), KEY(b), KEY(c)) ENGINE=MyISAM; 389CREATE TABLE t2 (d DATE) ENGINE=MyISAM; 390SELECT * FROM t1,t2 WHERE 1 IS NOT NULL AND t1.b IS NULL; 391a b c d 392DROP TABLE t1,t2; 393# 394# Start of 10.0 tests 395# 396# 397# MDEV-7001 Bad result for NOT NOT STRCMP('a','b') and NOT NOT NULLIF(2,3) 398# 399SELECT NOT NOT NULLIF(2,3); 400NOT NOT NULLIF(2,3) 4011 402# 403# End of 10.0 tests 404# 405# 406# Start of 10.1 tests 407# 408# 409# MDEV-7146 NULLIF returns unexpected result with a YEAR field 410# 411CREATE TABLE t1 (a YEAR(2)); 412Warnings: 413Note 1287 'YEAR(2)' is deprecated and will be removed in a future release. Please use YEAR(4) instead 414INSERT INTO t1 VALUES (0); 415SELECT a,NULLIF(a,2000),NULLIF(2000,a) FROM t1; 416a NULLIF(a,2000) NULLIF(2000,a) 41700 NULL NULL 418SELECT a,NULLIF(a,2001),NULLIF(2001,a) FROM t1; 419a NULLIF(a,2001) NULLIF(2001,a) 42000 0 2001 421DROP TABLE t1; 422# 423# MDEV-7005 NULLIF does not work as documented 424# 425CREATE TABLE t1 (a TIME); 426CREATE TABLE t2 AS SELECT a,NULLIF(a,a), CASE WHEN a=a THEN NULL ELSE a END FROM t1; 427SHOW CREATE TABLE t2; 428Table Create Table 429t2 CREATE TABLE `t2` ( 430 `a` time DEFAULT NULL, 431 `NULLIF(a,a)` time DEFAULT NULL, 432 `CASE WHEN a=a THEN NULL ELSE a END` time DEFAULT NULL 433) ENGINE=MyISAM DEFAULT CHARSET=latin1 434DROP TABLE t1,t2; 435SELECT NULLIF(_latin1'a' COLLATE latin1_general_ci, _latin1'a' COLLATE latin1_bin); 436ERROR HY000: Illegal mix of collations (latin1_general_ci,EXPLICIT) and (latin1_bin,EXPLICIT) for operation 'nullif' 437SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 438CREATE TABLE t1 AS SELECT 439NULLIF(1,1), 440NULLIF(1,1.0), 441NULLIF(1,1e0), 442NULLIF(1,'2001-01-01'), 443NULLIF(1,TIME'00:00:00'); 444Warnings: 445Warning 1292 Truncated incorrect DOUBLE value: '2001-01-01' 446SHOW CREATE TABLE t1; 447Table Create Table 448t1 CREATE TABLE `t1` ( 449 `NULLIF(1,1)` int(1) DEFAULT NULL, 450 `NULLIF(1,1.0)` int(1) DEFAULT NULL, 451 `NULLIF(1,1e0)` int(1) DEFAULT NULL, 452 `NULLIF(1,'2001-01-01')` int(1) DEFAULT NULL, 453 `NULLIF(1,TIME'00:00:00')` int(1) DEFAULT NULL 454) ENGINE=MyISAM DEFAULT CHARSET=latin1 455DROP TABLE t1; 456SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 457CREATE TABLE t1 AS SELECT 458NULLIF(1.0,1), 459NULLIF(1.0,1.0), 460NULLIF(1.0,1e0), 461NULLIF(1.0,'2001-01-01'), 462NULLIF(1.0,TIME'00:00:00'); 463Warnings: 464Warning 1292 Truncated incorrect DOUBLE value: '2001-01-01' 465SHOW CREATE TABLE t1; 466Table Create Table 467t1 CREATE TABLE `t1` ( 468 `NULLIF(1.0,1)` decimal(2,1) DEFAULT NULL, 469 `NULLIF(1.0,1.0)` decimal(2,1) DEFAULT NULL, 470 `NULLIF(1.0,1e0)` decimal(2,1) DEFAULT NULL, 471 `NULLIF(1.0,'2001-01-01')` decimal(2,1) DEFAULT NULL, 472 `NULLIF(1.0,TIME'00:00:00')` decimal(2,1) DEFAULT NULL 473) ENGINE=MyISAM DEFAULT CHARSET=latin1 474DROP TABLE t1; 475SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 476CREATE TABLE t1 AS SELECT 477NULLIF(1e0,1), 478NULLIF(1e0,1.0), 479NULLIF(1e0,1e0), 480NULLIF(1e0,'2001-01-01'), 481NULLIF(1e0,TIME'00:00:00'); 482Warnings: 483Warning 1292 Truncated incorrect DOUBLE value: '2001-01-01' 484SHOW CREATE TABLE t1; 485Table Create Table 486t1 CREATE TABLE `t1` ( 487 `NULLIF(1e0,1)` double DEFAULT NULL, 488 `NULLIF(1e0,1.0)` double DEFAULT NULL, 489 `NULLIF(1e0,1e0)` double DEFAULT NULL, 490 `NULLIF(1e0,'2001-01-01')` double DEFAULT NULL, 491 `NULLIF(1e0,TIME'00:00:00')` double DEFAULT NULL 492) ENGINE=MyISAM DEFAULT CHARSET=latin1 493DROP TABLE t1; 494CREATE TABLE t1 AS SELECT 495NULLIF('1',1), 496NULLIF('1',1.0), 497NULLIF('1',1e0), 498NULLIF('1','2001-01-01'), 499NULLIF('1',TIME'00:00:00'); 500SHOW CREATE TABLE t1; 501Table Create Table 502t1 CREATE TABLE `t1` ( 503 `NULLIF('1',1)` varchar(1) DEFAULT NULL, 504 `NULLIF('1',1.0)` varchar(1) DEFAULT NULL, 505 `NULLIF('1',1e0)` varchar(1) DEFAULT NULL, 506 `NULLIF('1','2001-01-01')` varchar(1) DEFAULT NULL, 507 `NULLIF('1',TIME'00:00:00')` varchar(1) DEFAULT NULL 508) ENGINE=MyISAM DEFAULT CHARSET=latin1 509DROP TABLE t1; 510SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 511CREATE TABLE t1 AS SELECT 512NULLIF(TIMESTAMP'2001-01-01 00:00:00',1), 513NULLIF(TIMESTAMP'2001-01-01 00:00:00',1.0), 514NULLIF(TIMESTAMP'2001-01-01 00:00:00',1e0), 515NULLIF(TIMESTAMP'2001-01-01 00:00:00','2001-01-01'), 516NULLIF(TIMESTAMP'2001-01-01 00:00:00',TIME'00:00:00'); 517Warnings: 518Warning 1292 Truncated incorrect datetime value: '1' 519Warning 1292 Truncated incorrect datetime value: '1.0' 520Warning 1292 Truncated incorrect datetime value: '1' 521SHOW CREATE TABLE t1; 522Table Create Table 523t1 CREATE TABLE `t1` ( 524 `NULLIF(TIMESTAMP'2001-01-01 00:00:00',1)` datetime DEFAULT NULL, 525 `NULLIF(TIMESTAMP'2001-01-01 00:00:00',1.0)` datetime DEFAULT NULL, 526 `NULLIF(TIMESTAMP'2001-01-01 00:00:00',1e0)` datetime DEFAULT NULL, 527 `NULLIF(TIMESTAMP'2001-01-01 00:00:00','2001-01-01')` datetime DEFAULT NULL, 528 `NULLIF(TIMESTAMP'2001-01-01 00:00:00',TIME'00:00:00')` datetime DEFAULT NULL 529) ENGINE=MyISAM DEFAULT CHARSET=latin1 530DROP TABLE t1; 531SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 532CREATE TABLE t1 AS SELECT 533NULLIF(DATE'2001-01-01',1), 534NULLIF(DATE'2001-01-01',1.0), 535NULLIF(DATE'2001-01-01',1e0), 536NULLIF(DATE'2001-01-01','2001-01-01'), 537NULLIF(DATE'2001-01-01',TIME'00:00:00'); 538Warnings: 539Warning 1292 Truncated incorrect datetime value: '1' 540Warning 1292 Truncated incorrect datetime value: '1.0' 541Warning 1292 Truncated incorrect datetime value: '1' 542SHOW CREATE TABLE t1; 543Table Create Table 544t1 CREATE TABLE `t1` ( 545 `NULLIF(DATE'2001-01-01',1)` date DEFAULT NULL, 546 `NULLIF(DATE'2001-01-01',1.0)` date DEFAULT NULL, 547 `NULLIF(DATE'2001-01-01',1e0)` date DEFAULT NULL, 548 `NULLIF(DATE'2001-01-01','2001-01-01')` date DEFAULT NULL, 549 `NULLIF(DATE'2001-01-01',TIME'00:00:00')` date DEFAULT NULL 550) ENGINE=MyISAM DEFAULT CHARSET=latin1 551DROP TABLE t1; 552CREATE TABLE t1 AS SELECT 553NULLIF(TIME'00:00:01',1), 554NULLIF(TIME'00:00:01',1.0), 555NULLIF(TIME'00:00:01',1e0), 556NULLIF(TIME'00:00:01','00:00:00'), 557NULLIF(TIME'00:00:01',DATE'2001-01-01'); 558SHOW CREATE TABLE t1; 559Table Create Table 560t1 CREATE TABLE `t1` ( 561 `NULLIF(TIME'00:00:01',1)` time DEFAULT NULL, 562 `NULLIF(TIME'00:00:01',1.0)` time DEFAULT NULL, 563 `NULLIF(TIME'00:00:01',1e0)` time DEFAULT NULL, 564 `NULLIF(TIME'00:00:01','00:00:00')` time DEFAULT NULL, 565 `NULLIF(TIME'00:00:01',DATE'2001-01-01')` time DEFAULT NULL 566) ENGINE=MyISAM DEFAULT CHARSET=latin1 567DROP TABLE t1; 568CREATE TABLE t1 569( 570c_tinyint TINYINT, 571c_smallint SMALLINT, 572c_int INT, 573c_bigint BIGINT, 574c_float FLOAT, 575c_double DOUBLE, 576c_decimal103 DECIMAL(10,3), 577c_varchar10 VARCHAR(10), 578c_tinytext TINYTEXT, 579c_text TEXT, 580c_mediumtext MEDIUMTEXT, 581c_longtext LONGTEXT, 582c_tinyblob TINYBLOB, 583c_blob BLOB, 584c_mediumblob MEDIUMBLOB, 585c_longblob LONGBLOB, 586c_enum ENUM('one','two','tree'), 587c_datetime3 DATETIME(3), 588c_timestamp3 TIMESTAMP(3), 589c_date DATE, 590c_time TIME 591); 592# 593# Checking that the return type depends only on args[0], even when compared to a super type 594# 595CREATE TABLE t2 AS SELECT 596NULLIF(c_tinyint, 1), 597NULLIF(c_tinyint, c_smallint), 598NULLIF(c_tinyint, c_tinyint), 599NULLIF(c_tinyint, c_int), 600NULLIF(c_tinyint, c_bigint), 601NULLIF(c_tinyint, c_float), 602NULLIF(c_tinyint, c_double), 603NULLIF(c_tinyint, c_decimal103), 604NULLIF(c_tinyint, c_varchar10), 605NULLIF(c_tinyint, c_text), 606NULLIF(c_tinyint, c_blob), 607NULLIF(c_tinyint, c_enum), 608NULLIF(c_tinyint, c_datetime3), 609NULLIF(c_tinyint, c_timestamp3), 610NULLIF(c_tinyint, c_date), 611NULLIF(c_tinyint, c_time) 612FROM t1; 613SHOW CREATE TABLE t2; 614Table Create Table 615t2 CREATE TABLE `t2` ( 616 `NULLIF(c_tinyint, 1)` tinyint(4) DEFAULT NULL, 617 `NULLIF(c_tinyint, c_smallint)` tinyint(4) DEFAULT NULL, 618 `NULLIF(c_tinyint, c_tinyint)` tinyint(4) DEFAULT NULL, 619 `NULLIF(c_tinyint, c_int)` tinyint(4) DEFAULT NULL, 620 `NULLIF(c_tinyint, c_bigint)` tinyint(4) DEFAULT NULL, 621 `NULLIF(c_tinyint, c_float)` tinyint(4) DEFAULT NULL, 622 `NULLIF(c_tinyint, c_double)` tinyint(4) DEFAULT NULL, 623 `NULLIF(c_tinyint, c_decimal103)` tinyint(4) DEFAULT NULL, 624 `NULLIF(c_tinyint, c_varchar10)` tinyint(4) DEFAULT NULL, 625 `NULLIF(c_tinyint, c_text)` tinyint(4) DEFAULT NULL, 626 `NULLIF(c_tinyint, c_blob)` tinyint(4) DEFAULT NULL, 627 `NULLIF(c_tinyint, c_enum)` tinyint(4) DEFAULT NULL, 628 `NULLIF(c_tinyint, c_datetime3)` tinyint(4) DEFAULT NULL, 629 `NULLIF(c_tinyint, c_timestamp3)` tinyint(4) DEFAULT NULL, 630 `NULLIF(c_tinyint, c_date)` tinyint(4) DEFAULT NULL, 631 `NULLIF(c_tinyint, c_time)` tinyint(4) DEFAULT NULL 632) ENGINE=MyISAM DEFAULT CHARSET=latin1 633DROP TABLE t2; 634CREATE TABLE t2 AS SELECT 635NULLIF(c_smallint, 1), 636NULLIF(c_smallint, c_smallint), 637NULLIF(c_smallint, c_tinyint), 638NULLIF(c_smallint, c_int), 639NULLIF(c_smallint, c_bigint), 640NULLIF(c_smallint, c_float), 641NULLIF(c_smallint, c_double), 642NULLIF(c_smallint, c_decimal103), 643NULLIF(c_smallint, c_varchar10), 644NULLIF(c_smallint, c_text), 645NULLIF(c_smallint, c_blob), 646NULLIF(c_smallint, c_enum), 647NULLIF(c_smallint, c_datetime3), 648NULLIF(c_smallint, c_timestamp3), 649NULLIF(c_smallint, c_date), 650NULLIF(c_smallint, c_time) 651FROM t1; 652SHOW CREATE TABLE t2; 653Table Create Table 654t2 CREATE TABLE `t2` ( 655 `NULLIF(c_smallint, 1)` smallint(6) DEFAULT NULL, 656 `NULLIF(c_smallint, c_smallint)` smallint(6) DEFAULT NULL, 657 `NULLIF(c_smallint, c_tinyint)` smallint(6) DEFAULT NULL, 658 `NULLIF(c_smallint, c_int)` smallint(6) DEFAULT NULL, 659 `NULLIF(c_smallint, c_bigint)` smallint(6) DEFAULT NULL, 660 `NULLIF(c_smallint, c_float)` smallint(6) DEFAULT NULL, 661 `NULLIF(c_smallint, c_double)` smallint(6) DEFAULT NULL, 662 `NULLIF(c_smallint, c_decimal103)` smallint(6) DEFAULT NULL, 663 `NULLIF(c_smallint, c_varchar10)` smallint(6) DEFAULT NULL, 664 `NULLIF(c_smallint, c_text)` smallint(6) DEFAULT NULL, 665 `NULLIF(c_smallint, c_blob)` smallint(6) DEFAULT NULL, 666 `NULLIF(c_smallint, c_enum)` smallint(6) DEFAULT NULL, 667 `NULLIF(c_smallint, c_datetime3)` smallint(6) DEFAULT NULL, 668 `NULLIF(c_smallint, c_timestamp3)` smallint(6) DEFAULT NULL, 669 `NULLIF(c_smallint, c_date)` smallint(6) DEFAULT NULL, 670 `NULLIF(c_smallint, c_time)` smallint(6) DEFAULT NULL 671) ENGINE=MyISAM DEFAULT CHARSET=latin1 672DROP TABLE t2; 673CREATE TABLE t2 AS SELECT 674NULLIF(c_int, 1), 675NULLIF(c_int, c_smallint), 676NULLIF(c_int, c_tinyint), 677NULLIF(c_int, c_int), 678NULLIF(c_int, c_bigint), 679NULLIF(c_int, c_float), 680NULLIF(c_int, c_double), 681NULLIF(c_int, c_decimal103), 682NULLIF(c_int, c_varchar10), 683NULLIF(c_int, c_text), 684NULLIF(c_int, c_blob), 685NULLIF(c_int, c_enum), 686NULLIF(c_int, c_datetime3), 687NULLIF(c_int, c_timestamp3), 688NULLIF(c_int, c_date), 689NULLIF(c_int, c_time) 690FROM t1; 691SHOW CREATE TABLE t2; 692Table Create Table 693t2 CREATE TABLE `t2` ( 694 `NULLIF(c_int, 1)` int(11) DEFAULT NULL, 695 `NULLIF(c_int, c_smallint)` int(11) DEFAULT NULL, 696 `NULLIF(c_int, c_tinyint)` int(11) DEFAULT NULL, 697 `NULLIF(c_int, c_int)` int(11) DEFAULT NULL, 698 `NULLIF(c_int, c_bigint)` int(11) DEFAULT NULL, 699 `NULLIF(c_int, c_float)` int(11) DEFAULT NULL, 700 `NULLIF(c_int, c_double)` int(11) DEFAULT NULL, 701 `NULLIF(c_int, c_decimal103)` int(11) DEFAULT NULL, 702 `NULLIF(c_int, c_varchar10)` int(11) DEFAULT NULL, 703 `NULLIF(c_int, c_text)` int(11) DEFAULT NULL, 704 `NULLIF(c_int, c_blob)` int(11) DEFAULT NULL, 705 `NULLIF(c_int, c_enum)` int(11) DEFAULT NULL, 706 `NULLIF(c_int, c_datetime3)` int(11) DEFAULT NULL, 707 `NULLIF(c_int, c_timestamp3)` int(11) DEFAULT NULL, 708 `NULLIF(c_int, c_date)` int(11) DEFAULT NULL, 709 `NULLIF(c_int, c_time)` int(11) DEFAULT NULL 710) ENGINE=MyISAM DEFAULT CHARSET=latin1 711DROP TABLE t2; 712CREATE TABLE t2 AS SELECT 713NULLIF(c_bigint, 1), 714NULLIF(c_bigint, c_smallint), 715NULLIF(c_bigint, c_tinyint), 716NULLIF(c_bigint, c_int), 717NULLIF(c_bigint, c_bigint), 718NULLIF(c_bigint, c_float), 719NULLIF(c_bigint, c_double), 720NULLIF(c_bigint, c_decimal103), 721NULLIF(c_bigint, c_varchar10), 722NULLIF(c_bigint, c_text), 723NULLIF(c_bigint, c_blob), 724NULLIF(c_bigint, c_enum), 725NULLIF(c_bigint, c_datetime3), 726NULLIF(c_bigint, c_timestamp3), 727NULLIF(c_bigint, c_date), 728NULLIF(c_bigint, c_time) 729FROM t1; 730SHOW CREATE TABLE t2; 731Table Create Table 732t2 CREATE TABLE `t2` ( 733 `NULLIF(c_bigint, 1)` bigint(20) DEFAULT NULL, 734 `NULLIF(c_bigint, c_smallint)` bigint(20) DEFAULT NULL, 735 `NULLIF(c_bigint, c_tinyint)` bigint(20) DEFAULT NULL, 736 `NULLIF(c_bigint, c_int)` bigint(20) DEFAULT NULL, 737 `NULLIF(c_bigint, c_bigint)` bigint(20) DEFAULT NULL, 738 `NULLIF(c_bigint, c_float)` bigint(20) DEFAULT NULL, 739 `NULLIF(c_bigint, c_double)` bigint(20) DEFAULT NULL, 740 `NULLIF(c_bigint, c_decimal103)` bigint(20) DEFAULT NULL, 741 `NULLIF(c_bigint, c_varchar10)` bigint(20) DEFAULT NULL, 742 `NULLIF(c_bigint, c_text)` bigint(20) DEFAULT NULL, 743 `NULLIF(c_bigint, c_blob)` bigint(20) DEFAULT NULL, 744 `NULLIF(c_bigint, c_enum)` bigint(20) DEFAULT NULL, 745 `NULLIF(c_bigint, c_datetime3)` bigint(20) DEFAULT NULL, 746 `NULLIF(c_bigint, c_timestamp3)` bigint(20) DEFAULT NULL, 747 `NULLIF(c_bigint, c_date)` bigint(20) DEFAULT NULL, 748 `NULLIF(c_bigint, c_time)` bigint(20) DEFAULT NULL 749) ENGINE=MyISAM DEFAULT CHARSET=latin1 750DROP TABLE t2; 751CREATE TABLE t2 AS SELECT 752NULLIF(c_float, 1), 753NULLIF(c_float, c_smallint), 754NULLIF(c_float, c_tinyint), 755NULLIF(c_float, c_int), 756NULLIF(c_float, c_bigint), 757NULLIF(c_float, c_float), 758NULLIF(c_float, c_double), 759NULLIF(c_float, c_decimal103), 760NULLIF(c_float, c_varchar10), 761NULLIF(c_float, c_text), 762NULLIF(c_float, c_blob), 763NULLIF(c_float, c_enum), 764NULLIF(c_float, c_datetime3), 765NULLIF(c_float, c_timestamp3), 766NULLIF(c_float, c_date), 767NULLIF(c_float, c_time) 768FROM t1; 769SHOW CREATE TABLE t2; 770Table Create Table 771t2 CREATE TABLE `t2` ( 772 `NULLIF(c_float, 1)` float DEFAULT NULL, 773 `NULLIF(c_float, c_smallint)` float DEFAULT NULL, 774 `NULLIF(c_float, c_tinyint)` float DEFAULT NULL, 775 `NULLIF(c_float, c_int)` float DEFAULT NULL, 776 `NULLIF(c_float, c_bigint)` float DEFAULT NULL, 777 `NULLIF(c_float, c_float)` float DEFAULT NULL, 778 `NULLIF(c_float, c_double)` float DEFAULT NULL, 779 `NULLIF(c_float, c_decimal103)` float DEFAULT NULL, 780 `NULLIF(c_float, c_varchar10)` float DEFAULT NULL, 781 `NULLIF(c_float, c_text)` float DEFAULT NULL, 782 `NULLIF(c_float, c_blob)` float DEFAULT NULL, 783 `NULLIF(c_float, c_enum)` float DEFAULT NULL, 784 `NULLIF(c_float, c_datetime3)` float DEFAULT NULL, 785 `NULLIF(c_float, c_timestamp3)` float DEFAULT NULL, 786 `NULLIF(c_float, c_date)` float DEFAULT NULL, 787 `NULLIF(c_float, c_time)` float DEFAULT NULL 788) ENGINE=MyISAM DEFAULT CHARSET=latin1 789DROP TABLE t2; 790CREATE TABLE t2 AS SELECT 791NULLIF(c_double, 1), 792NULLIF(c_double, c_smallint), 793NULLIF(c_double, c_tinyint), 794NULLIF(c_double, c_int), 795NULLIF(c_double, c_bigint), 796NULLIF(c_double, c_float), 797NULLIF(c_double, c_double), 798NULLIF(c_double, c_decimal103), 799NULLIF(c_double, c_varchar10), 800NULLIF(c_double, c_text), 801NULLIF(c_double, c_blob), 802NULLIF(c_double, c_enum), 803NULLIF(c_double, c_datetime3), 804NULLIF(c_double, c_timestamp3), 805NULLIF(c_double, c_date), 806NULLIF(c_double, c_time) 807FROM t1; 808SHOW CREATE TABLE t2; 809Table Create Table 810t2 CREATE TABLE `t2` ( 811 `NULLIF(c_double, 1)` double DEFAULT NULL, 812 `NULLIF(c_double, c_smallint)` double DEFAULT NULL, 813 `NULLIF(c_double, c_tinyint)` double DEFAULT NULL, 814 `NULLIF(c_double, c_int)` double DEFAULT NULL, 815 `NULLIF(c_double, c_bigint)` double DEFAULT NULL, 816 `NULLIF(c_double, c_float)` double DEFAULT NULL, 817 `NULLIF(c_double, c_double)` double DEFAULT NULL, 818 `NULLIF(c_double, c_decimal103)` double DEFAULT NULL, 819 `NULLIF(c_double, c_varchar10)` double DEFAULT NULL, 820 `NULLIF(c_double, c_text)` double DEFAULT NULL, 821 `NULLIF(c_double, c_blob)` double DEFAULT NULL, 822 `NULLIF(c_double, c_enum)` double DEFAULT NULL, 823 `NULLIF(c_double, c_datetime3)` double DEFAULT NULL, 824 `NULLIF(c_double, c_timestamp3)` double DEFAULT NULL, 825 `NULLIF(c_double, c_date)` double DEFAULT NULL, 826 `NULLIF(c_double, c_time)` double DEFAULT NULL 827) ENGINE=MyISAM DEFAULT CHARSET=latin1 828DROP TABLE t2; 829CREATE TABLE t2 AS SELECT 830NULLIF(c_decimal103, 1), 831NULLIF(c_decimal103, c_smallint), 832NULLIF(c_decimal103, c_tinyint), 833NULLIF(c_decimal103, c_int), 834NULLIF(c_decimal103, c_bigint), 835NULLIF(c_decimal103, c_float), 836NULLIF(c_decimal103, c_double), 837NULLIF(c_decimal103, c_decimal103), 838NULLIF(c_decimal103, c_varchar10), 839NULLIF(c_decimal103, c_text), 840NULLIF(c_decimal103, c_blob), 841NULLIF(c_decimal103, c_enum), 842NULLIF(c_decimal103, c_datetime3), 843NULLIF(c_decimal103, c_timestamp3), 844NULLIF(c_decimal103, c_date), 845NULLIF(c_decimal103, c_time) 846FROM t1; 847SHOW CREATE TABLE t2; 848Table Create Table 849t2 CREATE TABLE `t2` ( 850 `NULLIF(c_decimal103, 1)` decimal(10,3) DEFAULT NULL, 851 `NULLIF(c_decimal103, c_smallint)` decimal(10,3) DEFAULT NULL, 852 `NULLIF(c_decimal103, c_tinyint)` decimal(10,3) DEFAULT NULL, 853 `NULLIF(c_decimal103, c_int)` decimal(10,3) DEFAULT NULL, 854 `NULLIF(c_decimal103, c_bigint)` decimal(10,3) DEFAULT NULL, 855 `NULLIF(c_decimal103, c_float)` decimal(10,3) DEFAULT NULL, 856 `NULLIF(c_decimal103, c_double)` decimal(10,3) DEFAULT NULL, 857 `NULLIF(c_decimal103, c_decimal103)` decimal(10,3) DEFAULT NULL, 858 `NULLIF(c_decimal103, c_varchar10)` decimal(10,3) DEFAULT NULL, 859 `NULLIF(c_decimal103, c_text)` decimal(10,3) DEFAULT NULL, 860 `NULLIF(c_decimal103, c_blob)` decimal(10,3) DEFAULT NULL, 861 `NULLIF(c_decimal103, c_enum)` decimal(10,3) DEFAULT NULL, 862 `NULLIF(c_decimal103, c_datetime3)` decimal(10,3) DEFAULT NULL, 863 `NULLIF(c_decimal103, c_timestamp3)` decimal(10,3) DEFAULT NULL, 864 `NULLIF(c_decimal103, c_date)` decimal(10,3) DEFAULT NULL, 865 `NULLIF(c_decimal103, c_time)` decimal(10,3) DEFAULT NULL 866) ENGINE=MyISAM DEFAULT CHARSET=latin1 867DROP TABLE t2; 868CREATE TABLE t2 AS SELECT 869NULLIF(c_varchar10, 1), 870NULLIF(c_varchar10, c_smallint), 871NULLIF(c_varchar10, c_tinyint), 872NULLIF(c_varchar10, c_int), 873NULLIF(c_varchar10, c_bigint), 874NULLIF(c_varchar10, c_float), 875NULLIF(c_varchar10, c_double), 876NULLIF(c_varchar10, c_decimal103), 877NULLIF(c_varchar10, c_varchar10), 878NULLIF(c_varchar10, c_text), 879NULLIF(c_varchar10, c_blob), 880NULLIF(c_varchar10, c_enum), 881NULLIF(c_varchar10, c_datetime3), 882NULLIF(c_varchar10, c_timestamp3), 883NULLIF(c_varchar10, c_date), 884NULLIF(c_varchar10, c_time) 885FROM t1; 886SHOW CREATE TABLE t2; 887Table Create Table 888t2 CREATE TABLE `t2` ( 889 `NULLIF(c_varchar10, 1)` varchar(10) DEFAULT NULL, 890 `NULLIF(c_varchar10, c_smallint)` varchar(10) DEFAULT NULL, 891 `NULLIF(c_varchar10, c_tinyint)` varchar(10) DEFAULT NULL, 892 `NULLIF(c_varchar10, c_int)` varchar(10) DEFAULT NULL, 893 `NULLIF(c_varchar10, c_bigint)` varchar(10) DEFAULT NULL, 894 `NULLIF(c_varchar10, c_float)` varchar(10) DEFAULT NULL, 895 `NULLIF(c_varchar10, c_double)` varchar(10) DEFAULT NULL, 896 `NULLIF(c_varchar10, c_decimal103)` varchar(10) DEFAULT NULL, 897 `NULLIF(c_varchar10, c_varchar10)` varchar(10) DEFAULT NULL, 898 `NULLIF(c_varchar10, c_text)` varchar(10) DEFAULT NULL, 899 `NULLIF(c_varchar10, c_blob)` varchar(10) DEFAULT NULL, 900 `NULLIF(c_varchar10, c_enum)` varchar(10) DEFAULT NULL, 901 `NULLIF(c_varchar10, c_datetime3)` varchar(10) DEFAULT NULL, 902 `NULLIF(c_varchar10, c_timestamp3)` varchar(10) DEFAULT NULL, 903 `NULLIF(c_varchar10, c_date)` varchar(10) DEFAULT NULL, 904 `NULLIF(c_varchar10, c_time)` varchar(10) DEFAULT NULL 905) ENGINE=MyISAM DEFAULT CHARSET=latin1 906DROP TABLE t2; 907CREATE TABLE t2 AS SELECT 908NULLIF(c_tinytext, 1), 909NULLIF(c_tinytext, c_smallint), 910NULLIF(c_tinytext, c_tinyint), 911NULLIF(c_tinytext, c_int), 912NULLIF(c_tinytext, c_bigint), 913NULLIF(c_tinytext, c_float), 914NULLIF(c_tinytext, c_double), 915NULLIF(c_tinytext, c_decimal103), 916NULLIF(c_tinytext, c_varchar10), 917NULLIF(c_tinytext, c_text), 918NULLIF(c_tinytext, c_blob), 919NULLIF(c_tinytext, c_enum), 920NULLIF(c_tinytext, c_datetime3), 921NULLIF(c_tinytext, c_timestamp3), 922NULLIF(c_tinytext, c_date), 923NULLIF(c_tinytext, c_time) 924FROM t1; 925SHOW CREATE TABLE t2; 926Table Create Table 927t2 CREATE TABLE `t2` ( 928 `NULLIF(c_tinytext, 1)` tinytext DEFAULT NULL, 929 `NULLIF(c_tinytext, c_smallint)` tinytext DEFAULT NULL, 930 `NULLIF(c_tinytext, c_tinyint)` tinytext DEFAULT NULL, 931 `NULLIF(c_tinytext, c_int)` tinytext DEFAULT NULL, 932 `NULLIF(c_tinytext, c_bigint)` tinytext DEFAULT NULL, 933 `NULLIF(c_tinytext, c_float)` tinytext DEFAULT NULL, 934 `NULLIF(c_tinytext, c_double)` tinytext DEFAULT NULL, 935 `NULLIF(c_tinytext, c_decimal103)` tinytext DEFAULT NULL, 936 `NULLIF(c_tinytext, c_varchar10)` tinytext DEFAULT NULL, 937 `NULLIF(c_tinytext, c_text)` tinytext DEFAULT NULL, 938 `NULLIF(c_tinytext, c_blob)` tinytext DEFAULT NULL, 939 `NULLIF(c_tinytext, c_enum)` tinytext DEFAULT NULL, 940 `NULLIF(c_tinytext, c_datetime3)` tinytext DEFAULT NULL, 941 `NULLIF(c_tinytext, c_timestamp3)` tinytext DEFAULT NULL, 942 `NULLIF(c_tinytext, c_date)` tinytext DEFAULT NULL, 943 `NULLIF(c_tinytext, c_time)` tinytext DEFAULT NULL 944) ENGINE=MyISAM DEFAULT CHARSET=latin1 945DROP TABLE t2; 946CREATE TABLE t2 AS SELECT 947NULLIF(c_text, 1), 948NULLIF(c_text, c_smallint), 949NULLIF(c_text, c_tinyint), 950NULLIF(c_text, c_int), 951NULLIF(c_text, c_bigint), 952NULLIF(c_text, c_float), 953NULLIF(c_text, c_double), 954NULLIF(c_text, c_decimal103), 955NULLIF(c_text, c_varchar10), 956NULLIF(c_text, c_text), 957NULLIF(c_text, c_blob), 958NULLIF(c_text, c_enum), 959NULLIF(c_text, c_datetime3), 960NULLIF(c_text, c_timestamp3), 961NULLIF(c_text, c_date), 962NULLIF(c_text, c_time) 963FROM t1; 964SHOW CREATE TABLE t2; 965Table Create Table 966t2 CREATE TABLE `t2` ( 967 `NULLIF(c_text, 1)` text DEFAULT NULL, 968 `NULLIF(c_text, c_smallint)` text DEFAULT NULL, 969 `NULLIF(c_text, c_tinyint)` text DEFAULT NULL, 970 `NULLIF(c_text, c_int)` text DEFAULT NULL, 971 `NULLIF(c_text, c_bigint)` text DEFAULT NULL, 972 `NULLIF(c_text, c_float)` text DEFAULT NULL, 973 `NULLIF(c_text, c_double)` text DEFAULT NULL, 974 `NULLIF(c_text, c_decimal103)` text DEFAULT NULL, 975 `NULLIF(c_text, c_varchar10)` text DEFAULT NULL, 976 `NULLIF(c_text, c_text)` text DEFAULT NULL, 977 `NULLIF(c_text, c_blob)` text DEFAULT NULL, 978 `NULLIF(c_text, c_enum)` text DEFAULT NULL, 979 `NULLIF(c_text, c_datetime3)` text DEFAULT NULL, 980 `NULLIF(c_text, c_timestamp3)` text DEFAULT NULL, 981 `NULLIF(c_text, c_date)` text DEFAULT NULL, 982 `NULLIF(c_text, c_time)` text DEFAULT NULL 983) ENGINE=MyISAM DEFAULT CHARSET=latin1 984DROP TABLE t2; 985CREATE TABLE t2 AS SELECT 986NULLIF(c_mediumtext, 1), 987NULLIF(c_mediumtext, c_smallint), 988NULLIF(c_mediumtext, c_tinyint), 989NULLIF(c_mediumtext, c_int), 990NULLIF(c_mediumtext, c_bigint), 991NULLIF(c_mediumtext, c_float), 992NULLIF(c_mediumtext, c_double), 993NULLIF(c_mediumtext, c_decimal103), 994NULLIF(c_mediumtext, c_varchar10), 995NULLIF(c_mediumtext, c_text), 996NULLIF(c_mediumtext, c_blob), 997NULLIF(c_mediumtext, c_enum), 998NULLIF(c_mediumtext, c_datetime3), 999NULLIF(c_mediumtext, c_timestamp3), 1000NULLIF(c_mediumtext, c_date), 1001NULLIF(c_mediumtext, c_time) 1002FROM t1; 1003SHOW CREATE TABLE t2; 1004Table Create Table 1005t2 CREATE TABLE `t2` ( 1006 `NULLIF(c_mediumtext, 1)` mediumtext DEFAULT NULL, 1007 `NULLIF(c_mediumtext, c_smallint)` mediumtext DEFAULT NULL, 1008 `NULLIF(c_mediumtext, c_tinyint)` mediumtext DEFAULT NULL, 1009 `NULLIF(c_mediumtext, c_int)` mediumtext DEFAULT NULL, 1010 `NULLIF(c_mediumtext, c_bigint)` mediumtext DEFAULT NULL, 1011 `NULLIF(c_mediumtext, c_float)` mediumtext DEFAULT NULL, 1012 `NULLIF(c_mediumtext, c_double)` mediumtext DEFAULT NULL, 1013 `NULLIF(c_mediumtext, c_decimal103)` mediumtext DEFAULT NULL, 1014 `NULLIF(c_mediumtext, c_varchar10)` mediumtext DEFAULT NULL, 1015 `NULLIF(c_mediumtext, c_text)` mediumtext DEFAULT NULL, 1016 `NULLIF(c_mediumtext, c_blob)` mediumtext DEFAULT NULL, 1017 `NULLIF(c_mediumtext, c_enum)` mediumtext DEFAULT NULL, 1018 `NULLIF(c_mediumtext, c_datetime3)` mediumtext DEFAULT NULL, 1019 `NULLIF(c_mediumtext, c_timestamp3)` mediumtext DEFAULT NULL, 1020 `NULLIF(c_mediumtext, c_date)` mediumtext DEFAULT NULL, 1021 `NULLIF(c_mediumtext, c_time)` mediumtext DEFAULT NULL 1022) ENGINE=MyISAM DEFAULT CHARSET=latin1 1023DROP TABLE t2; 1024CREATE TABLE t2 AS SELECT 1025NULLIF(c_longtext, 1), 1026NULLIF(c_longtext, c_smallint), 1027NULLIF(c_longtext, c_tinyint), 1028NULLIF(c_longtext, c_int), 1029NULLIF(c_longtext, c_bigint), 1030NULLIF(c_longtext, c_float), 1031NULLIF(c_longtext, c_double), 1032NULLIF(c_longtext, c_decimal103), 1033NULLIF(c_longtext, c_varchar10), 1034NULLIF(c_longtext, c_text), 1035NULLIF(c_longtext, c_blob), 1036NULLIF(c_longtext, c_enum), 1037NULLIF(c_longtext, c_datetime3), 1038NULLIF(c_longtext, c_timestamp3), 1039NULLIF(c_longtext, c_date), 1040NULLIF(c_longtext, c_time) 1041FROM t1; 1042SHOW CREATE TABLE t2; 1043Table Create Table 1044t2 CREATE TABLE `t2` ( 1045 `NULLIF(c_longtext, 1)` longtext DEFAULT NULL, 1046 `NULLIF(c_longtext, c_smallint)` longtext DEFAULT NULL, 1047 `NULLIF(c_longtext, c_tinyint)` longtext DEFAULT NULL, 1048 `NULLIF(c_longtext, c_int)` longtext DEFAULT NULL, 1049 `NULLIF(c_longtext, c_bigint)` longtext DEFAULT NULL, 1050 `NULLIF(c_longtext, c_float)` longtext DEFAULT NULL, 1051 `NULLIF(c_longtext, c_double)` longtext DEFAULT NULL, 1052 `NULLIF(c_longtext, c_decimal103)` longtext DEFAULT NULL, 1053 `NULLIF(c_longtext, c_varchar10)` longtext DEFAULT NULL, 1054 `NULLIF(c_longtext, c_text)` longtext DEFAULT NULL, 1055 `NULLIF(c_longtext, c_blob)` longtext DEFAULT NULL, 1056 `NULLIF(c_longtext, c_enum)` longtext DEFAULT NULL, 1057 `NULLIF(c_longtext, c_datetime3)` longtext DEFAULT NULL, 1058 `NULLIF(c_longtext, c_timestamp3)` longtext DEFAULT NULL, 1059 `NULLIF(c_longtext, c_date)` longtext DEFAULT NULL, 1060 `NULLIF(c_longtext, c_time)` longtext DEFAULT NULL 1061) ENGINE=MyISAM DEFAULT CHARSET=latin1 1062DROP TABLE t2; 1063CREATE TABLE t2 AS SELECT 1064NULLIF(c_tinyblob, 1), 1065NULLIF(c_tinyblob, c_smallint), 1066NULLIF(c_tinyblob, c_tinyint), 1067NULLIF(c_tinyblob, c_int), 1068NULLIF(c_tinyblob, c_bigint), 1069NULLIF(c_tinyblob, c_float), 1070NULLIF(c_tinyblob, c_double), 1071NULLIF(c_tinyblob, c_decimal103), 1072NULLIF(c_tinyblob, c_varchar10), 1073NULLIF(c_tinyblob, c_text), 1074NULLIF(c_tinyblob, c_blob), 1075NULLIF(c_tinyblob, c_enum), 1076NULLIF(c_tinyblob, c_datetime3), 1077NULLIF(c_tinyblob, c_timestamp3), 1078NULLIF(c_tinyblob, c_date), 1079NULLIF(c_tinyblob, c_time) 1080FROM t1; 1081SHOW CREATE TABLE t2; 1082Table Create Table 1083t2 CREATE TABLE `t2` ( 1084 `NULLIF(c_tinyblob, 1)` tinyblob DEFAULT NULL, 1085 `NULLIF(c_tinyblob, c_smallint)` tinyblob DEFAULT NULL, 1086 `NULLIF(c_tinyblob, c_tinyint)` tinyblob DEFAULT NULL, 1087 `NULLIF(c_tinyblob, c_int)` tinyblob DEFAULT NULL, 1088 `NULLIF(c_tinyblob, c_bigint)` tinyblob DEFAULT NULL, 1089 `NULLIF(c_tinyblob, c_float)` tinyblob DEFAULT NULL, 1090 `NULLIF(c_tinyblob, c_double)` tinyblob DEFAULT NULL, 1091 `NULLIF(c_tinyblob, c_decimal103)` tinyblob DEFAULT NULL, 1092 `NULLIF(c_tinyblob, c_varchar10)` tinyblob DEFAULT NULL, 1093 `NULLIF(c_tinyblob, c_text)` tinyblob DEFAULT NULL, 1094 `NULLIF(c_tinyblob, c_blob)` tinyblob DEFAULT NULL, 1095 `NULLIF(c_tinyblob, c_enum)` tinyblob DEFAULT NULL, 1096 `NULLIF(c_tinyblob, c_datetime3)` tinyblob DEFAULT NULL, 1097 `NULLIF(c_tinyblob, c_timestamp3)` tinyblob DEFAULT NULL, 1098 `NULLIF(c_tinyblob, c_date)` tinyblob DEFAULT NULL, 1099 `NULLIF(c_tinyblob, c_time)` tinyblob DEFAULT NULL 1100) ENGINE=MyISAM DEFAULT CHARSET=latin1 1101DROP TABLE t2; 1102CREATE TABLE t2 AS SELECT 1103NULLIF(c_blob, 1), 1104NULLIF(c_blob, c_smallint), 1105NULLIF(c_blob, c_tinyint), 1106NULLIF(c_blob, c_int), 1107NULLIF(c_blob, c_bigint), 1108NULLIF(c_blob, c_float), 1109NULLIF(c_blob, c_double), 1110NULLIF(c_blob, c_decimal103), 1111NULLIF(c_blob, c_varchar10), 1112NULLIF(c_blob, c_text), 1113NULLIF(c_blob, c_blob), 1114NULLIF(c_blob, c_enum), 1115NULLIF(c_blob, c_datetime3), 1116NULLIF(c_blob, c_timestamp3), 1117NULLIF(c_blob, c_date), 1118NULLIF(c_blob, c_time) 1119FROM t1; 1120SHOW CREATE TABLE t2; 1121Table Create Table 1122t2 CREATE TABLE `t2` ( 1123 `NULLIF(c_blob, 1)` blob DEFAULT NULL, 1124 `NULLIF(c_blob, c_smallint)` blob DEFAULT NULL, 1125 `NULLIF(c_blob, c_tinyint)` blob DEFAULT NULL, 1126 `NULLIF(c_blob, c_int)` blob DEFAULT NULL, 1127 `NULLIF(c_blob, c_bigint)` blob DEFAULT NULL, 1128 `NULLIF(c_blob, c_float)` blob DEFAULT NULL, 1129 `NULLIF(c_blob, c_double)` blob DEFAULT NULL, 1130 `NULLIF(c_blob, c_decimal103)` blob DEFAULT NULL, 1131 `NULLIF(c_blob, c_varchar10)` blob DEFAULT NULL, 1132 `NULLIF(c_blob, c_text)` blob DEFAULT NULL, 1133 `NULLIF(c_blob, c_blob)` blob DEFAULT NULL, 1134 `NULLIF(c_blob, c_enum)` blob DEFAULT NULL, 1135 `NULLIF(c_blob, c_datetime3)` blob DEFAULT NULL, 1136 `NULLIF(c_blob, c_timestamp3)` blob DEFAULT NULL, 1137 `NULLIF(c_blob, c_date)` blob DEFAULT NULL, 1138 `NULLIF(c_blob, c_time)` blob DEFAULT NULL 1139) ENGINE=MyISAM DEFAULT CHARSET=latin1 1140DROP TABLE t2; 1141CREATE TABLE t2 AS SELECT 1142NULLIF(c_mediumblob, 1), 1143NULLIF(c_mediumblob, c_smallint), 1144NULLIF(c_mediumblob, c_tinyint), 1145NULLIF(c_mediumblob, c_int), 1146NULLIF(c_mediumblob, c_bigint), 1147NULLIF(c_mediumblob, c_float), 1148NULLIF(c_mediumblob, c_double), 1149NULLIF(c_mediumblob, c_decimal103), 1150NULLIF(c_mediumblob, c_varchar10), 1151NULLIF(c_mediumblob, c_text), 1152NULLIF(c_mediumblob, c_blob), 1153NULLIF(c_mediumblob, c_enum), 1154NULLIF(c_mediumblob, c_datetime3), 1155NULLIF(c_mediumblob, c_timestamp3), 1156NULLIF(c_mediumblob, c_date), 1157NULLIF(c_mediumblob, c_time) 1158FROM t1; 1159SHOW CREATE TABLE t2; 1160Table Create Table 1161t2 CREATE TABLE `t2` ( 1162 `NULLIF(c_mediumblob, 1)` mediumblob DEFAULT NULL, 1163 `NULLIF(c_mediumblob, c_smallint)` mediumblob DEFAULT NULL, 1164 `NULLIF(c_mediumblob, c_tinyint)` mediumblob DEFAULT NULL, 1165 `NULLIF(c_mediumblob, c_int)` mediumblob DEFAULT NULL, 1166 `NULLIF(c_mediumblob, c_bigint)` mediumblob DEFAULT NULL, 1167 `NULLIF(c_mediumblob, c_float)` mediumblob DEFAULT NULL, 1168 `NULLIF(c_mediumblob, c_double)` mediumblob DEFAULT NULL, 1169 `NULLIF(c_mediumblob, c_decimal103)` mediumblob DEFAULT NULL, 1170 `NULLIF(c_mediumblob, c_varchar10)` mediumblob DEFAULT NULL, 1171 `NULLIF(c_mediumblob, c_text)` mediumblob DEFAULT NULL, 1172 `NULLIF(c_mediumblob, c_blob)` mediumblob DEFAULT NULL, 1173 `NULLIF(c_mediumblob, c_enum)` mediumblob DEFAULT NULL, 1174 `NULLIF(c_mediumblob, c_datetime3)` mediumblob DEFAULT NULL, 1175 `NULLIF(c_mediumblob, c_timestamp3)` mediumblob DEFAULT NULL, 1176 `NULLIF(c_mediumblob, c_date)` mediumblob DEFAULT NULL, 1177 `NULLIF(c_mediumblob, c_time)` mediumblob DEFAULT NULL 1178) ENGINE=MyISAM DEFAULT CHARSET=latin1 1179DROP TABLE t2; 1180CREATE TABLE t2 AS SELECT 1181NULLIF(c_longblob, 1), 1182NULLIF(c_longblob, c_smallint), 1183NULLIF(c_longblob, c_tinyint), 1184NULLIF(c_longblob, c_int), 1185NULLIF(c_longblob, c_bigint), 1186NULLIF(c_longblob, c_float), 1187NULLIF(c_longblob, c_double), 1188NULLIF(c_longblob, c_decimal103), 1189NULLIF(c_longblob, c_varchar10), 1190NULLIF(c_longblob, c_text), 1191NULLIF(c_longblob, c_blob), 1192NULLIF(c_longblob, c_enum), 1193NULLIF(c_longblob, c_datetime3), 1194NULLIF(c_longblob, c_timestamp3), 1195NULLIF(c_longblob, c_date), 1196NULLIF(c_longblob, c_time) 1197FROM t1; 1198SHOW CREATE TABLE t2; 1199Table Create Table 1200t2 CREATE TABLE `t2` ( 1201 `NULLIF(c_longblob, 1)` longblob DEFAULT NULL, 1202 `NULLIF(c_longblob, c_smallint)` longblob DEFAULT NULL, 1203 `NULLIF(c_longblob, c_tinyint)` longblob DEFAULT NULL, 1204 `NULLIF(c_longblob, c_int)` longblob DEFAULT NULL, 1205 `NULLIF(c_longblob, c_bigint)` longblob DEFAULT NULL, 1206 `NULLIF(c_longblob, c_float)` longblob DEFAULT NULL, 1207 `NULLIF(c_longblob, c_double)` longblob DEFAULT NULL, 1208 `NULLIF(c_longblob, c_decimal103)` longblob DEFAULT NULL, 1209 `NULLIF(c_longblob, c_varchar10)` longblob DEFAULT NULL, 1210 `NULLIF(c_longblob, c_text)` longblob DEFAULT NULL, 1211 `NULLIF(c_longblob, c_blob)` longblob DEFAULT NULL, 1212 `NULLIF(c_longblob, c_enum)` longblob DEFAULT NULL, 1213 `NULLIF(c_longblob, c_datetime3)` longblob DEFAULT NULL, 1214 `NULLIF(c_longblob, c_timestamp3)` longblob DEFAULT NULL, 1215 `NULLIF(c_longblob, c_date)` longblob DEFAULT NULL, 1216 `NULLIF(c_longblob, c_time)` longblob DEFAULT NULL 1217) ENGINE=MyISAM DEFAULT CHARSET=latin1 1218DROP TABLE t2; 1219CREATE TABLE t2 AS SELECT 1220NULLIF(c_enum, 1), 1221NULLIF(c_enum, c_smallint), 1222NULLIF(c_enum, c_tinyint), 1223NULLIF(c_enum, c_int), 1224NULLIF(c_enum, c_bigint), 1225NULLIF(c_enum, c_float), 1226NULLIF(c_enum, c_double), 1227NULLIF(c_enum, c_decimal103), 1228NULLIF(c_enum, c_varchar10), 1229NULLIF(c_enum, c_text), 1230NULLIF(c_enum, c_blob), 1231NULLIF(c_enum, c_enum), 1232NULLIF(c_enum, c_datetime3), 1233NULLIF(c_enum, c_timestamp3), 1234NULLIF(c_enum, c_date), 1235NULLIF(c_enum, c_time) 1236FROM t1; 1237SHOW CREATE TABLE t2; 1238Table Create Table 1239t2 CREATE TABLE `t2` ( 1240 `NULLIF(c_enum, 1)` varchar(4) DEFAULT NULL, 1241 `NULLIF(c_enum, c_smallint)` varchar(4) DEFAULT NULL, 1242 `NULLIF(c_enum, c_tinyint)` varchar(4) DEFAULT NULL, 1243 `NULLIF(c_enum, c_int)` varchar(4) DEFAULT NULL, 1244 `NULLIF(c_enum, c_bigint)` varchar(4) DEFAULT NULL, 1245 `NULLIF(c_enum, c_float)` varchar(4) DEFAULT NULL, 1246 `NULLIF(c_enum, c_double)` varchar(4) DEFAULT NULL, 1247 `NULLIF(c_enum, c_decimal103)` varchar(4) DEFAULT NULL, 1248 `NULLIF(c_enum, c_varchar10)` varchar(4) DEFAULT NULL, 1249 `NULLIF(c_enum, c_text)` varchar(4) DEFAULT NULL, 1250 `NULLIF(c_enum, c_blob)` varchar(4) DEFAULT NULL, 1251 `NULLIF(c_enum, c_enum)` varchar(4) DEFAULT NULL, 1252 `NULLIF(c_enum, c_datetime3)` varchar(4) DEFAULT NULL, 1253 `NULLIF(c_enum, c_timestamp3)` varchar(4) DEFAULT NULL, 1254 `NULLIF(c_enum, c_date)` varchar(4) DEFAULT NULL, 1255 `NULLIF(c_enum, c_time)` varchar(4) DEFAULT NULL 1256) ENGINE=MyISAM DEFAULT CHARSET=latin1 1257DROP TABLE t2; 1258CREATE TABLE t2 AS SELECT 1259NULLIF(c_datetime3, 1), 1260NULLIF(c_datetime3, c_smallint), 1261NULLIF(c_datetime3, c_tinyint), 1262NULLIF(c_datetime3, c_int), 1263NULLIF(c_datetime3, c_bigint), 1264NULLIF(c_datetime3, c_float), 1265NULLIF(c_datetime3, c_double), 1266NULLIF(c_datetime3, c_decimal103), 1267NULLIF(c_datetime3, c_varchar10), 1268NULLIF(c_datetime3, c_text), 1269NULLIF(c_datetime3, c_blob), 1270NULLIF(c_datetime3, c_enum), 1271NULLIF(c_datetime3, c_datetime3), 1272NULLIF(c_datetime3, c_timestamp3), 1273NULLIF(c_datetime3, c_date), 1274NULLIF(c_datetime3, c_time) 1275FROM t1; 1276SHOW CREATE TABLE t2; 1277Table Create Table 1278t2 CREATE TABLE `t2` ( 1279 `NULLIF(c_datetime3, 1)` datetime(3) DEFAULT NULL, 1280 `NULLIF(c_datetime3, c_smallint)` datetime(3) DEFAULT NULL, 1281 `NULLIF(c_datetime3, c_tinyint)` datetime(3) DEFAULT NULL, 1282 `NULLIF(c_datetime3, c_int)` datetime(3) DEFAULT NULL, 1283 `NULLIF(c_datetime3, c_bigint)` datetime(3) DEFAULT NULL, 1284 `NULLIF(c_datetime3, c_float)` datetime(3) DEFAULT NULL, 1285 `NULLIF(c_datetime3, c_double)` datetime(3) DEFAULT NULL, 1286 `NULLIF(c_datetime3, c_decimal103)` datetime(3) DEFAULT NULL, 1287 `NULLIF(c_datetime3, c_varchar10)` datetime(3) DEFAULT NULL, 1288 `NULLIF(c_datetime3, c_text)` datetime(3) DEFAULT NULL, 1289 `NULLIF(c_datetime3, c_blob)` datetime(3) DEFAULT NULL, 1290 `NULLIF(c_datetime3, c_enum)` datetime(3) DEFAULT NULL, 1291 `NULLIF(c_datetime3, c_datetime3)` datetime(3) DEFAULT NULL, 1292 `NULLIF(c_datetime3, c_timestamp3)` datetime(3) DEFAULT NULL, 1293 `NULLIF(c_datetime3, c_date)` datetime(3) DEFAULT NULL, 1294 `NULLIF(c_datetime3, c_time)` datetime(3) DEFAULT NULL 1295) ENGINE=MyISAM DEFAULT CHARSET=latin1 1296DROP TABLE t2; 1297CREATE TABLE t2 AS SELECT 1298NULLIF(c_timestamp3, 1), 1299NULLIF(c_timestamp3, c_smallint), 1300NULLIF(c_timestamp3, c_tinyint), 1301NULLIF(c_timestamp3, c_int), 1302NULLIF(c_timestamp3, c_bigint), 1303NULLIF(c_timestamp3, c_float), 1304NULLIF(c_timestamp3, c_double), 1305NULLIF(c_timestamp3, c_decimal103), 1306NULLIF(c_timestamp3, c_varchar10), 1307NULLIF(c_timestamp3, c_text), 1308NULLIF(c_timestamp3, c_blob), 1309NULLIF(c_timestamp3, c_enum), 1310NULLIF(c_timestamp3, c_datetime3), 1311NULLIF(c_timestamp3, c_timestamp3), 1312NULLIF(c_timestamp3, c_date), 1313NULLIF(c_timestamp3, c_time) 1314FROM t1; 1315SHOW CREATE TABLE t2; 1316Table Create Table 1317t2 CREATE TABLE `t2` ( 1318 `NULLIF(c_timestamp3, 1)` timestamp(3) NULL DEFAULT NULL, 1319 `NULLIF(c_timestamp3, c_smallint)` timestamp(3) NULL DEFAULT NULL, 1320 `NULLIF(c_timestamp3, c_tinyint)` timestamp(3) NULL DEFAULT NULL, 1321 `NULLIF(c_timestamp3, c_int)` timestamp(3) NULL DEFAULT NULL, 1322 `NULLIF(c_timestamp3, c_bigint)` timestamp(3) NULL DEFAULT NULL, 1323 `NULLIF(c_timestamp3, c_float)` timestamp(3) NULL DEFAULT NULL, 1324 `NULLIF(c_timestamp3, c_double)` timestamp(3) NULL DEFAULT NULL, 1325 `NULLIF(c_timestamp3, c_decimal103)` timestamp(3) NULL DEFAULT NULL, 1326 `NULLIF(c_timestamp3, c_varchar10)` timestamp(3) NULL DEFAULT NULL, 1327 `NULLIF(c_timestamp3, c_text)` timestamp(3) NULL DEFAULT NULL, 1328 `NULLIF(c_timestamp3, c_blob)` timestamp(3) NULL DEFAULT NULL, 1329 `NULLIF(c_timestamp3, c_enum)` timestamp(3) NULL DEFAULT NULL, 1330 `NULLIF(c_timestamp3, c_datetime3)` timestamp(3) NULL DEFAULT NULL, 1331 `NULLIF(c_timestamp3, c_timestamp3)` timestamp(3) NULL DEFAULT NULL, 1332 `NULLIF(c_timestamp3, c_date)` timestamp(3) NULL DEFAULT NULL, 1333 `NULLIF(c_timestamp3, c_time)` timestamp(3) NULL DEFAULT NULL 1334) ENGINE=MyISAM DEFAULT CHARSET=latin1 1335DROP TABLE t2; 1336CREATE TABLE t2 AS SELECT 1337NULLIF(c_date, 1), 1338NULLIF(c_date, c_smallint), 1339NULLIF(c_date, c_tinyint), 1340NULLIF(c_date, c_int), 1341NULLIF(c_date, c_bigint), 1342NULLIF(c_date, c_float), 1343NULLIF(c_date, c_double), 1344NULLIF(c_date, c_decimal103), 1345NULLIF(c_date, c_varchar10), 1346NULLIF(c_date, c_text), 1347NULLIF(c_date, c_blob), 1348NULLIF(c_date, c_enum), 1349NULLIF(c_date, c_datetime3), 1350NULLIF(c_date, c_timestamp3), 1351NULLIF(c_date, c_date), 1352NULLIF(c_date, c_time) 1353FROM t1; 1354SHOW CREATE TABLE t2; 1355Table Create Table 1356t2 CREATE TABLE `t2` ( 1357 `NULLIF(c_date, 1)` date DEFAULT NULL, 1358 `NULLIF(c_date, c_smallint)` date DEFAULT NULL, 1359 `NULLIF(c_date, c_tinyint)` date DEFAULT NULL, 1360 `NULLIF(c_date, c_int)` date DEFAULT NULL, 1361 `NULLIF(c_date, c_bigint)` date DEFAULT NULL, 1362 `NULLIF(c_date, c_float)` date DEFAULT NULL, 1363 `NULLIF(c_date, c_double)` date DEFAULT NULL, 1364 `NULLIF(c_date, c_decimal103)` date DEFAULT NULL, 1365 `NULLIF(c_date, c_varchar10)` date DEFAULT NULL, 1366 `NULLIF(c_date, c_text)` date DEFAULT NULL, 1367 `NULLIF(c_date, c_blob)` date DEFAULT NULL, 1368 `NULLIF(c_date, c_enum)` date DEFAULT NULL, 1369 `NULLIF(c_date, c_datetime3)` date DEFAULT NULL, 1370 `NULLIF(c_date, c_timestamp3)` date DEFAULT NULL, 1371 `NULLIF(c_date, c_date)` date DEFAULT NULL, 1372 `NULLIF(c_date, c_time)` date DEFAULT NULL 1373) ENGINE=MyISAM DEFAULT CHARSET=latin1 1374DROP TABLE t2; 1375CREATE TABLE t2 AS SELECT 1376NULLIF(c_time, 1), 1377NULLIF(c_time, c_smallint), 1378NULLIF(c_time, c_tinyint), 1379NULLIF(c_time, c_int), 1380NULLIF(c_time, c_bigint), 1381NULLIF(c_time, c_float), 1382NULLIF(c_time, c_double), 1383NULLIF(c_time, c_decimal103), 1384NULLIF(c_time, c_varchar10), 1385NULLIF(c_time, c_text), 1386NULLIF(c_time, c_blob), 1387NULLIF(c_time, c_enum), 1388NULLIF(c_time, c_datetime3), 1389NULLIF(c_time, c_timestamp3), 1390NULLIF(c_time, c_date), 1391NULLIF(c_time, c_time) 1392FROM t1; 1393SHOW CREATE TABLE t2; 1394Table Create Table 1395t2 CREATE TABLE `t2` ( 1396 `NULLIF(c_time, 1)` time DEFAULT NULL, 1397 `NULLIF(c_time, c_smallint)` time DEFAULT NULL, 1398 `NULLIF(c_time, c_tinyint)` time DEFAULT NULL, 1399 `NULLIF(c_time, c_int)` time DEFAULT NULL, 1400 `NULLIF(c_time, c_bigint)` time DEFAULT NULL, 1401 `NULLIF(c_time, c_float)` time DEFAULT NULL, 1402 `NULLIF(c_time, c_double)` time DEFAULT NULL, 1403 `NULLIF(c_time, c_decimal103)` time DEFAULT NULL, 1404 `NULLIF(c_time, c_varchar10)` time DEFAULT NULL, 1405 `NULLIF(c_time, c_text)` time DEFAULT NULL, 1406 `NULLIF(c_time, c_blob)` time DEFAULT NULL, 1407 `NULLIF(c_time, c_enum)` time DEFAULT NULL, 1408 `NULLIF(c_time, c_datetime3)` time DEFAULT NULL, 1409 `NULLIF(c_time, c_timestamp3)` time DEFAULT NULL, 1410 `NULLIF(c_time, c_date)` time DEFAULT NULL, 1411 `NULLIF(c_time, c_time)` time DEFAULT NULL 1412) ENGINE=MyISAM DEFAULT CHARSET=latin1 1413DROP TABLE t2; 1414# 1415# Checking that the return type depends only on args[0], even if compared to a field 1416# 1417CREATE TABLE t2 AS SELECT 1418NULLIF(1, 1), 1419NULLIF(1, c_smallint), 1420NULLIF(1, c_tinyint), 1421NULLIF(1, c_int), 1422NULLIF(1, c_bigint), 1423NULLIF(1, c_float), 1424NULLIF(1, c_double), 1425NULLIF(1, c_decimal103), 1426NULLIF(1, c_varchar10), 1427NULLIF(1, c_text), 1428NULLIF(1, c_blob), 1429NULLIF(1, c_enum), 1430NULLIF(1, c_datetime3), 1431NULLIF(1, c_timestamp3), 1432NULLIF(1, c_date), 1433NULLIF(1, c_time) 1434FROM t1; 1435SHOW CREATE TABLE t2; 1436Table Create Table 1437t2 CREATE TABLE `t2` ( 1438 `NULLIF(1, 1)` int(1) DEFAULT NULL, 1439 `NULLIF(1, c_smallint)` int(1) DEFAULT NULL, 1440 `NULLIF(1, c_tinyint)` int(1) DEFAULT NULL, 1441 `NULLIF(1, c_int)` int(1) DEFAULT NULL, 1442 `NULLIF(1, c_bigint)` int(1) DEFAULT NULL, 1443 `NULLIF(1, c_float)` int(1) DEFAULT NULL, 1444 `NULLIF(1, c_double)` int(1) DEFAULT NULL, 1445 `NULLIF(1, c_decimal103)` int(1) DEFAULT NULL, 1446 `NULLIF(1, c_varchar10)` int(1) DEFAULT NULL, 1447 `NULLIF(1, c_text)` int(1) DEFAULT NULL, 1448 `NULLIF(1, c_blob)` int(1) DEFAULT NULL, 1449 `NULLIF(1, c_enum)` int(1) DEFAULT NULL, 1450 `NULLIF(1, c_datetime3)` int(1) DEFAULT NULL, 1451 `NULLIF(1, c_timestamp3)` int(1) DEFAULT NULL, 1452 `NULLIF(1, c_date)` int(1) DEFAULT NULL, 1453 `NULLIF(1, c_time)` int(1) DEFAULT NULL 1454) ENGINE=MyISAM DEFAULT CHARSET=latin1 1455DROP TABLE t2; 1456CREATE TABLE t2 AS SELECT 1457NULLIF(1.0, 1), 1458NULLIF(1.0, c_smallint), 1459NULLIF(1.0, c_tinyint), 1460NULLIF(1.0, c_int), 1461NULLIF(1.0, c_bigint), 1462NULLIF(1.0, c_float), 1463NULLIF(1.0, c_double), 1464NULLIF(1.0, c_decimal103), 1465NULLIF(1.0, c_varchar10), 1466NULLIF(1.0, c_text), 1467NULLIF(1.0, c_blob), 1468NULLIF(1.0, c_enum), 1469NULLIF(1.0, c_datetime3), 1470NULLIF(1.0, c_timestamp3), 1471NULLIF(1.0, c_date), 1472NULLIF(1.0, c_time) 1473FROM t1; 1474SHOW CREATE TABLE t2; 1475Table Create Table 1476t2 CREATE TABLE `t2` ( 1477 `NULLIF(1.0, 1)` decimal(2,1) DEFAULT NULL, 1478 `NULLIF(1.0, c_smallint)` decimal(2,1) DEFAULT NULL, 1479 `NULLIF(1.0, c_tinyint)` decimal(2,1) DEFAULT NULL, 1480 `NULLIF(1.0, c_int)` decimal(2,1) DEFAULT NULL, 1481 `NULLIF(1.0, c_bigint)` decimal(2,1) DEFAULT NULL, 1482 `NULLIF(1.0, c_float)` decimal(2,1) DEFAULT NULL, 1483 `NULLIF(1.0, c_double)` decimal(2,1) DEFAULT NULL, 1484 `NULLIF(1.0, c_decimal103)` decimal(2,1) DEFAULT NULL, 1485 `NULLIF(1.0, c_varchar10)` decimal(2,1) DEFAULT NULL, 1486 `NULLIF(1.0, c_text)` decimal(2,1) DEFAULT NULL, 1487 `NULLIF(1.0, c_blob)` decimal(2,1) DEFAULT NULL, 1488 `NULLIF(1.0, c_enum)` decimal(2,1) DEFAULT NULL, 1489 `NULLIF(1.0, c_datetime3)` decimal(2,1) DEFAULT NULL, 1490 `NULLIF(1.0, c_timestamp3)` decimal(2,1) DEFAULT NULL, 1491 `NULLIF(1.0, c_date)` decimal(2,1) DEFAULT NULL, 1492 `NULLIF(1.0, c_time)` decimal(2,1) DEFAULT NULL 1493) ENGINE=MyISAM DEFAULT CHARSET=latin1 1494DROP TABLE t2; 1495CREATE TABLE t2 AS SELECT 1496NULLIF(1e0, 1), 1497NULLIF(1e0, c_smallint), 1498NULLIF(1e0, c_tinyint), 1499NULLIF(1e0, c_int), 1500NULLIF(1e0, c_bigint), 1501NULLIF(1e0, c_float), 1502NULLIF(1e0, c_double), 1503NULLIF(1e0, c_decimal103), 1504NULLIF(1e0, c_varchar10), 1505NULLIF(1e0, c_text), 1506NULLIF(1e0, c_blob), 1507NULLIF(1e0, c_enum), 1508NULLIF(1e0, c_datetime3), 1509NULLIF(1e0, c_timestamp3), 1510NULLIF(1e0, c_date), 1511NULLIF(1e0, c_time) 1512FROM t1; 1513SHOW CREATE TABLE t2; 1514Table Create Table 1515t2 CREATE TABLE `t2` ( 1516 `NULLIF(1e0, 1)` double DEFAULT NULL, 1517 `NULLIF(1e0, c_smallint)` double DEFAULT NULL, 1518 `NULLIF(1e0, c_tinyint)` double DEFAULT NULL, 1519 `NULLIF(1e0, c_int)` double DEFAULT NULL, 1520 `NULLIF(1e0, c_bigint)` double DEFAULT NULL, 1521 `NULLIF(1e0, c_float)` double DEFAULT NULL, 1522 `NULLIF(1e0, c_double)` double DEFAULT NULL, 1523 `NULLIF(1e0, c_decimal103)` double DEFAULT NULL, 1524 `NULLIF(1e0, c_varchar10)` double DEFAULT NULL, 1525 `NULLIF(1e0, c_text)` double DEFAULT NULL, 1526 `NULLIF(1e0, c_blob)` double DEFAULT NULL, 1527 `NULLIF(1e0, c_enum)` double DEFAULT NULL, 1528 `NULLIF(1e0, c_datetime3)` double DEFAULT NULL, 1529 `NULLIF(1e0, c_timestamp3)` double DEFAULT NULL, 1530 `NULLIF(1e0, c_date)` double DEFAULT NULL, 1531 `NULLIF(1e0, c_time)` double DEFAULT NULL 1532) ENGINE=MyISAM DEFAULT CHARSET=latin1 1533DROP TABLE t2; 1534CREATE TABLE t2 AS SELECT 1535NULLIF('1', 1), 1536NULLIF('1', c_smallint), 1537NULLIF('1', c_tinyint), 1538NULLIF('1', c_int), 1539NULLIF('1', c_bigint), 1540NULLIF('1', c_float), 1541NULLIF('1', c_double), 1542NULLIF('1', c_decimal103), 1543NULLIF('1', c_varchar10), 1544NULLIF('1', c_text), 1545NULLIF('1', c_blob), 1546NULLIF('1', c_enum), 1547NULLIF('1', c_datetime3), 1548NULLIF('1', c_timestamp3), 1549NULLIF('1', c_date), 1550NULLIF('1', c_time) 1551FROM t1; 1552SHOW CREATE TABLE t2; 1553Table Create Table 1554t2 CREATE TABLE `t2` ( 1555 `NULLIF('1', 1)` varchar(1) DEFAULT NULL, 1556 `NULLIF('1', c_smallint)` varchar(1) DEFAULT NULL, 1557 `NULLIF('1', c_tinyint)` varchar(1) DEFAULT NULL, 1558 `NULLIF('1', c_int)` varchar(1) DEFAULT NULL, 1559 `NULLIF('1', c_bigint)` varchar(1) DEFAULT NULL, 1560 `NULLIF('1', c_float)` varchar(1) DEFAULT NULL, 1561 `NULLIF('1', c_double)` varchar(1) DEFAULT NULL, 1562 `NULLIF('1', c_decimal103)` varchar(1) DEFAULT NULL, 1563 `NULLIF('1', c_varchar10)` varchar(1) DEFAULT NULL, 1564 `NULLIF('1', c_text)` varchar(1) DEFAULT NULL, 1565 `NULLIF('1', c_blob)` varchar(1) DEFAULT NULL, 1566 `NULLIF('1', c_enum)` varchar(1) DEFAULT NULL, 1567 `NULLIF('1', c_datetime3)` varchar(1) DEFAULT NULL, 1568 `NULLIF('1', c_timestamp3)` varchar(1) DEFAULT NULL, 1569 `NULLIF('1', c_date)` varchar(1) DEFAULT NULL, 1570 `NULLIF('1', c_time)` varchar(1) DEFAULT NULL 1571) ENGINE=MyISAM DEFAULT CHARSET=latin1 1572DROP TABLE t2; 1573CREATE TABLE t2 AS SELECT 1574NULLIF(TIME'10:10:10', 1), 1575NULLIF(TIME'10:10:10', c_smallint), 1576NULLIF(TIME'10:10:10', c_tinyint), 1577NULLIF(TIME'10:10:10', c_int), 1578NULLIF(TIME'10:10:10', c_bigint), 1579NULLIF(TIME'10:10:10', c_float), 1580NULLIF(TIME'10:10:10', c_double), 1581NULLIF(TIME'10:10:10', c_decimal103), 1582NULLIF(TIME'10:10:10', c_varchar10), 1583NULLIF(TIME'10:10:10', c_text), 1584NULLIF(TIME'10:10:10', c_blob), 1585NULLIF(TIME'10:10:10', c_enum), 1586NULLIF(TIME'10:10:10', c_datetime3), 1587NULLIF(TIME'10:10:10', c_timestamp3), 1588NULLIF(TIME'10:10:10', c_date), 1589NULLIF(TIME'10:10:10', c_time) 1590FROM t1; 1591SHOW CREATE TABLE t2; 1592Table Create Table 1593t2 CREATE TABLE `t2` ( 1594 `NULLIF(TIME'10:10:10', 1)` time DEFAULT NULL, 1595 `NULLIF(TIME'10:10:10', c_smallint)` time DEFAULT NULL, 1596 `NULLIF(TIME'10:10:10', c_tinyint)` time DEFAULT NULL, 1597 `NULLIF(TIME'10:10:10', c_int)` time DEFAULT NULL, 1598 `NULLIF(TIME'10:10:10', c_bigint)` time DEFAULT NULL, 1599 `NULLIF(TIME'10:10:10', c_float)` time DEFAULT NULL, 1600 `NULLIF(TIME'10:10:10', c_double)` time DEFAULT NULL, 1601 `NULLIF(TIME'10:10:10', c_decimal103)` time DEFAULT NULL, 1602 `NULLIF(TIME'10:10:10', c_varchar10)` time DEFAULT NULL, 1603 `NULLIF(TIME'10:10:10', c_text)` time DEFAULT NULL, 1604 `NULLIF(TIME'10:10:10', c_blob)` time DEFAULT NULL, 1605 `NULLIF(TIME'10:10:10', c_enum)` time DEFAULT NULL, 1606 `NULLIF(TIME'10:10:10', c_datetime3)` time DEFAULT NULL, 1607 `NULLIF(TIME'10:10:10', c_timestamp3)` time DEFAULT NULL, 1608 `NULLIF(TIME'10:10:10', c_date)` time DEFAULT NULL, 1609 `NULLIF(TIME'10:10:10', c_time)` time DEFAULT NULL 1610) ENGINE=MyISAM DEFAULT CHARSET=latin1 1611DROP TABLE t2; 1612DROP TABLE t1; 1613# 1614# MDEV-7759 NULLIF(x,y) is not equal to CASE WHEN x=y THEN NULL ELSE x END 1615# 1616CREATE TABLE t1 (a YEAR); 1617INSERT INTO t1 VALUES (2010),(2020); 1618SELECT * FROM t1 WHERE a=2010 AND NULLIF(10.1,a) IS NULL; 1619a 16202010 1621EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND NULLIF(10.1,a) IS NULL; 1622id select_type table type possible_keys key key_len ref rows filtered Extra 16231 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 1624Warnings: 1625Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2010 1626SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL ELSE 10.1 END IS NULL; 1627a 16282010 1629EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2010 AND CASE WHEN 10.1=a THEN NULL ELSE 10.1 END IS NULL; 1630id select_type table type possible_keys key key_len ref rows filtered Extra 16311 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 1632Warnings: 1633Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2010 1634DROP TABLE t1; 1635# Two warnings expected 1636SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1637CREATE TABLE t1 AS SELECT 1638NULLIF(TIMESTAMP'2001-01-01 00:00:00',1) AS a, 1639CASE WHEN TIMESTAMP'2001-01-01 00:00:00'=1 THEN NULL 1640ELSE TIMESTAMP'2001-01-01 00:00:00' 1641 END AS b; 1642Warnings: 1643Warning 1292 Truncated incorrect datetime value: '1' 1644Warning 1292 Truncated incorrect datetime value: '1' 1645DROP TABLE t1; 1646# 1647# MDEV-8785 Wrong results for EXPLAIN EXTENDED...WHERE NULLIF(latin1_col, _utf8'a' COLLATE utf8_bin) IS NOT NULL 1648# 1649CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); 1650INSERT INTO t1 VALUES ('a'),('A'); 1651SELECT a, NULLIF(a,_utf8'a' COLLATE utf8_bin) IS NULL FROM t1; 1652a NULLIF(a,_utf8'a' COLLATE utf8_bin) IS NULL 1653a 1 1654A 0 1655SELECT CHARSET(NULLIF(a,_utf8'a' COLLATE utf8_bin)) FROM t1; 1656CHARSET(NULLIF(a,_utf8'a' COLLATE utf8_bin)) 1657latin1 1658latin1 1659EXPLAIN EXTENDED SELECT NULLIF(a,_utf8'a' COLLATE utf8_bin) IS NULL AS expr FROM t1; 1660id select_type table type possible_keys key key_len ref rows filtered Extra 16611 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 1662Warnings: 1663Note 1003 select (case when convert(`test`.`t1`.`a` using utf8) = _utf8'a' collate utf8_bin then NULL else `test`.`t1`.`a` end) is null AS `expr` from `test`.`t1` 1664DROP TABLE t1; 1665# 1666# MDEV-8740 Wrong result for SELECT..WHERE year_field=10 AND NULLIF(year_field,2011.1)='2011' 1667# 1668CREATE TABLE t1 (a YEAR); 1669INSERT INTO t1 VALUES (2010),(2011); 1670SELECT a=10 AND NULLIF(a,2011.1)='2011' AS cond FROM t1; 1671cond 16720 16730 1674SELECT * FROM t1 WHERE a=10; 1675a 16762010 1677SELECT * FROM t1 WHERE NULLIF(a,2011.1)='2011'; 1678a 1679SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011'; 1680a 1681EXPLAIN EXTENDED 1682SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011'; 1683id select_type table type possible_keys key key_len ref rows filtered Extra 16841 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 1685Warnings: 1686Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2010 and (case when 2010 = 2011 then NULL else `test`.`t1`.`a` end) = '2011' 1687EXPLAIN EXTENDED 1688SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)=CONCAT('2011',RAND()); 1689id select_type table type possible_keys key key_len ref rows filtered Extra 16901 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 1691Warnings: 1692Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2010 and (case when 2010 = 2011 then NULL else `test`.`t1`.`a` end) = concat('2011',rand()) 1693DROP TABLE t1; 1694# 1695# MDEV-8754 Wrong result for SELECT..WHERE year_field=2020 AND NULLIF(year_field,2010)='2020' 1696# 1697CREATE TABLE t1 (a YEAR); 1698INSERT INTO t1 VALUES (2010),(2020); 1699SELECT * FROM t1 WHERE a=2020; 1700a 17012020 1702SELECT * FROM t1 WHERE NULLIF(a,2010)='2020'; 1703a 17042020 1705SELECT * FROM t1 WHERE a=2020 AND NULLIF(a,2010)='2020'; 1706a 17072020 1708EXPLAIN EXTENDED 1709SELECT * FROM t1 WHERE a=2020 AND NULLIF(a,2010)='2020'; 1710id select_type table type possible_keys key key_len ref rows filtered Extra 17111 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 1712Warnings: 1713Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2020 and (case when 2020 = 2010 then NULL else `test`.`t1`.`a` end) = '2020' 1714EXPLAIN EXTENDED 1715SELECT * FROM t1 WHERE a=2020 AND NULLIF(a,2010)=CONCAT('2020',RAND()); 1716id select_type table type possible_keys key key_len ref rows filtered Extra 17171 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where 1718Warnings: 1719Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2020 and (case when 2020 = 2010 then NULL else `test`.`t1`.`a` end) = concat('2020',rand()) 1720DROP TABLE t1; 1721# 1722# MDEV-9181 (NULLIF(count(table.col)), 0) gives wrong result on 10.1.x 1723# 1724CREATE TABLE t1 (c1 varchar(50) DEFAULT NULL); 1725INSERT INTO t1 (c1) VALUES ('hello'), ('hello\r\n'), ('hello'),('hello'); 1726SELECT NULLIF(COUNT(c1),0) FROM t1; 1727NULLIF(COUNT(c1),0) 17284 1729SELECT CASE WHEN COUNT(c1)=0 THEN NULL ELSE COUNT(c1) END FROM t1; 1730CASE WHEN COUNT(c1)=0 THEN NULL ELSE COUNT(c1) END 17314 1732SELECT NULLIF(COUNT(c1)+0,0) AS c1,NULLIF(CAST(COUNT(c1) AS SIGNED),0) AS c2,NULLIF(CONCAT(COUNT(c1)),0) AS c3 FROM t1; 1733c1 c2 c3 17344 4 4 1735SELECT NULLIF(COUNT(DISTINCT c1),0) FROM t1; 1736NULLIF(COUNT(DISTINCT c1),0) 17372 1738SELECT CASE WHEN COUNT(DISTINCT c1)=0 THEN NULL ELSE COUNT(DISTINCT c1) END FROM t1; 1739CASE WHEN COUNT(DISTINCT c1)=0 THEN NULL ELSE COUNT(DISTINCT c1) END 17402 1741DROP TABLE t1; 1742CREATE TABLE t1 ( 1743id INT NOT NULL, 1744c1 INT DEFAULT NULL 1745); 1746INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(2,4); 1747SELECT NULLIF(COUNT(c1),0) AS c1,NULLIF(COUNT(c1)+0,0) AS c1_wrapped,CASE WHEN COUNT(c1) IS NULL THEN 0 ELSE COUNT(c1) END AS c1_case FROM t1 GROUP BY id; 1748c1 c1_wrapped c1_case 17492 2 2 17502 2 2 1751DROP TABLE t1; 1752CREATE TABLE t1 (a INT); 1753INSERT INTO t1 VALUES (1),(2),(3); 1754SET @a=0; 1755SELECT NULLIF(LAST_VALUE(@a:=@a+1,a),0) FROM t1; 1756NULLIF(LAST_VALUE(@a:=@a+1,a),0) 17571 17582 17593 1760SELECT @a; 1761@a 17626 1763SET @a=0; 1764SELECT NULLIF(AVG(a),0), NULLIF(AVG(LAST_VALUE(@a:=@a+1,a)),0) FROM t1; 1765NULLIF(AVG(a),0) NULLIF(AVG(LAST_VALUE(@a:=@a+1,a)),0) 17662.0000 2.0000 1767SELECT @a; 1768@a 17693 1770EXPLAIN EXTENDED SELECT NULLIF(a,0) FROM t1; 1771id select_type table type possible_keys key key_len ref rows filtered Extra 17721 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 1773Warnings: 1774Note 1003 select nullif(`test`.`t1`.`a`,0) AS `NULLIF(a,0)` from `test`.`t1` 1775EXPLAIN EXTENDED SELECT NULLIF(AVG(a),0) FROM t1; 1776id select_type table type possible_keys key key_len ref rows filtered Extra 17771 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 1778Warnings: 1779Note 1003 select nullif(<cache>(avg(`test`.`t1`.`a`)),0) AS `NULLIF(AVG(a),0)` from `test`.`t1` 1780DROP TABLE t1; 1781create table t1 (col1 varchar(50)); 1782create view v1 AS select nullif(count(distinct col1),0) from t1; 1783show create view v1; 1784View Create View character_set_client collation_connection 1785v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select nullif(count(distinct `t1`.`col1`),0) AS `nullif(count(distinct col1),0)` from `t1` latin1 latin1_swedish_ci 1786drop view v1; 1787drop table t1; 1788create table t1 (col1 varchar(50) default null); 1789insert into t1 (col1) values ('hello'), ('hello'), ('hello'); 1790create view v1 as select nullif(count(col1),0) from t1; 1791select * from v1; 1792nullif(count(col1),0) 17933 1794select nullif(count(col1),0) from t1; 1795nullif(count(col1),0) 17963 1797drop view v1; 1798drop table t1; 1799select nullif((select 1), (select 2)); 1800nullif((select 1), (select 2)) 18011 1802create table t1 (f int); 1803insert into t1 values (1),(2); 1804select nullif( not f, 1 ) from t1; 1805nullif( not f, 1 ) 18060 18070 1808drop table t1; 1809set names utf8; 1810create table t1 (f1 varchar(10)); 1811insert into t1 values ('2015-12-31'); 1812select power( timestamp( nullif( '2002-09-08', f1 ) ), 24 ) from t1; 1813ERROR 22003: DOUBLE value is out of range in 'pow(cast((case when '2002-09-08' = `test`.`t1`.`f1` then NULL else '2002-09-08' end) as datetime(6)),24)' 1814drop table t1; 1815CREATE TABLE t1 (f1 INT); 1816INSERT INTO t1 VALUES (1),(2); 1817PREPARE stmt FROM "SELECT * FROM t1 WHERE NULLIF( ( 1, 2 ) IN ( SELECT 3, 4 ), 1 )"; 1818EXECUTE stmt; 1819f1 1820EXECUTE stmt; 1821f1 1822DROP TABLE t1; 1823CREATE TABLE t1 (i INT); 1824INSERT INTO t1 VALUES (1),(2); 1825SELECT * FROM t1 WHERE NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(i = ROUND(0), 14), 13), 12), 11), 10), 9), 8), 7), 6), 5), 4), 3), 2), 1); 1826i 1827DROP TABLE t1; 1828# 1829# MDEV-10347 mysqld got signal 11 1830# 1831CREATE TABLE t1 (f1 VARCHAR(10), f2 VARCHAR(40)); 1832CREATE TABLE t2 (f3 VARCHAR(20)); 1833PREPARE stmt FROM " 1834 SELECT ( 1835 SELECT IFNULL(f3,4) FROM t2 1836 WHERE IFNULL(NULLIF(f1,''),1) 1837 ) AS sq 1838 FROM t1 1839 GROUP BY f2 1840"; 1841EXECUTE stmt; 1842sq 1843DEALLOCATE PREPARE stmt; 1844DROP TABLE t2,t1; 1845# 1846# MDEV-10236 Where expression with NOT function gives incorrect result 1847# 1848CREATE TABLE t1 (c1 INT); 1849INSERT INTO t1 VALUES (1),(2),(3); 1850EXPLAIN EXTENDED 1851SELECT * FROM t1 WHERE ((c1 IS NOT NULL) >= (NOT TRUE)) IS NOT NULL; 1852id select_type table type possible_keys key key_len ref rows filtered Extra 18531 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where 1854Warnings: 1855Note 1003 select `test`.`t1`.`c1` AS `c1` from `test`.`t1` where `test`.`t1`.`c1` is not null >= 0 is not null 1856SELECT * FROM t1 WHERE ((c1 IS NOT NULL) >= (NOT TRUE)) IS NOT NULL; 1857c1 18581 18592 18603 1861DROP TABLE t1; 1862# 1863# End of 10.1 tests 1864# 1865