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#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`
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	NULL
59Warnings:
60Note	1003	/* select#1 */ 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), ('12345678901234567890');
181SELECT * FROM (SELECT MAX(IF(1, CAST(c AS UNSIGNED), 0)) FROM t1) AS te;
182MAX(IF(1, CAST(c AS UNSIGNED), 0))
18312345678901234567890
184SELECT * FROM (SELECT MAX(IFNULL(CAST(c AS UNSIGNED), 0)) FROM t1) AS te;
185MAX(IFNULL(CAST(c AS UNSIGNED), 0))
18612345678901234567890
187DROP TABLE t1;
188End of 5.0 tests
189#
190# Bug#55077: Assertion failed: width > 0 && to != ((void *)0), file .\dtoa.c
191#
192CREATE TABLE t1 (a LONGBLOB, b DOUBLE);
193INSERT INTO t1 VALUES (NULL, 0), (NULL, 1);
194SELECT IF(b, (SELECT a FROM t1 LIMIT 1), b) c FROM t1 GROUP BY c;
195c
196NULL
1970
198DROP TABLE t1;
199#
200# Bug#12620084
201# ASSERTION WHEN CHECKING FIELD_TYPES[FIELD_POS] AND IT'S MYSQL_TYPE_NULL
202#
203SELECT if(0, (SELECT min('hello')), NULL);
204if(0, (SELECT min('hello')), NULL)
205NULL
206SELECT if(1, (SELECT min('hello')), NULL);
207if(1, (SELECT min('hello')), NULL)
208hello
209SELECT if(0, NULL, (SELECT min('hello')));
210if(0, NULL, (SELECT min('hello')))
211hello
212SELECT if(1, NULL, (SELECT min('hello')));
213if(1, NULL, (SELECT min('hello')))
214NULL
215#
216# Bug#12532830
217# SIGFPE OR ASSERTION (PRECISION <= ((9 * 9) - 8*2)) && (DEC <= 30)
218#
219select
220sum(distinct(if('a',
221(select adddate(elt(convert(9999999999999999999999999999999999999,decimal(64,0)),count(*)),
222interval 1 day))
223, .1))) as foo;
224foo
2250.1
226Warnings:
227Warning	1292	Truncated incorrect DOUBLE value: 'a'
228