1drop table if exists t1; 2select IF(0,"ERROR","this"),IF(1,"is","ERROR"),IF(NULL,"ERROR","a"),IF(1,2,3)|0,IF(1,2.0,3.0)+0 ; 3IF(0,"ERROR","this") IF(1,"is","ERROR") IF(NULL,"ERROR","a") IF(1,2,3)|0 IF(1,2.0,3.0)+0 4this is a 2 2.0 5CREATE TABLE t1 (st varchar(255) NOT NULL, u int(11) NOT NULL) ENGINE=MyISAM; 6INSERT INTO t1 VALUES ('a',1),('A',1),('aa',1),('AA',1),('a',1),('aaa',0),('BBB',0); 7select if(1,st,st) s from t1 order by s; 8s 9a 10A 11a 12aa 13AA 14aaa 15BBB 16select if(u=1,st,st) s from t1 order by s; 17s 18a 19A 20a 21aa 22AA 23aaa 24BBB 25select if(u=1,binary st,st) s from t1 order by s; 26s 27A 28AA 29BBB 30a 31a 32aa 33aaa 34select if(u=1,st,binary st) s from t1 where st like "%a%" order by s; 35s 36A 37AA 38a 39a 40aa 41aaa 42explain extended select if(u=1,st,binary st) s from t1 where st like "%a%" order by s; 43id select_type table type possible_keys key key_len ref rows filtered Extra 441 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using where; Using filesort 45Warnings: 46Note 1003 select if(`test`.`t1`.`u` = 1,`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) AS `s` from `test`.`t1` where `test`.`t1`.`st` like '%a%' order by if(`test`.`t1`.`u` = 1,`test`.`t1`.`st`,cast(`test`.`t1`.`st` as char charset binary)) 47select nullif(u, 1) from t1; 48nullif(u, 1) 49NULL 50NULL 51NULL 52NULL 53NULL 540 550 56explain extended select nullif(u, 1) from t1; 57id select_type table type possible_keys key key_len ref rows filtered Extra 581 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 59Warnings: 60Note 1003 select nullif(`test`.`t1`.`u`,1) AS `nullif(u, 1)` from `test`.`t1` 61drop table t1; 62select nullif(1,'test'); 63nullif(1,'test') 641 65Warnings: 66Warning 1292 Truncated incorrect DOUBLE value: 'test' 67select NULLIF(NULL,NULL), NULLIF(NULL,1), NULLIF(NULL,1.0), NULLIF(NULL,"test"); 68NULLIF(NULL,NULL) NULLIF(NULL,1) NULLIF(NULL,1.0) NULLIF(NULL,"test") 69NULL NULL NULL NULL 70select NULLIF(1,NULL), NULLIF(1.0, NULL), NULLIF("test", NULL); 71NULLIF(1,NULL) NULLIF(1.0, NULL) NULLIF("test", NULL) 721 1.0 test 73create table t1 (num double(12,2)); 74insert into t1 values (144.54); 75select sum(if(num is null,0.00,num)) from t1; 76sum(if(num is null,0.00,num)) 77144.54 78drop table t1; 79create table t1 (x int, y int); 80insert into t1 values (0,6),(10,16),(20,26),(30,10),(40,46),(50,56); 81select min(if(y -x > 5,y,NULL)), max(if(y - x > 5,y,NULL)) from t1; 82min(if(y -x > 5,y,NULL)) max(if(y - x > 5,y,NULL)) 836 56 84drop table t1; 85create table t1 (a int); 86insert t1 values (1),(2); 87select if(1>2,a,avg(a)) from t1; 88if(1>2,a,avg(a)) 891.5000 90drop table t1; 91SELECT NULLIF(5,5) IS NULL, NULLIF(5,5) IS NOT NULL; 92NULLIF(5,5) IS NULL NULLIF(5,5) IS NOT NULL 931 0 94CREATE TABLE `t1` ( 95`id` int(11) NOT NULL , 96`date` int(10) default NULL, 97`text` varchar(32) NOT NULL 98); 99INSERT INTO t1 VALUES (1,1110000000,'Day 1'),(2,1111000000,'Day 2'),(3,1112000000,'Day 3'); 100SELECT id, IF(date IS NULL, '-', FROM_UNIXTIME(date, '%d-%m-%Y')) AS date_ord, text FROM t1 ORDER BY date_ord ASC; 101id date_ord text 1021 05-03-2005 Day 1 1032 16-03-2005 Day 2 1043 28-03-2005 Day 3 105SELECT id, IF(date IS NULL, '-', FROM_UNIXTIME(date, '%d-%m-%Y')) AS date_ord, text FROM t1 ORDER BY date_ord DESC; 106id date_ord text 1073 28-03-2005 Day 3 1082 16-03-2005 Day 2 1091 05-03-2005 Day 1 110DROP TABLE t1; 111CREATE TABLE t1 (a CHAR(10)); 112INSERT INTO t1 VALUES ('aaa'), (NULL), (''), ('bbb'); 113SELECT a, NULLIF(a,'') FROM t1; 114a NULLIF(a,'') 115aaa aaa 116NULL NULL 117 NULL 118bbb bbb 119SELECT a, NULLIF(a,'') FROM t1 WHERE NULLIF(a,'') IS NULL; 120a NULLIF(a,'') 121NULL NULL 122 NULL 123DROP TABLE t1; 124create table t1 (f1 int, f2 int); 125insert into t1 values(1,1),(0,0); 126select f1, f2, if(f1, 40.0, 5.00) from t1 group by f1 order by f2; 127f1 f2 if(f1, 40.0, 5.00) 1280 0 5.00 1291 1 40.00 130drop table t1; 131select if(0, 18446744073709551610, 18446744073709551610); 132if(0, 18446744073709551610, 18446744073709551610) 13318446744073709551610 134CREATE TABLE t1(a DECIMAL(10,3)); 135SELECT t1.a, 136IF((ROUND(t1.a,2)=1), 2, 137IF((ROUND(t1.a,2)=1), 2, 138IF((ROUND(t1.a,2)=1), 2, 139IF((ROUND(t1.a,2)=1), 2, 140IF((ROUND(t1.a,2)=1), 2, 141IF((ROUND(t1.a,2)=1), 2, 142IF((ROUND(t1.a,2)=1), 2, 143IF((ROUND(t1.a,2)=1), 2, 144IF((ROUND(t1.a,2)=1), 2, 145IF((ROUND(t1.a,2)=1), 2, 146IF((ROUND(t1.a,2)=1), 2, 147IF((ROUND(t1.a,2)=1), 2, 148IF((ROUND(t1.a,2)=1), 2, 149IF((ROUND(t1.a,2)=1), 2, 150IF((ROUND(t1.a,2)=1), 2, 151IF((ROUND(t1.a,2)=1), 2, 152IF((ROUND(t1.a,2)=1), 2, 153IF((ROUND(t1.a,2)=1), 2, 154IF((ROUND(t1.a,2)=1), 2, 155IF((ROUND(t1.a,2)=1), 2, 156IF((ROUND(t1.a,2)=1), 2, 157IF((ROUND(t1.a,2)=1), 2, 158IF((ROUND(t1.a,2)=1), 2, 159IF((ROUND(t1.a,2)=1), 2, 160IF((ROUND(t1.a,2)=1), 2, 161IF((ROUND(t1.a,2)=1), 2, 162IF((ROUND(t1.a,2)=1), 2, 163IF((ROUND(t1.a,2)=1), 2, 164IF((ROUND(t1.a,2)=1), 2, 165IF((ROUND(t1.a,2)=1), 2,0)))))))))))))))))))))))))))))) + 1 166FROM t1; 167a IF((ROUND(t1.a,2)=1), 2, 168IF((ROUND(t1.a,2)=1), 2, 169IF((ROUND(t1.a,2)=1), 2, 170IF((ROUND(t1.a,2)=1), 2, 171IF((ROUND(t1.a,2)=1), 2, 172IF((ROUND(t1.a,2)=1), 2, 173IF((ROUND(t1.a,2)=1), 2, 174IF((ROUND(t1.a,2)=1), 2, 175IF((ROUND(t1.a,2)=1), 2, 176IF((ROUND(t1.a,2)=1), 2, 177IF((R 178DROP TABLE t1; 179CREATE TABLE t1 (c LONGTEXT); 180INSERT INTO t1 VALUES(1), (2), (3), (4), ('1234567890123456789'); 181SELECT IF(1, CAST(c AS UNSIGNED), 0) FROM t1; 182IF(1, CAST(c AS UNSIGNED), 0) 1831 1842 1853 1864 1871234567890123456789 188SELECT * FROM (SELECT MAX(IF(1, CAST(c AS UNSIGNED), 0)) FROM t1) AS te; 189MAX(IF(1, CAST(c AS UNSIGNED), 0)) 1901234567890123456789 191SELECT * FROM (SELECT MAX(IFNULL(CAST(c AS UNSIGNED), 0)) FROM t1) AS te; 192MAX(IFNULL(CAST(c AS UNSIGNED), 0)) 1931234567890123456789 194DROP TABLE t1; 195End of 5.0 tests 196# 197# Bug#55077: Assertion failed: width > 0 && to != ((void *)0), file .\dtoa.c 198# 199CREATE TABLE t1 (a LONGBLOB, b DOUBLE); 200INSERT INTO t1 VALUES (NULL, 0), (NULL, 1); 201SELECT IF(b, (SELECT a FROM t1 LIMIT 1), b) c FROM t1 GROUP BY c; 202c 203NULL 2040 205DROP TABLE t1; 206# 207# Bug#12532830 208# SIGFPE OR ASSERTION (PRECISION <= ((9 * 9) - 8*2)) && (DEC <= 30) 209# 210select 211sum(distinct(if('a', 212(select adddate(elt(convert(9999999999999999999999999999999999999,decimal(64,0)),count(*)), 213interval 1 day)) 214, .1))) as foo; 215foo 2160.1 217Warnings: 218Warning 1292 Truncated incorrect DOUBLE value: 'a' 219Warning 1292 Truncated incorrect DOUBLE value: 'a' 220# 221# LP bug#998321 Simple query with IF expression causes an 222# assertion abort (see also mysql Bug#12620084) 223# 224SELECT if(0, (SELECT min('hello')), NULL); 225if(0, (SELECT min('hello')), NULL) 226NULL 227SELECT if(1, (SELECT min('hello')), NULL); 228if(1, (SELECT min('hello')), NULL) 229hello 230SELECT if(0, NULL, (SELECT min('hello'))); 231if(0, NULL, (SELECT min('hello'))) 232hello 233SELECT if(1, NULL, (SELECT min('hello'))); 234if(1, NULL, (SELECT min('hello'))) 235NULL 236End of 5.2 tests 237# 238# MDEV-8663: IF Statement returns multiple values erroneously 239# (or Assertion `!null_value' failed in Item::send(Protocol*, String*) 240# 241CREATE TABLE `t1` ( 242`datas` VARCHAR(25) NOT NULL 243) DEFAULT CHARSET=utf8; 244INSERT INTO `t1` VALUES ('1,2'), ('2,3'), ('3,4'); 245SELECT IF(FIND_IN_SET('1', `datas`), 1.5, IF(FIND_IN_SET('2', `datas`), 2, NULL)) AS `First`, '1' AS `Second`, '2' AS `Third` FROM `t1`; 246First Second Third 2471.5 1 2 2482.0 1 2 249NULL 1 2 250drop table t1; 251