1
drop table if exists t1, t2;
2
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;
3
NULL NULL 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
4
NULL NULL 1 1 1 1 TRUE TRUE 1 1
5
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;
6
id select_type table type possible_keys key key_len ref rows filtered Extra
7
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
9
Note 1003 select NULL AS `NULL`,NULL AS `NULL`,isnull(NULL) AS `isnull(null)`,isnull((1 / 0)) AS `isnull(1/0)`,isnull(((1 / 0) = NULL)) AS `isnull(1/0 = null)`,ifnull(NULL,1) AS `ifnull(null,1)`,ifnull(NULL,'TRUE') AS `ifnull(null,"TRUE")`,ifnull('TRUE','ERROR') AS `ifnull("TRUE","ERROR")`,isnull((1 / 0)) AS `1/0 is null`,(1 is not null) AS `1 is not null`
10
select 1 | NULL,1 & NULL,1+NULL,1-NULL;
11
1 | NULL 1 & NULL 1+NULL 1-NULL
13
select NULL=NULL,NULL<>NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0;
14
NULL=NULL NULL<>NULL IFNULL(NULL,1.1)+0 IFNULL(NULL,1) | 0
16
select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null;
17
strcmp("a",NULL) (1<NULL)+0.0 NULL regexp "a" null like "a%" "a%" like null
18
NULL NULL NULL NULL NULL
19
select concat("a",NULL),replace(NULL,"a","b"),replace("string","i",NULL),replace("string",NULL,"i"),insert("abc",1,1,NULL),left(NULL,1);
20
concat("a",NULL) replace(NULL,"a","b") replace("string","i",NULL) replace("string",NULL,"i") insert("abc",1,1,NULL) left(NULL,1)
21
NULL NULL NULL NULL NULL NULL
22
select repeat("a",0),repeat("ab",5+5),repeat("ab",-1),reverse(NULL);
23
repeat("a",0) repeat("ab",5+5) repeat("ab",-1) reverse(NULL)
24
abababababababababab NULL
25
select field(NULL,"a","b","c");
26
field(NULL,"a","b","c")
28
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;
29
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
31
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;
32
id select_type table type possible_keys key key_len ref rows filtered Extra
33
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
35
Note 1003 select (2 between NULL and 1) AS `2 between null and 1`,(2 between 3 and NULL) AS `2 between 3 AND NULL`,(NULL between 1 and 2) AS `NULL between 1 and 2`,(2 between NULL and 3) AS `2 between NULL and 3`,(2 between 1 and NULL) AS `2 between 1 AND null`
36
SELECT NULL AND NULL, 1 AND NULL, NULL AND 1, NULL OR NULL, 0 OR NULL, NULL OR 0;
37
NULL AND NULL 1 AND NULL NULL AND 1 NULL OR NULL 0 OR NULL NULL OR 0
38
NULL NULL NULL NULL NULL NULL
39
SELECT (NULL OR NULL) IS NULL;
40
(NULL OR NULL) IS NULL
42
select NULL AND 0, 0 and NULL;
45
select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton("");
46
inet_ntoa(null) inet_aton(null) inet_aton("122.256") inet_aton("122.226.") inet_aton("")
47
NULL NULL NULL NULL NULL
48
explain extended select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton("");
49
id select_type table type possible_keys key key_len ref rows filtered Extra
50
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
52
Note 1003 select inet_ntoa(NULL) AS `inet_ntoa(null)`,inet_aton(NULL) AS `inet_aton(null)`,inet_aton('122.256') AS `inet_aton("122.256")`,inet_aton('122.226.') AS `inet_aton("122.226.")`,inet_aton('') AS `inet_aton("")`
53
create table t1 (x int);
54
insert into t1 values (null);
55
select * from t1 where x != 0;
59
indexed_field int default NULL,
60
KEY indexed_field (indexed_field)
62
INSERT INTO t1 VALUES (NULL),(NULL);
63
SELECT * FROM t1 WHERE indexed_field=NULL;
65
SELECT * FROM t1 WHERE indexed_field IS NULL;
69
SELECT * FROM t1 WHERE indexed_field<=>NULL;
74
create table t1 (a int, b int) engine=myisam;
75
insert into t1 values(20,null);
76
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
78
b ifnull(t2.b,"this is null")
80
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
82
b ifnull(t2.b,"this is null")
84
insert into t1 values(10,null);
85
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
87
b ifnull(t2.b,"this is null")
91
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);
92
INSERT INTO t1 SET a = "", d= "2003-01-14 03:54:55";
94
Warning 1265 Data truncated for column 'd' at row 1
95
UPDATE t1 SET d=1/NULL;
96
ERROR 23000: Column 'd' cannot be null
98
ERROR 23000: Column 'd' cannot be null
99
INSERT INTO t1 (a) values (null);
100
ERROR 23000: Column 'a' cannot be null
101
INSERT INTO t1 (a) values (1/null);
102
ERROR 23000: Column 'a' cannot be null
103
INSERT INTO t1 (a) values (null),(null);
105
Warning 1048 Column 'a' cannot be null
106
Warning 1048 Column 'a' cannot be null
107
INSERT INTO t1 (b) values (null);
108
ERROR 23000: Column 'b' cannot be null
109
INSERT INTO t1 (b) values (1/null);
110
ERROR 23000: Column 'b' cannot be null
111
INSERT INTO t1 (b) values (null),(null);
113
Warning 1048 Column 'b' cannot be null
114
Warning 1048 Column 'b' cannot be null
115
INSERT INTO t1 (c) values (null);
116
ERROR 23000: Column 'c' cannot be null
117
INSERT INTO t1 (c) values (1/null);
118
ERROR 23000: Column 'c' cannot be null
119
INSERT INTO t1 (c) values (null),(null);
121
Warning 1048 Column 'c' cannot be null
122
Warning 1048 Column 'c' cannot be null
123
INSERT INTO t1 (d) values (null);
124
ERROR 23000: Column 'd' cannot be null
125
INSERT INTO t1 (d) values (1/null);
126
ERROR 23000: Column 'd' cannot be null
127
INSERT INTO t1 (d) values (null),(null);
129
Warning 1048 Column 'd' cannot be null
130
Warning 1048 Column 'd' cannot be null
133
0 0000-00-00 00:00:00 2003
134
0 0000-00-00 00:00:00 0
135
0 0000-00-00 00:00:00 0
136
0 0000-00-00 00:00:00 0
137
0 0000-00-00 00:00:00 0
138
0 0000-00-00 00:00:00 0
139
0 0000-00-00 00:00:00 0
140
0 0000-00-00 00:00:00 0
141
0 0000-00-00 00:00:00 0
143
create table t1 (a int not null, b int not null, index idx(a));
144
insert into t1 values
145
(1,1), (2,2), (3,3), (4,4), (5,5), (6,6),
146
(7,7), (8,8), (9,9), (10,10), (11,11), (12,12);
147
explain select * from t1 where a between 2 and 3;
148
id select_type table type possible_keys key key_len ref rows Extra
149
1 SIMPLE t1 range idx idx 4 NULL 2 Using index condition; Using MRR
150
explain select * from t1 where a between 2 and 3 or b is null;
151
id select_type table type possible_keys key key_len ref rows Extra
152
1 SIMPLE t1 range idx idx 4 NULL 2 Using index condition; Using MRR
154
select cast(NULL as signed);
157
create table t1(i int, key(i));
158
insert into t1 values(1);
159
insert into t1 select i*2 from t1;
160
insert into t1 select i*2 from t1;
161
insert into t1 select i*2 from t1;
162
insert into t1 select i*2 from t1;
163
insert into t1 select i*2 from t1;
164
insert into t1 select i*2 from t1;
165
insert into t1 select i*2 from t1;
166
insert into t1 select i*2 from t1;
167
insert into t1 select i*2 from t1;
168
insert into t1 values(null);
169
explain select * from t1 where i=2 or i is null;
170
id select_type table type possible_keys key key_len ref rows Extra
171
1 SIMPLE t1 ref_or_null i i 5 const 9 Using index
172
select count(*) from t1 where i=2 or i is null;
175
alter table t1 change i i int not null;
177
Warning 1265 Data truncated for column 'i' at row 513
178
explain select * from t1 where i=2 or i is null;
179
id select_type table type possible_keys key key_len ref rows Extra
180
1 SIMPLE t1 ref i i 4 const 7 Using index
181
select count(*) from t1 where i=2 or i is null;
186
create table t1 select
188
if(1, null, 'string') as c01,
189
if(0, null, 'string') as c02,
190
ifnull(null, 'string') as c03,
191
ifnull('string', null) as c04,
192
case when 0 then null else 'string' end as c05,
193
case when 1 then null else 'string' end as c06,
194
coalesce(null, 'string') as c07,
195
coalesce('string', null) as c08,
196
least('string',null) as c09,
197
least(null, 'string') as c10,
198
greatest('string',null) as c11,
199
greatest(null, 'string') as c12,
200
nullif('string', null) as c13,
201
nullif(null, 'string') as c14,
202
trim('string' from null) as c15,
203
trim(null from 'string') as c16,
204
substring_index('string', null, 1) as c17,
205
substring_index(null, 'string', 1) as c18,
206
elt(1, null, 'string') as c19,
207
elt(1, 'string', null) as c20,
208
concat('string', null) as c21,
209
concat(null, 'string') as c22,
210
concat_ws('sep', 'string', null) as c23,
211
concat_ws('sep', null, 'string') as c24,
212
concat_ws(null, 'string', 'string') as c25,
213
make_set(3, 'string', null) as c26,
214
make_set(3, null, 'string') as c27,
215
export_set(3, null, 'off', 'sep') as c29,
216
export_set(3, 'on', null, 'sep') as c30,
217
export_set(3, 'on', 'off', null) as c31,
218
replace(null, 'from', 'to') as c32,
219
replace('str', null, 'to') as c33,
220
replace('str', 'from', null) as c34,
221
insert('str', 1, 2, null) as c35,
222
insert(null, 1, 2, 'str') as c36,
223
lpad('str', 10, null) as c37,
224
rpad(null, 10, 'str') as c38;
225
show create table t1;
227
t1 CREATE TABLE `t1` (
228
`c00` binary(0) DEFAULT NULL,
229
`c01` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
230
`c02` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
231
`c03` varchar(6) CHARACTER SET latin2 NOT NULL DEFAULT '',
232
`c04` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
233
`c05` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
234
`c06` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
235
`c07` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
236
`c08` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
237
`c09` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
238
`c10` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
239
`c11` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
240
`c12` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
241
`c13` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
242
`c14` char(0) CHARACTER SET latin2 DEFAULT NULL,
243
`c15` char(0) CHARACTER SET latin2 DEFAULT NULL,
244
`c16` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
245
`c17` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
246
`c18` char(0) CHARACTER SET latin2 DEFAULT NULL,
247
`c19` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
248
`c20` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
249
`c21` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
250
`c22` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
251
`c23` varchar(9) CHARACTER SET latin2 DEFAULT NULL,
252
`c24` varchar(9) CHARACTER SET latin2 DEFAULT NULL,
253
`c25` varchar(12) CHARACTER SET latin2 DEFAULT NULL,
254
`c26` varchar(7) CHARACTER SET latin2 DEFAULT NULL,
255
`c27` varchar(7) CHARACTER SET latin2 DEFAULT NULL,
256
`c29` varchar(381) CHARACTER SET latin2 DEFAULT NULL,
257
`c30` varchar(317) CHARACTER SET latin2 DEFAULT NULL,
258
`c31` varchar(192) CHARACTER SET latin2 DEFAULT NULL,
259
`c32` char(0) CHARACTER SET latin2 DEFAULT NULL,
260
`c33` varchar(3) CHARACTER SET latin2 DEFAULT NULL,
261
`c34` varchar(3) CHARACTER SET latin2 DEFAULT NULL,
262
`c35` varchar(3) CHARACTER SET latin2 DEFAULT NULL,
263
`c36` varchar(3) CHARACTER SET latin2 DEFAULT NULL,
264
`c37` varchar(10) CHARACTER SET latin2 DEFAULT NULL,
265
`c38` varchar(10) CHARACTER SET latin2 DEFAULT NULL
266
) ENGINE=MyISAM DEFAULT CHARSET=latin1
269
case 'str' when 'STR' then 'str' when null then 'null' end as c01,
270
case 'str' when null then 'null' when 'STR' then 'str' end as c02,
271
field(null, 'str1', 'str2') as c03,
272
field('str1','STR1', null) as c04,
273
field('str1', null, 'STR1') as c05,
274
'string' in ('STRING', null) as c08,
275
'string' in (null, 'STRING') as c09;
276
c01 c02 c03 c04 c05 c08 c09
279
create table bug19145a (e enum('a','b','c') default 'b' , s set('x', 'y', 'z') default 'y' ) engine=MyISAM;
280
create table bug19145b (e enum('a','b','c') default null, s set('x', 'y', 'z') default null) engine=MyISAM;
281
create table bug19145c (e enum('a','b','c') not null default 'b' , s set('x', 'y', 'z') not null default 'y' ) engine=MyISAM;
282
create table bug19145setnotnulldefaultnull (e enum('a','b','c') default null, s set('x', 'y', 'z') not null default null) engine=MyISAM;
283
ERROR 42000: Invalid default value for 's'
284
create table bug19145enumnotnulldefaultnull (e enum('a','b','c') not null default null, s set('x', 'y', 'z') default null) engine=MyISAM;
285
ERROR 42000: Invalid default value for 'e'
286
alter table bug19145a alter column e set default null;
287
alter table bug19145a alter column s set default null;
288
alter table bug19145a add column (i int);
289
alter table bug19145b alter column e set default null;
290
alter table bug19145b alter column s set default null;
291
alter table bug19145b add column (i int);
292
alter table bug19145c alter column e set default null;
293
ERROR 42000: Invalid default value for 'e'
294
alter table bug19145c alter column s set default null;
295
ERROR 42000: Invalid default value for 's'
296
alter table bug19145c add column (i int);
297
show create table bug19145a;
299
bug19145a CREATE TABLE `bug19145a` (
300
`e` enum('a','b','c') DEFAULT NULL,
301
`s` set('x','y','z') DEFAULT NULL,
302
`i` int(11) DEFAULT NULL
303
) ENGINE=MyISAM DEFAULT CHARSET=latin1
304
show create table bug19145b;
306
bug19145b CREATE TABLE `bug19145b` (
307
`e` enum('a','b','c') DEFAULT NULL,
308
`s` set('x','y','z') DEFAULT NULL,
309
`i` int(11) DEFAULT NULL
310
) ENGINE=MyISAM DEFAULT CHARSET=latin1
311
show create table bug19145c;
313
bug19145c CREATE TABLE `bug19145c` (
314
`e` enum('a','b','c') NOT NULL DEFAULT 'b',
315
`s` set('x','y','z') NOT NULL DEFAULT 'y',
316
`i` int(11) DEFAULT NULL
317
) ENGINE=MyISAM DEFAULT CHARSET=latin1
318
drop table bug19145a;
319
drop table bug19145b;
320
drop table bug19145c;
323
# Bug #31471: decimal_bin_size: Assertion `scale >= 0 &&
324
# precision > 0 && scale <= precision'
326
CREATE TABLE t1 (a DECIMAL (1, 0) ZEROFILL, b DECIMAL (1, 0) ZEROFILL);
327
INSERT INTO t1 (a, b) VALUES (0, 0);
328
CREATE TABLE t2 SELECT IFNULL(a, b) FROM t1;
330
Field Type Null Key Default Extra
331
IFNULL(a, b) decimal(1,0) unsigned YES NULL
333
CREATE TABLE t2 SELECT IFNULL(a, NULL) FROM t1;
335
Field Type Null Key Default Extra
336
IFNULL(a, NULL) decimal(1,0) YES NULL
338
CREATE TABLE t2 SELECT IFNULL(NULL, b) FROM t1;
340
Field Type Null Key Default Extra
341
IFNULL(NULL, b) decimal(1,0) YES NULL
added
removed
mysql-test/r/null.result
