|
1
by brian
clean slate |
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
|