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;
97
Warning 1265 Data truncated for column 'd' at row 1
100
Warning 1048 Column 'd' cannot be null
101
INSERT INTO t1 (a) values (null);
102
ERROR 23000: Column 'a' cannot be null
103
INSERT INTO t1 (a) values (1/null);
104
ERROR 23000: Column 'a' cannot be null
105
INSERT INTO t1 (a) values (null),(null);
107
Warning 1048 Column 'a' cannot be null
108
Warning 1048 Column 'a' cannot be null
109
INSERT INTO t1 (b) values (null);
110
ERROR 23000: Column 'b' cannot be null
111
INSERT INTO t1 (b) values (1/null);
112
ERROR 23000: Column 'b' cannot be null
113
INSERT INTO t1 (b) values (null),(null);
115
Warning 1048 Column 'b' cannot be null
116
Warning 1048 Column 'b' cannot be null
117
INSERT INTO t1 (c) values (null);
118
ERROR 23000: Column 'c' cannot be null
119
INSERT INTO t1 (c) values (1/null);
120
ERROR 23000: Column 'c' cannot be null
121
INSERT INTO t1 (c) values (null),(null);
123
Warning 1048 Column 'c' cannot be null
124
Warning 1048 Column 'c' cannot be null
125
INSERT INTO t1 (d) values (null);
126
ERROR 23000: Column 'd' cannot be null
127
INSERT INTO t1 (d) values (1/null);
128
ERROR 23000: Column 'd' cannot be null
129
INSERT INTO t1 (d) values (null),(null);
131
Warning 1048 Column 'd' cannot be null
132
Warning 1048 Column 'd' cannot be null
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
142
0 0000-00-00 00:00:00 0
143
0 0000-00-00 00:00:00 0
145
create table t1 (a int not null, b int not null, index idx(a));
146
insert into t1 values
147
(1,1), (2,2), (3,3), (4,4), (5,5), (6,6),
148
(7,7), (8,8), (9,9), (10,10), (11,11), (12,12);
149
explain select * from t1 where a between 2 and 3;
150
id select_type table type possible_keys key key_len ref rows Extra
151
1 SIMPLE t1 range idx idx 4 NULL 2 Using where
152
explain select * from t1 where a between 2 and 3 or b is null;
153
id select_type table type possible_keys key key_len ref rows Extra
154
1 SIMPLE t1 range idx idx 4 NULL 2 Using where
156
select cast(NULL as signed);
159
create table t1(i int, key(i));
160
insert into t1 values(1);
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 select i*2 from t1;
169
insert into t1 select i*2 from t1;
170
insert into t1 values(null);
171
explain select * from t1 where i=2 or i is null;
172
id select_type table type possible_keys key key_len ref rows Extra
173
1 SIMPLE t1 ref_or_null i i 5 const 9 Using where; Using index
174
select count(*) from t1 where i=2 or i is null;
177
alter table t1 change i i int not null;
179
Warning 1265 Data truncated for column 'i' at row 513
180
explain select * from t1 where i=2 or i is null;
181
id select_type table type possible_keys key key_len ref rows Extra
182
1 SIMPLE t1 ref i i 4 const 7 Using index
183
select count(*) from t1 where i=2 or i is null;
188
create table t1 select
190
if(1, null, 'string') as c01,
191
if(0, null, 'string') as c02,
192
ifnull(null, 'string') as c03,
193
ifnull('string', null) as c04,
194
case when 0 then null else 'string' end as c05,
195
case when 1 then null else 'string' end as c06,
196
coalesce(null, 'string') as c07,
197
coalesce('string', null) as c08,
198
least('string',null) as c09,
199
least(null, 'string') as c10,
200
greatest('string',null) as c11,
201
greatest(null, 'string') as c12,
202
nullif('string', null) as c13,
203
nullif(null, 'string') as c14,
204
trim('string' from null) as c15,
205
trim(null from 'string') as c16,
206
substring_index('string', null, 1) as c17,
207
substring_index(null, 'string', 1) as c18,
208
elt(1, null, 'string') as c19,
209
elt(1, 'string', null) as c20,
210
concat('string', null) as c21,
211
concat(null, 'string') as c22,
212
concat_ws('sep', 'string', null) as c23,
213
concat_ws('sep', null, 'string') as c24,
214
concat_ws(null, 'string', 'string') as c25,
215
make_set(3, 'string', null) as c26,
216
make_set(3, null, 'string') as c27,
217
export_set(3, null, 'off', 'sep') as c29,
218
export_set(3, 'on', null, 'sep') as c30,
219
export_set(3, 'on', 'off', null) as c31,
220
replace(null, 'from', 'to') as c32,
221
replace('str', null, 'to') as c33,
222
replace('str', 'from', null) as c34,
223
insert('str', 1, 2, null) as c35,
224
insert(null, 1, 2, 'str') as c36,
225
lpad('str', 10, null) as c37,
226
rpad(null, 10, 'str') as c38;
227
show create table t1;
229
t1 CREATE TABLE `t1` (
230
`c00` binary(0) DEFAULT NULL,
231
`c01` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
232
`c02` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
233
`c03` varchar(6) CHARACTER SET latin2 NOT NULL DEFAULT '',
234
`c04` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
235
`c05` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
236
`c06` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
237
`c07` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
238
`c08` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
239
`c09` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
240
`c10` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
241
`c11` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
242
`c12` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
243
`c13` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
244
`c14` char(0) CHARACTER SET latin2 DEFAULT NULL,
245
`c15` char(0) CHARACTER SET latin2 DEFAULT NULL,
246
`c16` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
247
`c17` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
248
`c18` char(0) CHARACTER SET latin2 DEFAULT NULL,
249
`c19` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
250
`c20` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
251
`c21` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
252
`c22` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
253
`c23` varchar(9) CHARACTER SET latin2 DEFAULT NULL,
254
`c24` varchar(9) CHARACTER SET latin2 DEFAULT NULL,
255
`c25` varchar(12) CHARACTER SET latin2 DEFAULT NULL,
256
`c26` varchar(7) CHARACTER SET latin2 DEFAULT NULL,
257
`c27` varchar(7) CHARACTER SET latin2 DEFAULT NULL,
258
`c29` varchar(381) CHARACTER SET latin2 DEFAULT NULL,
259
`c30` varchar(317) CHARACTER SET latin2 DEFAULT NULL,
260
`c31` varchar(192) CHARACTER SET latin2 DEFAULT NULL,
261
`c32` char(0) CHARACTER SET latin2 DEFAULT NULL,
262
`c33` varchar(3) CHARACTER SET latin2 DEFAULT NULL,
263
`c34` varchar(3) CHARACTER SET latin2 DEFAULT NULL,
264
`c35` varchar(3) CHARACTER SET latin2 DEFAULT NULL,
265
`c36` varchar(3) CHARACTER SET latin2 DEFAULT NULL,
266
`c37` varchar(10) CHARACTER SET latin2 DEFAULT NULL,
267
`c38` varchar(10) CHARACTER SET latin2 DEFAULT NULL
268
) ENGINE=MyISAM DEFAULT CHARSET=latin1
271
case 'str' when 'STR' then 'str' when null then 'null' end as c01,
272
case 'str' when null then 'null' when 'STR' then 'str' end as c02,
273
field(null, 'str1', 'str2') as c03,
274
field('str1','STR1', null) as c04,
275
field('str1', null, 'STR1') as c05,
276
'string' in ('STRING', null) as c08,
277
'string' in (null, 'STRING') as c09;
278
c01 c02 c03 c04 c05 c08 c09
281
create table bug19145a (e enum('a','b','c') default 'b' , s set('x', 'y', 'z') default 'y' ) engine=MyISAM;
282
create table bug19145b (e enum('a','b','c') default null, s set('x', 'y', 'z') default null) engine=MyISAM;
283
create table bug19145c (e enum('a','b','c') not null default 'b' , s set('x', 'y', 'z') not null default 'y' ) engine=MyISAM;
284
create table bug19145setnotnulldefaultnull (e enum('a','b','c') default null, s set('x', 'y', 'z') not null default null) engine=MyISAM;
285
ERROR 42000: Invalid default value for 's'
286
create table bug19145enumnotnulldefaultnull (e enum('a','b','c') not null default null, s set('x', 'y', 'z') default null) engine=MyISAM;
287
ERROR 42000: Invalid default value for 'e'
288
alter table bug19145a alter column e set default null;
289
alter table bug19145a alter column s set default null;
290
alter table bug19145a add column (i int);
291
alter table bug19145b alter column e set default null;
292
alter table bug19145b alter column s set default null;
293
alter table bug19145b add column (i int);
294
alter table bug19145c alter column e set default null;
295
ERROR 42000: Invalid default value for 'e'
296
alter table bug19145c alter column s set default null;
297
ERROR 42000: Invalid default value for 's'
298
alter table bug19145c add column (i int);
299
show create table bug19145a;
301
bug19145a CREATE TABLE `bug19145a` (
302
`e` enum('a','b','c') DEFAULT NULL,
303
`s` set('x','y','z') DEFAULT NULL,
304
`i` int(11) DEFAULT NULL
305
) ENGINE=MyISAM DEFAULT CHARSET=latin1
306
show create table bug19145b;
308
bug19145b CREATE TABLE `bug19145b` (
309
`e` enum('a','b','c') DEFAULT NULL,
310
`s` set('x','y','z') DEFAULT NULL,
311
`i` int(11) DEFAULT NULL
312
) ENGINE=MyISAM DEFAULT CHARSET=latin1
313
show create table bug19145c;
315
bug19145c CREATE TABLE `bug19145c` (
316
`e` enum('a','b','c') NOT NULL DEFAULT 'b',
317
`s` set('x','y','z') NOT NULL DEFAULT 'y',
318
`i` int(11) DEFAULT NULL
319
) ENGINE=MyISAM DEFAULT CHARSET=latin1
320
drop table bug19145a;
321
drop table bug19145b;
322
drop table bug19145c;
325
# Bug #31471: decimal_bin_size: Assertion `scale >= 0 &&
326
# precision > 0 && scale <= precision'
328
CREATE TABLE t1 (a DECIMAL (1, 0) ZEROFILL, b DECIMAL (1, 0) ZEROFILL);
329
INSERT INTO t1 (a, b) VALUES (0, 0);
330
CREATE TABLE t2 SELECT IFNULL(a, b) FROM t1;
332
Field Type Null Key Default Extra
333
IFNULL(a, b) decimal(1,0) unsigned YES NULL
335
CREATE TABLE t2 SELECT IFNULL(a, NULL) FROM t1;
337
Field Type Null Key Default Extra
338
IFNULL(a, NULL) decimal(1,0) YES NULL
340
CREATE TABLE t2 SELECT IFNULL(NULL, b) FROM t1;
342
Field Type Null Key Default Extra
343
IFNULL(NULL, b) decimal(1,0) YES NULL