| blob | 955a784f04cfd6c91100b0eb157d8e442e0f6850 |
1 drop table if exists t1;
2 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 ;
3 IF(0,"ERROR","this") IF(1,"is","ERROR") IF(NULL,"ERROR","a") IF(1,2,3)|0 IF(1,2.0,3.0)+0
4 this is a 2 2.0
5 CREATE TABLE t1 (st varchar(255) NOT NULL, u int(11) NOT NULL) ENGINE=MyISAM;
6 INSERT INTO t1 VALUES ('a',1),('A',1),('aa',1),('AA',1),('a',1),('aaa',0),('BBB',0);
7 select if(1,st,st) s from t1 order by s;
8 s
9 a
10 A
11 a
12 aa
13 AA
14 aaa
15 BBB
16 select if(u=1,st,st) s from t1 order by s;
17 s
18 a
19 A
20 a
21 aa
22 AA
23 aaa
24 BBB
25 select if(u=1,binary st,st) s from t1 order by s;
26 s
27 A
28 AA
29 BBB
30 a
31 a
32 aa
33 aaa
34 select if(u=1,st,binary st) s from t1 where st like "%a%" order by s;
35 s
36 A
37 AA
38 a
39 a
40 aa
41 aaa
42 explain extended select if(u=1,st,binary st) s from t1 where st like "%a%" order by s;
43 id select_type table type possible_keys key key_len ref rows filtered Extra
44 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00 Using where; Using filesort
45 Warnings:
46 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))
47 select nullif(u, 1) from t1;
48 nullif(u, 1)
49 NULL
50 NULL
51 NULL
52 NULL
53 NULL
54 0
55 0
56 explain extended select nullif(u, 1) from t1;
57 id select_type table type possible_keys key key_len ref rows filtered Extra
58 1 SIMPLE t1 ALL NULL NULL NULL NULL 7 100.00
59 Warnings:
60 Note 1003 select nullif(`test`.`t1`.`u`,1) AS `nullif(u, 1)` from `test`.`t1`
61 drop table t1;
62 select nullif(1,'test');
63 nullif(1,'test')
64 1
65 Warnings:
66 Warning 1292 Truncated incorrect DOUBLE value: 'test'
67 select NULLIF(NULL,NULL), NULLIF(NULL,1), NULLIF(NULL,1.0), NULLIF(NULL,"test");
68 NULLIF(NULL,NULL) NULLIF(NULL,1) NULLIF(NULL,1.0) NULLIF(NULL,"test")
69 NULL NULL NULL NULL
70 select NULLIF(1,NULL), NULLIF(1.0, NULL), NULLIF("test", NULL);
71 NULLIF(1,NULL) NULLIF(1.0, NULL) NULLIF("test", NULL)
72 1 1.0 test
73 create table t1 (num double(12,2));
74 insert into t1 values (144.54);
75 select sum(if(num is null,0.00,num)) from t1;
76 sum(if(num is null,0.00,num))
77 144.54
78 drop table t1;
79 create table t1 (x int, y int);
80 insert into t1 values (0,6),(10,16),(20,26),(30,10),(40,46),(50,56);
81 select min(if(y -x > 5,y,NULL)), max(if(y - x > 5,y,NULL)) from t1;
82 min(if(y -x > 5,y,NULL)) max(if(y - x > 5,y,NULL))
83 6 56
84 drop table t1;
85 create table t1 (a int);
86 insert t1 values (1),(2);
87 select if(1>2,a,avg(a)) from t1;
88 if(1>2,a,avg(a))
89 1.5000
90 drop table t1;
91 SELECT NULLIF(5,5) IS NULL, NULLIF(5,5) IS NOT NULL;
92 NULLIF(5,5) IS NULL NULLIF(5,5) IS NOT NULL
93 1 0
94 CREATE TABLE `t1` (
95 `id` int(11) NOT NULL ,
96 `date` int(10) default NULL,
97 `text` varchar(32) NOT NULL
98 );
99 INSERT INTO t1 VALUES (1,1110000000,'Day 1'),(2,1111000000,'Day 2'),(3,1112000000,'Day 3');
100 SELECT id, IF(date IS NULL, '-', FROM_UNIXTIME(date, '%d-%m-%Y')) AS date_ord, text FROM t1 ORDER BY date_ord ASC;
101 id date_ord text
102 1 05-03-2005 Day 1
103 2 16-03-2005 Day 2
104 3 28-03-2005 Day 3
105 SELECT id, IF(date IS NULL, '-', FROM_UNIXTIME(date, '%d-%m-%Y')) AS date_ord, text FROM t1 ORDER BY date_ord DESC;
106 id date_ord text
107 3 28-03-2005 Day 3
108 2 16-03-2005 Day 2
109 1 05-03-2005 Day 1
110 DROP TABLE t1;
111 CREATE TABLE t1 (a CHAR(10));
112 INSERT INTO t1 VALUES ('aaa'), (NULL), (''), ('bbb');
113 SELECT a, NULLIF(a,'') FROM t1;
114 a NULLIF(a,'')
115 aaa aaa
116 NULL NULL
117 NULL
118 bbb bbb
119 SELECT a, NULLIF(a,'') FROM t1 WHERE NULLIF(a,'') IS NULL;
120 a NULLIF(a,'')
121 NULL NULL
122 NULL
123 DROP TABLE t1;
124 create table t1 (f1 int, f2 int);
125 insert into t1 values(1,1),(0,0);
126 select f1, f2, if(f1, 40.0, 5.00) from t1 group by f1 order by f2;
127 f1 f2 if(f1, 40.0, 5.00)
128 0 0 5.00
129 1 1 40.00
130 drop table t1;
131 select if(0, 18446744073709551610, 18446744073709551610);
132 if(0, 18446744073709551610, 18446744073709551610)
133 18446744073709551610
134 CREATE TABLE t1(a DECIMAL(10,3));
135 SELECT t1.a,
136 IF((ROUND(t1.a,2)=1), 2,
137 IF((ROUND(t1.a,2)=1), 2,
138 IF((ROUND(t1.a,2)=1), 2,
139 IF((ROUND(t1.a,2)=1), 2,
140 IF((ROUND(t1.a,2)=1), 2,
141 IF((ROUND(t1.a,2)=1), 2,
142 IF((ROUND(t1.a,2)=1), 2,
143 IF((ROUND(t1.a,2)=1), 2,
144 IF((ROUND(t1.a,2)=1), 2,
145 IF((ROUND(t1.a,2)=1), 2,
146 IF((ROUND(t1.a,2)=1), 2,
147 IF((ROUND(t1.a,2)=1), 2,
148 IF((ROUND(t1.a,2)=1), 2,
149 IF((ROUND(t1.a,2)=1), 2,
150 IF((ROUND(t1.a,2)=1), 2,
151 IF((ROUND(t1.a,2)=1), 2,
152 IF((ROUND(t1.a,2)=1), 2,
153 IF((ROUND(t1.a,2)=1), 2,
154 IF((ROUND(t1.a,2)=1), 2,
155 IF((ROUND(t1.a,2)=1), 2,
156 IF((ROUND(t1.a,2)=1), 2,
157 IF((ROUND(t1.a,2)=1), 2,
158 IF((ROUND(t1.a,2)=1), 2,
159 IF((ROUND(t1.a,2)=1), 2,
160 IF((ROUND(t1.a,2)=1), 2,
161 IF((ROUND(t1.a,2)=1), 2,
162 IF((ROUND(t1.a,2)=1), 2,
163 IF((ROUND(t1.a,2)=1), 2,
164 IF((ROUND(t1.a,2)=1), 2,
165 IF((ROUND(t1.a,2)=1), 2,0)))))))))))))))))))))))))))))) + 1
166 FROM t1;
167 a IF((ROUND(t1.a,2)=1), 2,
168 IF((ROUND(t1.a,2)=1), 2,
169 IF((ROUND(t1.a,2)=1), 2,
170 IF((ROUND(t1.a,2)=1), 2,
171 IF((ROUND(t1.a,2)=1), 2,
172 IF((ROUND(t1.a,2)=1), 2,
173 IF((ROUND(t1.a,2)=1), 2,
174 IF((ROUND(t1.a,2)=1), 2,
175 IF((ROUND(t1.a,2)=1), 2,
176 IF((ROUND(t1.a,2)=1), 2,
177 IF((R
178 DROP TABLE t1;
179 CREATE TABLE t1 (c LONGTEXT);
180 INSERT INTO t1 VALUES(1), (2), (3), (4), ('12345678901234567890');
181 SELECT * FROM (SELECT MAX(IF(1, CAST(c AS UNSIGNED), 0)) FROM t1) AS te;
182 MAX(IF(1, CAST(c AS UNSIGNED), 0))
183 12345678901234567890
184 SELECT * FROM (SELECT MAX(IFNULL(CAST(c AS UNSIGNED), 0)) FROM t1) AS te;
185 MAX(IFNULL(CAST(c AS UNSIGNED), 0))
186 12345678901234567890
187 DROP TABLE t1;
188 End of 5.0 tests