null.result 14.4 KB
Newer Older
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 4
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
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 7
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
8
Warnings:
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 12
1 | NULL	1 & NULL	1+NULL	1-NULL
NULL	NULL	NULL	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
15
NULL	NULL	1.1	1
16
select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null;
17 18
strcmp("a",NULL)	(1<NULL)+0.0	NULL regexp "a"	null like "a%"	"a%" like null
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 21
concat("a",NULL)	replace(NULL,"a","b")	replace("string","i",NULL)	replace("string",NULL,"i")	insert("abc",1,1,NULL)	left(NULL,1)
NULL	NULL	NULL	NULL	NULL	NULL
22
select repeat("a",0),repeat("ab",5+5),repeat("ab",-1),reverse(NULL);
23 24
repeat("a",0)	repeat("ab",5+5)	repeat("ab",-1)	reverse(NULL)
	abababababababababab		NULL
25
select field(NULL,"a","b","c");
26 27
field(NULL,"a","b","c")
0
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 30
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
0	0	NULL	NULL	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 33
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
34
Warnings:
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 38
NULL AND NULL	1 AND NULL	NULL AND 1	NULL OR NULL	0 OR NULL	NULL OR 0
NULL	NULL	NULL	NULL	NULL	NULL
39
SELECT (NULL OR NULL) IS NULL;
40 41
(NULL OR NULL) IS NULL
1
42
select NULL AND 0, 0 and NULL;
43
NULL AND 0	0 and NULL
44
0	0
45
select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton("");
46 47
inet_ntoa(null)	inet_aton(null)	inet_aton("122.256")	inet_aton("122.226.")	inet_aton("")
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 50
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
51
Warnings:
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 54 55
create table t1 (x int);
insert into t1 values (null);
select * from t1 where x != 0;
56
x
57
drop table t1;
58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
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;
indexed_field
SELECT * FROM t1 WHERE indexed_field IS NULL;
indexed_field
NULL
NULL
SELECT * FROM t1 WHERE indexed_field<=>NULL;
indexed_field
NULL
NULL
DROP TABLE t1;
74
create table t1 (a int, b int) engine=myisam;
75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90
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;
b	ifnull(t2.b,"this is null")
NULL	this is 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;
b	ifnull(t2.b,"this is null")
NULL	this is null
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;
b	ifnull(t2.b,"this is null")
NULL	this is null
NULL	this is null
drop table t1;
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";
93
Warnings:
monty@mysql.com's avatar
monty@mysql.com committed
94
Warning	1265	Data truncated for column 'd' at row 1
95
UPDATE t1 SET d=1/NULL;
96 97
Warnings:
Warning	1265	Data truncated for column 'd' at row 1
98
UPDATE t1 SET d=NULL;
99 100
Warnings:
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);
venu@myvenu.com's avatar
venu@myvenu.com committed
106
Warnings:
107 108
Warning	1048	Column 'a' cannot be null
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);
venu@myvenu.com's avatar
venu@myvenu.com committed
114
Warnings:
115 116
Warning	1048	Column 'b' cannot be null
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);
venu@myvenu.com's avatar
venu@myvenu.com committed
122
Warnings:
123 124
Warning	1048	Column 'c' cannot be null
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);
venu@myvenu.com's avatar
venu@myvenu.com committed
130
Warnings:
131 132
Warning	1048	Column 'd' cannot be null
Warning	1048	Column 'd' cannot be null
133 134
select * from t1;
a	b	c	d
135
	0	0000-00-00 00:00:00	0
136 137 138 139 140 141 142 143 144
	0	0000-00-00 00:00:00	0
	0	0000-00-00 00:00:00	0
	0	0000-00-00 00:00:00	0
	0	0000-00-00 00:00:00	0
	0	0000-00-00 00:00:00	0
	0	0000-00-00 00:00:00	0
	0	0000-00-00 00:00:00	0
	0	0000-00-00 00:00:00	0
drop table t1;
145 146 147 148 149 150 151 152 153 154 155
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;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx	idx	4	NULL	2	Using where
explain select * from t1 where a between 2 and 3 or b is null;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx	idx	4	NULL	2	Using where
drop table t1;
156 157 158
select cast(NULL as signed);
cast(NULL as signed)
NULL
159 160 161 162 163 164 165 166 167 168 169
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;
170
insert into t1 values(null);
171 172
explain select * from t1 where i=2 or i is null;
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 175 176
select count(*) from t1 where i=2 or i is null;
count(*)
10
177
alter table t1 change i i int not null;
178 179
Warnings:
Warning	1265	Data truncated for column 'i' at row 513
180 181
explain select * from t1 where i=2 or i is null;
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 184 185
select count(*) from t1 where i=2 or i is null;
count(*)
9
186
drop table t1;
187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229
set names latin2;
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;
Table	Create Table
t1	CREATE TABLE `t1` (
230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267
  `c00` binary(0) DEFAULT NULL,
  `c01` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c02` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c03` varchar(6) CHARACTER SET latin2 NOT NULL DEFAULT '',
  `c04` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c05` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c06` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c07` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c08` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c09` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c10` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c11` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c12` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c13` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c14` char(0) CHARACTER SET latin2 DEFAULT NULL,
  `c15` char(0) CHARACTER SET latin2 DEFAULT NULL,
  `c16` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c17` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c18` char(0) CHARACTER SET latin2 DEFAULT NULL,
  `c19` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c20` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c21` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c22` varchar(6) CHARACTER SET latin2 DEFAULT NULL,
  `c23` varchar(9) CHARACTER SET latin2 DEFAULT NULL,
  `c24` varchar(9) CHARACTER SET latin2 DEFAULT NULL,
  `c25` varchar(12) CHARACTER SET latin2 DEFAULT NULL,
  `c26` varchar(7) CHARACTER SET latin2 DEFAULT NULL,
  `c27` varchar(7) CHARACTER SET latin2 DEFAULT NULL,
  `c29` varchar(381) CHARACTER SET latin2 DEFAULT NULL,
  `c30` varchar(317) CHARACTER SET latin2 DEFAULT NULL,
  `c31` varchar(192) CHARACTER SET latin2 DEFAULT NULL,
  `c32` char(0) CHARACTER SET latin2 DEFAULT NULL,
  `c33` varchar(3) CHARACTER SET latin2 DEFAULT NULL,
  `c34` varchar(3) CHARACTER SET latin2 DEFAULT NULL,
  `c35` varchar(3) CHARACTER SET latin2 DEFAULT NULL,
  `c36` varchar(3) CHARACTER SET latin2 DEFAULT NULL,
  `c37` varchar(10) CHARACTER SET latin2 DEFAULT NULL,
  `c38` varchar(10) CHARACTER SET latin2 DEFAULT NULL
268 269 270 271 272 273 274 275 276 277 278 279 280
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
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;
c01	c02	c03	c04	c05	c08	c09
str	str	0	1	2	1	1
set names latin1;
281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301
create table bug19145a (e enum('a','b','c')          default 'b' , s set('x', 'y', 'z')          default 'y' ) engine=MyISAM;
create table bug19145b (e enum('a','b','c')          default null, s set('x', 'y', 'z')          default null) engine=MyISAM;
create table bug19145c (e enum('a','b','c') not null default 'b' , s set('x', 'y', 'z') not null default 'y' ) engine=MyISAM;
create table bug19145setnotnulldefaultnull (e enum('a','b','c')          default null, s set('x', 'y', 'z') not null default null) engine=MyISAM;
ERROR 42000: Invalid default value for 's'
create table bug19145enumnotnulldefaultnull (e enum('a','b','c') not null default null, s set('x', 'y', 'z')          default null) engine=MyISAM;
ERROR 42000: Invalid default value for 'e'
alter table bug19145a alter column e set default null;
alter table bug19145a alter column s set default null;
alter table bug19145a add column (i int);
alter table bug19145b alter column e set default null;
alter table bug19145b alter column s set default null;
alter table bug19145b add column (i int);
alter table bug19145c alter column e set default null;
ERROR 42000: Invalid default value for 'e'
alter table bug19145c alter column s set default null;
ERROR 42000: Invalid default value for 's'
alter table bug19145c add column (i int);
show create table bug19145a;
Table	Create Table
bug19145a	CREATE TABLE `bug19145a` (
302 303 304
  `e` enum('a','b','c') DEFAULT NULL,
  `s` set('x','y','z') DEFAULT NULL,
  `i` int(11) DEFAULT NULL
305 306 307 308
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show create table bug19145b;
Table	Create Table
bug19145b	CREATE TABLE `bug19145b` (
309 310 311
  `e` enum('a','b','c') DEFAULT NULL,
  `s` set('x','y','z') DEFAULT NULL,
  `i` int(11) DEFAULT NULL
312 313 314 315
) ENGINE=MyISAM DEFAULT CHARSET=latin1
show create table bug19145c;
Table	Create Table
bug19145c	CREATE TABLE `bug19145c` (
316 317 318
  `e` enum('a','b','c') NOT NULL DEFAULT 'b',
  `s` set('x','y','z') NOT NULL DEFAULT 'y',
  `i` int(11) DEFAULT NULL
319 320 321 322
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table bug19145a;
drop table bug19145b;
drop table bug19145c;
323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345
# End of 4.1 tests
#
# Bug #31471: decimal_bin_size: Assertion `scale >= 0 &&
#             precision > 0 && scale <= precision'
#
CREATE TABLE t1 (a DECIMAL (1, 0) ZEROFILL, b DECIMAL (1, 0) ZEROFILL);
INSERT INTO t1 (a, b) VALUES (0, 0);
CREATE TABLE t2 SELECT IFNULL(a, b) FROM t1;
DESCRIBE t2;
Field	Type	Null	Key	Default	Extra
IFNULL(a, b)	decimal(1,0) unsigned	YES		NULL	
DROP TABLE t2;
CREATE TABLE t2 SELECT IFNULL(a, NULL) FROM t1;
DESCRIBE t2;
Field	Type	Null	Key	Default	Extra
IFNULL(a, NULL)	decimal(1,0)	YES		NULL	
DROP TABLE t2;
CREATE TABLE t2 SELECT IFNULL(NULL, b) FROM t1;
DESCRIBE t2;
Field	Type	Null	Key	Default	Extra
IFNULL(NULL, b)	decimal(1,0)	YES		NULL	
DROP TABLE t1, t2;
# End of 5.0 tests