~drizzle-trunk/drizzle/development

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