summaryrefslogtreecommitdiff
path: root/mysql-test/r/func_if.result
blob: 887470e9771c266b51512fbf6d714d5dbd3737be (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
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 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))
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	
Warnings:
Note	1003	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), ('1234567890123456789');
SELECT IF(1, CAST(c AS UNSIGNED), 0) FROM t1;
IF(1, CAST(c AS UNSIGNED), 0)
1
2
3
4
1234567890123456789
SELECT * FROM (SELECT MAX(IF(1, CAST(c AS UNSIGNED), 0)) FROM t1) AS te;
MAX(IF(1, CAST(c AS UNSIGNED), 0))
1234567890123456789
SELECT * FROM (SELECT MAX(IFNULL(CAST(c AS UNSIGNED), 0)) FROM t1) AS te;
MAX(IFNULL(CAST(c AS UNSIGNED), 0))
1234567890123456789
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;