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);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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
Warnings:
Warning	1287	'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead
select if(u=1,st,binary st) s from t1 where st like "%a%" order by s;
s
A
AA
a
a
aa
aaa
Warnings:
Warning	1287	'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead
analyze table t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
explain select if(u=1,st,binary st) s from t1 where st like "%a%" order by s;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ALL	NULL	NULL	NULL	NULL	7	14.29	Using where; Using filesort
Warnings:
Warning	1287	'BINARY expr' is deprecated and will be removed in a future release. Please use CAST instead
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 select nullif(u, 1) from t1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	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));
Warnings:
Warning	1681	Specifying number of digits for floating point data types is deprecated and will be removed in a future release.
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
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
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.0
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
0
NULL
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'
#
# Bug#22148586 IF(BOOL, DATE STR, DATE STR) THROWS ERROR IN UPDATE
#
CREATE TABLE t1(c1 INT);
INSERT INTO t1 VALUES(1);
UPDATE t1 SET c1 = 2 WHERE IF(true, '2015-01-01', '2015-01-01') IS NOT NULL;
DROP TABLE t1;
#
# Bug#24492965 ERROR RESULTS FROM CALCULATION
#
CREATE TABLE test_grids_1 (
unq_id int(11) NOT NULL DEFAULT '0',
var_fld int(11) DEFAULT '0'
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO test_grids_1 VALUES
(1,4500),
(2,6000);
CREATE TABLE test_grid_dtl_1 (
dtl_id int(11) NOT NULL DEFAULT '0',
unq_id int(11) DEFAULT '0'
);
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
INSERT INTO test_grid_dtl_1 VALUES
(1,1),
(2,1),
(3,2);
SELECT g.calc_var, g.if_var, g.case_var
FROM
(
SELECT unq_id,
IF ( var_fld  > 5000, (     1 / var_fld ) , 5000 ) calc_var,
IF ( var_fld  > 5000, ( 00001 / var_fld ) , 5000 ) if_var,
CASE  var_fld  > 5000 WHEN TRUE THEN ( 1 / var_fld ) ELSE 5000 END case_var
FROM
test_grids_1
) g
JOIN
test_grid_dtl_1 d USING (unq_id)
ORDER BY 1
;
calc_var	if_var	case_var
0.0002	0.0002	0.0002
5000.0000	5000.0000	5000.0000
5000.0000	5000.0000	5000.0000
DROP TABLE test_grids_1, test_grid_dtl_1;
# Bug#25669606: regression: assertion failed: !is_temporal()
DO NULLIF(CAST(DATABASE() AS TIME), 1);
Warnings:
Warning	1292	Truncated incorrect time value: 'test'
Warning	1292	Truncated incorrect time value: 'test'
DO NULLIF(CAST(DATABASE() AS DATE), 1);
Warnings:
Warning	1292	Incorrect datetime value: 'test'
Warning	1292	Incorrect datetime value: 'test'
DO NULLIF(CAST(DATABASE() AS DATETIME), 1);
Warnings:
Warning	1292	Incorrect datetime value: 'test'
Warning	1292	Incorrect datetime value: 'test'
#
# Bug#28007237  NULLIF returns boolean in context of JSON_ARRAYAGG and JSON_OBJECTAGG's value
#
SELECT NULLIF(1,2);
NULLIF(1,2)
1
SELECT JSON_ARRAYAGG(NULLIF(1,2));
JSON_ARRAYAGG(NULLIF(1,2))
[1]
SELECT JSON_ARRAYAGG(CASE WHEN 1 = 2 THEN NULL ELSE 1 END);
JSON_ARRAYAGG(CASE WHEN 1 = 2 THEN NULL ELSE 1 END)
[1]
SELECT NULLIF(true,false);
NULLIF(true,false)
1
SELECT JSON_ARRAYAGG(NULLIF(true,false));
JSON_ARRAYAGG(NULLIF(true,false))
[1]
SELECT JSON_ARRAYAGG(CASE WHEN true = false THEN NULL ELSE true END);
JSON_ARRAYAGG(CASE WHEN true = false THEN NULL ELSE true END)
[1]
# Bug#32231557: Field_blob::store_to_mem: assertion `field_charset == cs' failed.
DO GROUP_CONCAT(NULLIF(ELT(1, @e), POINT(250,41)) ORDER BY 1);
# Bug#32816305: Prepare type checking error
CREATE TABLE t (t_date DATETIME NULL);
PREPARE ps FROM 'INSERT INTO t SET t_date = NULLIF(?, '''')';
SET @t_date = NOW();
EXECUTE ps USING @t_date;
DEALLOCATE PREPARE ps;
DROP TABLE t;
#
# Bug#34941663 casting unix_timestamp: \
#              Assertion `!null_value || is_nullable()' failed.
#
DO CAST(UNIX_TIMESTAMP(IF('',3,13339)) AS UNSIGNED);
Warnings:
Warning	1292	Incorrect datetime value: '13339'
DO CAST(UNIX_TIMESTAMP(CASE 1 WHEN 1 THEN 42 ELSE 42 END) AS UNSIGNED);
Warnings:
Warning	1292	Incorrect datetime value: '42'