3
drop table if exists t1, t2;
7
# Testing of NULL in a lot of different places
10
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;
11
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;
12
select 1 | NULL,1 & NULL,1+NULL,1-NULL;
13
select NULL=NULL,NULL<>NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0;
14
select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null;
15
select concat("a",NULL),replace(NULL,"a","b"),replace("string","i",NULL),replace("string",NULL,"i"),insert("abc",1,1,NULL),left(NULL,1);
16
select repeat("a",0),repeat("ab",5+5),repeat("ab",-1),reverse(NULL);
17
select field(NULL,"a","b","c");
18
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;
19
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;
20
SELECT NULL AND NULL, 1 AND NULL, NULL AND 1, NULL OR NULL, 0 OR NULL, NULL OR 0;
21
SELECT (NULL OR NULL) IS NULL;
22
select NULL AND 0, 0 and NULL;
23
select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton("");
24
explain extended select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton("");
26
create table t1 (x int);
27
insert into t1 values (null);
28
select * from t1 where x != 0;
32
# Test problem med index on NULL columns and testing with =NULL;
36
indexed_field int default NULL,
37
KEY indexed_field (indexed_field)
39
INSERT INTO t1 VALUES (NULL),(NULL);
40
SELECT * FROM t1 WHERE indexed_field=NULL;
41
SELECT * FROM t1 WHERE indexed_field IS NULL;
42
SELECT * FROM t1 WHERE indexed_field<=>NULL;
48
create table t1 (a int, b int) engine=myisam;
49
insert into t1 values(20,null);
50
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
52
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
54
insert into t1 values(10,null);
55
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
60
# Test inserting and updating with NULL
62
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);
63
INSERT INTO t1 SET a = "", d= "2003-01-14 03:54:55";
65
UPDATE t1 SET d=1/NULL;
69
INSERT INTO t1 (a) values (null);
71
INSERT INTO t1 (a) values (1/null);
72
INSERT INTO t1 (a) values (null),(null);
74
INSERT INTO t1 (b) values (null);
76
INSERT INTO t1 (b) values (1/null);
77
INSERT INTO t1 (b) values (null),(null);
79
INSERT INTO t1 (c) values (null);
81
INSERT INTO t1 (c) values (1/null);
82
INSERT INTO t1 (c) values (null),(null);
84
INSERT INTO t1 (d) values (null);
86
INSERT INTO t1 (d) values (1/null);
87
INSERT INTO t1 (d) values (null),(null);
92
# Test to check elimination of IS NULL predicate for a non-nullable attribute
95
create table t1 (a int not null, b int not null, index idx(a));
97
(1,1), (2,2), (3,3), (4,4), (5,5), (6,6),
98
(7,7), (8,8), (9,9), (10,10), (11,11), (12,12);
99
explain select * from t1 where a between 2 and 3;
100
explain select * from t1 where a between 2 and 3 or b is null;
102
select cast(NULL as signed);
105
# IS NULL is unable to use index in range if column is declared not null
108
create table t1(i int, key(i));
109
insert into t1 values(1);
110
insert into t1 select i*2 from t1;
111
insert into t1 select i*2 from t1;
112
insert into t1 select i*2 from t1;
113
insert into t1 select i*2 from t1;
114
insert into t1 select i*2 from t1;
115
insert into t1 select i*2 from t1;
116
insert into t1 select i*2 from t1;
117
insert into t1 select i*2 from t1;
118
insert into t1 select i*2 from t1;
119
insert into t1 values(null);
120
explain select * from t1 where i=2 or i is null;
121
select count(*) from t1 where i=2 or i is null;
122
alter table t1 change i i int not null;
123
explain select * from t1 where i=2 or i is null;
124
select count(*) from t1 where i=2 or i is null;
128
# NULL has its own type BINARY(0) by default.
129
# But NULL should be weaker than a constant
130
# when mixing charsets/collations
133
# Check that result type is taken from a non-null string
134
create table t1 select
136
if(1, null, 'string') as c01,
137
if(0, null, 'string') as c02,
138
ifnull(null, 'string') as c03,
139
ifnull('string', null) as c04,
140
case when 0 then null else 'string' end as c05,
141
case when 1 then null else 'string' end as c06,
142
coalesce(null, 'string') as c07,
143
coalesce('string', null) as c08,
144
least('string',null) as c09,
145
least(null, 'string') as c10,
146
greatest('string',null) as c11,
147
greatest(null, 'string') as c12,
148
nullif('string', null) as c13,
149
nullif(null, 'string') as c14,
150
trim('string' from null) as c15,
151
trim(null from 'string') as c16,
152
substring_index('string', null, 1) as c17,
153
substring_index(null, 'string', 1) as c18,
154
elt(1, null, 'string') as c19,
155
elt(1, 'string', null) as c20,
156
concat('string', null) as c21,
157
concat(null, 'string') as c22,
158
concat_ws('sep', 'string', null) as c23,
159
concat_ws('sep', null, 'string') as c24,
160
concat_ws(null, 'string', 'string') as c25,
161
make_set(3, 'string', null) as c26,
162
make_set(3, null, 'string') as c27,
163
export_set(3, null, 'off', 'sep') as c29,
164
export_set(3, 'on', null, 'sep') as c30,
165
export_set(3, 'on', 'off', null) as c31,
166
replace(null, 'from', 'to') as c32,
167
replace('str', null, 'to') as c33,
168
replace('str', 'from', null) as c34,
169
insert('str', 1, 2, null) as c35,
170
insert(null, 1, 2, 'str') as c36,
171
lpad('str', 10, null) as c37,
172
rpad(null, 10, 'str') as c38;
174
show create table t1;
178
# Check that comparison is done according to
179
# non-null string collation, i.e. case insensitively,
180
# rather than according to NULL's collation, i.e. case sensitively
184
case 'str' when 'STR' then 'str' when null then 'null' end as c01,
185
case 'str' when null then 'null' when 'STR' then 'str' end as c02,
186
field(null, 'str1', 'str2') as c03,
187
field('str1','STR1', null) as c04,
188
field('str1', null, 'STR1') as c05,
189
'string' in ('STRING', null) as c08,
190
'string' in (null, 'STRING') as c09;
192
# Restore charset to the default value.
196
# Bug#19145: mysqld crashes if you set the default value of an enum field to NULL
198
create table bug19145a (e enum('a','b','c') default 'b' , s set('x', 'y', 'z') default 'y' ) engine=MyISAM;
199
create table bug19145b (e enum('a','b','c') default null, s set('x', 'y', 'z') default null) engine=MyISAM;
201
create table bug19145c (e enum('a','b','c') not null default 'b' , s set('x', 'y', 'z') not null default 'y' ) engine=MyISAM;
203
# Invalid default value for 's'
205
create table bug19145setnotnulldefaultnull (e enum('a','b','c') default null, s set('x', 'y', 'z') not null default null) engine=MyISAM;
207
# Invalid default value for 'e'
209
create table bug19145enumnotnulldefaultnull (e enum('a','b','c') not null default null, s set('x', 'y', 'z') default null) engine=MyISAM;
211
alter table bug19145a alter column e set default null;
212
alter table bug19145a alter column s set default null;
213
alter table bug19145a add column (i int);
215
alter table bug19145b alter column e set default null;
216
alter table bug19145b alter column s set default null;
217
alter table bug19145b add column (i int);
219
# Invalid default value for 'e'
221
alter table bug19145c alter column e set default null;
223
# Invalid default value for 's'
225
alter table bug19145c alter column s set default null;
226
alter table bug19145c add column (i int);
228
show create table bug19145a;
229
show create table bug19145b;
230
show create table bug19145c;
232
drop table bug19145a;
233
drop table bug19145b;
234
drop table bug19145c;
236
--echo # End of 4.1 tests
239
--echo # Bug #31471: decimal_bin_size: Assertion `scale >= 0 &&
240
--echo # precision > 0 && scale <= precision'
243
CREATE TABLE t1 (a DECIMAL (1, 0) ZEROFILL, b DECIMAL (1, 0) ZEROFILL);
244
INSERT INTO t1 (a, b) VALUES (0, 0);
246
CREATE TABLE t2 SELECT IFNULL(a, b) FROM t1;
250
CREATE TABLE t2 SELECT IFNULL(a, NULL) FROM t1;
254
CREATE TABLE t2 SELECT IFNULL(NULL, b) FROM t1;
259
--echo # End of 5.0 tests
added
removed
mysql-test/t/null.test
