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
|
# Initialise
--disable_warnings
drop table if exists t1;
--enable_warnings
#
# Testing of NULL in a lot of different places
#
select null,\N,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null;
explain extended select null,\N,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null;
select 1 | NULL,1 & NULL,1+NULL,1-NULL;
select NULL=NULL,NULL<>NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0;
select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null;
select concat("a",NULL),replace(NULL,"a","b"),replace("string","i",NULL),replace("string",NULL,"i"),insert("abc",1,1,NULL),left(NULL,1);
select repeat("a",0),repeat("ab",5+5),repeat("ab",-1),reverse(NULL);
select field(NULL,"a","b","c");
select 2 between null and 1,2 between 3 AND NULL,NULL between 1 and 2,2 between NULL and 3, 2 between 1 AND null;
explain extended select 2 between null and 1,2 between 3 AND NULL,NULL between 1 and 2,2 between NULL and 3, 2 between 1 AND null;
SELECT NULL AND NULL, 1 AND NULL, NULL AND 1, NULL OR NULL, 0 OR NULL, NULL OR 0;
SELECT (NULL OR NULL) IS NULL;
select NULL AND 0, 0 and NULL;
select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton("");
explain extended select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton("");
create table t1 (x int);
insert into t1 values (null);
select * from t1 where x != 0;
drop table t1;
#
# Test problem med index on NULL columns and testing with =NULL;
#
CREATE TABLE t1 (
indexed_field int default NULL,
KEY indexed_field (indexed_field)
);
INSERT INTO t1 VALUES (NULL),(NULL);
SELECT * FROM t1 WHERE indexed_field=NULL;
SELECT * FROM t1 WHERE indexed_field IS NULL;
SELECT * FROM t1 WHERE indexed_field<=>NULL;
DROP TABLE t1;
#
# Testing of IFNULL
#
create table t1 (a int, b int) engine=myisam;
insert into t1 values(20,null);
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
t2.b=t3.a;
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
t2.b=t3.a order by 1;
insert into t1 values(10,null);
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
t2.b=t3.a order by 1;
drop table t1;
#
# Test inserting and updating with NULL
#
CREATE TABLE t1 (a varchar(16) NOT NULL default '', b smallint(6) NOT NULL default 0, c datetime NOT NULL default '0000-00-00 00:00:00', d smallint(6) NOT NULL default 0);
INSERT INTO t1 SET a = "", d= "2003-01-14 03:54:55";
UPDATE t1 SET d=1/NULL;
UPDATE t1 SET d=NULL;
--error 1048
INSERT INTO t1 (a) values (null);
--error 1048
INSERT INTO t1 (a) values (1/null);
INSERT INTO t1 (a) values (null),(null);
--error 1048
INSERT INTO t1 (b) values (null);
--error 1048
INSERT INTO t1 (b) values (1/null);
INSERT INTO t1 (b) values (null),(null);
--error 1048
INSERT INTO t1 (c) values (null);
--error 1048
INSERT INTO t1 (c) values (1/null);
INSERT INTO t1 (c) values (null),(null);
--error 1048
INSERT INTO t1 (d) values (null);
--error 1048
INSERT INTO t1 (d) values (1/null);
INSERT INTO t1 (d) values (null),(null);
select * from t1;
drop table t1;
#
# Test to check elimination of IS NULL predicate for a non-nullable attribute
# (bug #1990)
#
create table t1 (a int not null, b int not null, index idx(a));
insert into t1 values
(1,1), (2,2), (3,3), (4,4), (5,5), (6,6),
(7,7), (8,8), (9,9), (10,10), (11,11), (12,12);
explain select * from t1 where a between 2 and 3;
explain select * from t1 where a between 2 and 3 or b is null;
drop table t1;
select cast(NULL as signed);
#
# IS NULL is unable to use index in range if column is declared not null
# (Bug #4256)
#
create table t1(i int, key(i));
insert into t1 values(1);
insert into t1 select i*2 from t1;
insert into t1 select i*2 from t1;
insert into t1 select i*2 from t1;
insert into t1 select i*2 from t1;
insert into t1 select i*2 from t1;
insert into t1 select i*2 from t1;
insert into t1 select i*2 from t1;
insert into t1 select i*2 from t1;
insert into t1 select i*2 from t1;
insert into t1 values(null);
explain select * from t1 where i=2 or i is null;
select count(*) from t1 where i=2 or i is null;
alter table t1 change i i int not null;
explain select * from t1 where i=2 or i is null;
select count(*) from t1 where i=2 or i is null;
drop table t1;
#
# NULL has its own type BINARY(0) by default.
# But NULL should be weaker than a constant
# when mixing charsets/collations
#
set names latin2;
# Check that result type is taken from a non-null string
create table t1 select
null as c00,
if(1, null, 'string') as c01,
if(0, null, 'string') as c02,
ifnull(null, 'string') as c03,
ifnull('string', null) as c04,
case when 0 then null else 'string' end as c05,
case when 1 then null else 'string' end as c06,
coalesce(null, 'string') as c07,
coalesce('string', null) as c08,
least('string',null) as c09,
least(null, 'string') as c10,
greatest('string',null) as c11,
greatest(null, 'string') as c12,
nullif('string', null) as c13,
nullif(null, 'string') as c14,
trim('string' from null) as c15,
trim(null from 'string') as c16,
substring_index('string', null, 1) as c17,
substring_index(null, 'string', 1) as c18,
elt(1, null, 'string') as c19,
elt(1, 'string', null) as c20,
concat('string', null) as c21,
concat(null, 'string') as c22,
concat_ws('sep', 'string', null) as c23,
concat_ws('sep', null, 'string') as c24,
concat_ws(null, 'string', 'string') as c25,
make_set(3, 'string', null) as c26,
make_set(3, null, 'string') as c27,
export_set(3, null, 'off', 'sep') as c29,
export_set(3, 'on', null, 'sep') as c30,
export_set(3, 'on', 'off', null) as c31,
replace(null, 'from', 'to') as c32,
replace('str', null, 'to') as c33,
replace('str', 'from', null) as c34,
insert('str', 1, 2, null) as c35,
insert(null, 1, 2, 'str') as c36,
lpad('str', 10, null) as c37,
rpad(null, 10, 'str') as c38;
show create table t1;
drop table t1;
#
# Check that comparison is done according to
# non-null string collation, i.e. case insensitively,
# rather than according to NULL's collation, i.e. case sensitively
#
-- in field
select
case 'str' when 'STR' then 'str' when null then 'null' end as c01,
case 'str' when null then 'null' when 'STR' then 'str' end as c02,
field(null, 'str1', 'str2') as c03,
field('str1','STR1', null) as c04,
field('str1', null, 'STR1') as c05,
'string' in ('STRING', null) as c08,
'string' in (null, 'STRING') as c09;
# Restore charset to the default value.
set names latin1;
# End of 4.1 tests
|
