|
1
by brian
clean slate |
1 |
#
|
2 |
# Init section
|
|
3 |
#
|
|
4 |
--disable_warnings |
|
5 |
drop table if exists t1; |
|
6 |
--enable_warnings |
|
7 |
||
8 |
#
|
|
9 |
# Simple IF tests
|
|
10 |
#
|
|
11 |
||
12 |
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 ; |
|
13 |
||
14 |
#
|
|
15 |
# Test of IF and case-sensitiveness
|
|
16 |
#
|
|
17 |
CREATE TABLE t1 (st varchar(255) NOT NULL, u int(11) NOT NULL) ENGINE=MyISAM; |
|
18 |
INSERT INTO t1 VALUES ('a',1),('A',1),('aa',1),('AA',1),('a',1),('aaa',0),('BBB',0); |
|
19 |
select if(1,st,st) s from t1 order by s; |
|
20 |
select if(u=1,st,st) s from t1 order by s; |
|
21 |
select if(u=1,binary st,st) s from t1 order by s; |
|
22 |
select if(u=1,st,binary st) s from t1 where st like "%a%" order by s; |
|
23 |
explain extended select if(u=1,st,binary st) s from t1 where st like "%a%" order by s; |
|
24 |
||
25 |
#
|
|
26 |
# NULLIF test
|
|
27 |
#
|
|
28 |
select nullif(u, 1) from t1; |
|
29 |
explain extended select nullif(u, 1) from t1; |
|
30 |
drop table t1; |
|
31 |
select nullif(1,'test'); |
|
32 |
||
33 |
#
|
|
34 |
# Bug 2629
|
|
35 |
#
|
|
36 |
select NULLIF(NULL,NULL), NULLIF(NULL,1), NULLIF(NULL,1.0), NULLIF(NULL,"test"); |
|
37 |
select NULLIF(1,NULL), NULLIF(1.0, NULL), NULLIF("test", NULL); |
|
38 |
||
39 |
#
|
|
40 |
# Problem with IF()
|
|
41 |
#
|
|
42 |
||
43 |
create table t1 (num double(12,2)); |
|
44 |
insert into t1 values (144.54); |
|
45 |
select sum(if(num is null,0.00,num)) from t1; |
|
46 |
drop table t1; |
|
47 |
create table t1 (x int, y int); |
|
48 |
insert into t1 values (0,6),(10,16),(20,26),(30,10),(40,46),(50,56); |
|
49 |
select min(if(y -x > 5,y,NULL)), max(if(y - x > 5,y,NULL)) from t1; |
|
50 |
drop table t1; |
|
51 |
||
52 |
#
|
|
53 |
# BUG#3987
|
|
54 |
#
|
|
55 |
create table t1 (a int); |
|
56 |
insert t1 values (1),(2); |
|
57 |
select if(1>2,a,avg(a)) from t1; |
|
58 |
drop table t1; |
|
59 |
||
60 |
#
|
|
61 |
# Bug #5595 NULLIF() IS NULL returns false if NULLIF() returns NULL
|
|
62 |
#
|
|
63 |
SELECT NULLIF(5,5) IS NULL, NULLIF(5,5) IS NOT NULL; |
|
64 |
||
65 |
#
|
|
66 |
# Bug #9669 Ordering on IF function with FROM_UNIXTIME function fails
|
|
67 |
#
|
|
68 |
CREATE TABLE `t1` ( |
|
69 |
`id` int(11) NOT NULL , |
|
70 |
`date` int(10) default NULL, |
|
71 |
`text` varchar(32) NOT NULL |
|
72 |
);
|
|
73 |
INSERT INTO t1 VALUES (1,1110000000,'Day 1'),(2,1111000000,'Day 2'),(3,1112000000,'Day 3'); |
|
74 |
SELECT id, IF(date IS NULL, '-', FROM_UNIXTIME(date, '%d-%m-%Y')) AS date_ord, text FROM t1 ORDER BY date_ord ASC; |
|
75 |
SELECT id, IF(date IS NULL, '-', FROM_UNIXTIME(date, '%d-%m-%Y')) AS date_ord, text FROM t1 ORDER BY date_ord DESC; |
|
76 |
DROP TABLE t1; |
|
77 |
||
78 |
||
79 |
#
|
|
80 |
# Test for bug #11142: evaluation of NULLIF when the first argument is NULL
|
|
81 |
#
|
|
82 |
||
83 |
CREATE TABLE t1 (a CHAR(10)); |
|
84 |
INSERT INTO t1 VALUES ('aaa'), (NULL), (''), ('bbb'); |
|
85 |
||
86 |
SELECT a, NULLIF(a,'') FROM t1; |
|
87 |
SELECT a, NULLIF(a,'') FROM t1 WHERE NULLIF(a,'') IS NULL; |
|
88 |
||
89 |
DROP TABLE t1; |
|
90 |
||
91 |
# End of 4.1 tests
|
|
92 |
||
93 |
#
|
|
94 |
# Bug #16272 IF function with decimal args can produce wrong result
|
|
95 |
#
|
|
96 |
create table t1 (f1 int, f2 int); |
|
97 |
insert into t1 values(1,1),(0,0); |
|
98 |
select f1, f2, if(f1, 40.0, 5.00) from t1 group by f1 order by f2; |
|
99 |
drop table t1; |
|
100 |
||
101 |
#
|
|
102 |
# Bug#24532 (The return data type of IS TRUE is different from similar
|
|
103 |
# operations)
|
|
104 |
#
|
|
105 |
# IF(x, unsigned, unsigned) should be unsigned.
|
|
106 |
#
|
|
107 |
||
108 |
select if(0, 18446744073709551610, 18446744073709551610); |
|
109 |
||
110 |