Edit File: func_if.result
drop table if exists t1; select IF(0,"ERROR","this"),IF(1,"is","ERROR"),IF(NULL,"ERROR","a"),IF(1,2,3)|0,IF(1,2.0,3.0)+0 ; IF(0,"ERROR","this") IF(1,"is","ERROR") IF(NULL,"ERROR","a") IF(1,2,3)|0 IF(1,2.0,3.0)+0 this is a 2 2.0 CREATE TABLE t1 (st varchar(255) NOT NULL, u int(11) NOT NULL) ENGINE=MyISAM; INSERT INTO t1 VALUES ('a',1),('A',1),('aa',1),('AA',1),('a',1),('aaa',0),('BBB',0); select if(1,st,st) s from t1 order by s; s a A a aa AA aaa BBB select if(u=1,st,st) s from t1 order by s; s a A a aa AA aaa BBB select if(u=1,binary st,st) s from t1 order by s; s A AA BBB a a aa aaa select if(u=1,st,binary st) s from t1 where st like "%a%" order by s; s A AA a a aa aaa explain extended select if(u=1,st,binary st) s from t1 where st like "%a%" order by s; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using where; Using filesort Warnings: Note 1003 /* select#1 */ 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 `s` select nullif(u, 1) from t1; nullif(u, 1) NULL NULL NULL NULL NULL 0 0 explain extended select nullif(u, 1) from t1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 NULL Warnings: Note 1003 /* select#1 */ select nullif(`test`.`t1`.`u`,1) AS `nullif(u, 1)` from `test`.`t1` drop table t1; select nullif(1,'test'); nullif(1,'test') 1 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'test' select NULLIF(NULL,NULL), NULLIF(NULL,1), NULLIF(NULL,1.0), NULLIF(NULL,"test"); NULLIF(NULL,NULL) NULLIF(NULL,1) NULLIF(NULL,1.0) NULLIF(NULL,"test") NULL NULL NULL NULL select NULLIF(1,NULL), NULLIF(1.0, NULL), NULLIF("test", NULL); NULLIF(1,NULL) NULLIF(1.0, NULL) NULLIF("test", NULL) 1 1.0 test create table t1 (num double(12,2)); insert into t1 values (144.54); select sum(if(num is null,0.00,num)) from t1; sum(if(num is null,0.00,num)) 144.54 drop table t1; create table t1 (x int, y int); insert into t1 values (0,6),(10,16),(20,26),(30,10),(40,46),(50,56); select min(if(y -x > 5,y,NULL)), max(if(y - x > 5,y,NULL)) from t1; min(if(y -x > 5,y,NULL)) max(if(y - x > 5,y,NULL)) 6 56 drop table t1; create table t1 (a int); insert t1 values (1),(2); select if(1>2,a,avg(a)) from t1; if(1>2,a,avg(a)) 1.5000 drop table t1; SELECT NULLIF(5,5) IS NULL, NULLIF(5,5) IS NOT NULL; NULLIF(5,5) IS NULL NULLIF(5,5) IS NOT NULL 1 0 CREATE TABLE `t1` ( `id` int(11) NOT NULL , `date` int(10) default NULL, `text` varchar(32) NOT NULL ); INSERT INTO t1 VALUES (1,1110000000,'Day 1'),(2,1111000000,'Day 2'),(3,1112000000,'Day 3'); SELECT id, IF(date IS NULL, '-', FROM_UNIXTIME(date, '%d-%m-%Y')) AS date_ord, text FROM t1 ORDER BY date_ord ASC; id date_ord text 1 05-03-2005 Day 1 2 16-03-2005 Day 2 3 28-03-2005 Day 3 SELECT id, IF(date IS NULL, '-', FROM_UNIXTIME(date, '%d-%m-%Y')) AS date_ord, text FROM t1 ORDER BY date_ord DESC; id date_ord text 3 28-03-2005 Day 3 2 16-03-2005 Day 2 1 05-03-2005 Day 1 DROP TABLE t1; CREATE TABLE t1 (a CHAR(10)); INSERT INTO t1 VALUES ('aaa'), (NULL), (''), ('bbb'); SELECT a, NULLIF(a,'') FROM t1; a NULLIF(a,'') aaa aaa NULL NULL NULL bbb bbb SELECT a, NULLIF(a,'') FROM t1 WHERE NULLIF(a,'') IS NULL; a NULLIF(a,'') NULL NULL NULL DROP TABLE t1; create table t1 (f1 int, f2 int); insert into t1 values(1,1),(0,0); select f1, f2, if(f1, 40.0, 5.00) from t1 group by f1 order by f2; f1 f2 if(f1, 40.0, 5.00) 0 0 5.00 1 1 40.00 drop table t1; select if(0, 18446744073709551610, 18446744073709551610); if(0, 18446744073709551610, 18446744073709551610) 18446744073709551610 CREATE TABLE t1(a DECIMAL(10,3)); SELECT t1.a, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2,0)))))))))))))))))))))))))))))) + 1 FROM t1; a IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((ROUND(t1.a,2)=1), 2, IF((R DROP TABLE t1; CREATE TABLE t1 (c LONGTEXT); INSERT INTO t1 VALUES(1), (2), (3), (4), ('12345678901234567890'); SELECT * FROM (SELECT MAX(IF(1, CAST(c AS UNSIGNED), 0)) FROM t1) AS te; MAX(IF(1, CAST(c AS UNSIGNED), 0)) 12345678901234567890 SELECT * FROM (SELECT MAX(IFNULL(CAST(c AS UNSIGNED), 0)) FROM t1) AS te; MAX(IFNULL(CAST(c AS UNSIGNED), 0)) 12345678901234567890 DROP TABLE t1; End of 5.0 tests # # Bug#55077: Assertion failed: width > 0 && to != ((void *)0), file .\dtoa.c # CREATE TABLE t1 (a LONGBLOB, b DOUBLE); INSERT INTO t1 VALUES (NULL, 0), (NULL, 1); SELECT IF(b, (SELECT a FROM t1 LIMIT 1), b) c FROM t1 GROUP BY c; c NULL 0 DROP TABLE t1; # # Bug#12620084 # ASSERTION WHEN CHECKING FIELD_TYPES[FIELD_POS] AND IT'S MYSQL_TYPE_NULL # SELECT if(0, (SELECT min('hello')), NULL); if(0, (SELECT min('hello')), NULL) NULL SELECT if(1, (SELECT min('hello')), NULL); if(1, (SELECT min('hello')), NULL) hello SELECT if(0, NULL, (SELECT min('hello'))); if(0, NULL, (SELECT min('hello'))) hello SELECT if(1, NULL, (SELECT min('hello'))); if(1, NULL, (SELECT min('hello'))) NULL # # Bug#12532830 # SIGFPE OR ASSERTION (PRECISION <= ((9 * 9) - 8*2)) && (DEC <= 30) # select sum(distinct(if('a', (select adddate(elt(convert(9999999999999999999999999999999999999,decimal(64,0)),count(*)), interval 1 day)) , .1))) as foo; foo 0.1 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'a'