~drizzle-trunk/drizzle/development

drop table if exists t1, t2;

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;

ERROR 22012: Division by 0

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;

ERROR 22012: Division by 0

select CONCAT(1, NULL),1+NULL,1-NULL;

CONCAT(1, NULL)	1+NULL	1-NULL

NULL	NULL	NULL

select NULL=NULL,NULL<>NULL,IFNULL(NULL,1.1)+0,CONCAT(IFNULL(NULL,1), 0);

NULL=NULL	NULL<>NULL	IFNULL(NULL,1.1)+0	CONCAT(IFNULL(NULL,1), 0)

NULL	NULL	1.1	10

select strcmp("a",NULL),(1<NULL)+0.0,null like "a%","a%" like null;

strcmp("a",NULL)	(1<NULL)+0.0	null like "a%"	"a%" like null

NULL	NULL	NULL	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);

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

select repeat("a",0),repeat("ab",5+5),repeat("ab",-1),reverse(NULL);

repeat("a",0)	repeat("ab",5+5)	repeat("ab",-1)	reverse(NULL)

	abababababababababab		NULL

select field(NULL,"a","b","c");

field(NULL,"a","b","c")

0

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;

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

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;

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

Warnings:

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`

SELECT NULL AND NULL, 1 AND NULL, NULL AND 1, NULL OR NULL, 0 OR NULL, NULL OR 0;

NULL AND NULL	1 AND NULL	NULL AND 1	NULL OR NULL	0 OR NULL	NULL OR 0

NULL	NULL	NULL	NULL	NULL	NULL

SELECT (NULL OR NULL) IS NULL;

(NULL OR NULL) IS NULL

1

select NULL AND 0, 0 and NULL;

NULL AND 0	0 and NULL

0	0

create table t1 (x int);

insert into t1 values (null);

select * from t1 where x != 0;

x

drop table t1;

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;

create table t1 (a int, b int);

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;

CREATE TABLE t1 (a varchar(16) NOT NULL default '', b int NOT NULL default 0, c datetime NOT NULL default '2009-02-10 00:00:00', d int NOT NULL default 0);

INSERT INTO t1 (a) values (null);

ERROR 23000: Column 'a' cannot be null

INSERT INTO t1 (a) values (1/null);

ERROR 23000: Column 'a' cannot be null

INSERT INTO t1 (a) values (null),(null);

ERROR 23000: Column 'a' cannot be null

INSERT INTO t1 (b) values (null);

ERROR 23000: Column 'b' cannot be null

INSERT INTO t1 (b) values (1/null);

ERROR 23000: Column 'b' cannot be null

INSERT INTO t1 (b) values (null),(null);

ERROR 23000: Column 'b' cannot be null

INSERT INTO t1 (c) values (null);

ERROR 23000: Column 'c' cannot be null

INSERT INTO t1 (c) values (1/null);

ERROR 23000: Column 'c' cannot be null

INSERT INTO t1 (c) values (null),(null);

ERROR 23000: Column 'c' cannot be null

INSERT INTO t1 (d) values (null);

ERROR 23000: Column 'd' cannot be null

INSERT INTO t1 (d) values (1/null);

ERROR 23000: Column 'd' cannot be null

INSERT INTO t1 (d) values (null),(null);

ERROR 23000: Column 'd' cannot be null

UPDATE t1 SET d= NULL;

INSERT INTO t1 VALUES ();

UPDATE t1 SET a=1/NULL;

ERROR 23000: Column 'a' cannot be null

UPDATE t1 SET a=NULL;

ERROR 23000: Column 'a' cannot be null

UPDATE t1 SET b=NULL;

ERROR 23000: Column 'b' cannot be null

UPDATE t1 SET c=NULL;

ERROR 23000: Column 'c' cannot be null

UPDATE t1 SET d=NULL;

ERROR 23000: Column 'd' cannot be null

truncate table t1;

LOAD DATA INFILE '../../std_data_ln/null_test.txt' INTO TABLE t1 FIELDS ENCLOSED BY '"';

ERROR 22004: Column set to default value; NULL supplied to NOT NULL column 'a' at row 1

drop table t1;

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;

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` (

  `c00` VARBINARY(0) DEFAULT NULL,

  `c01` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c02` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c03` VARCHAR(6) COLLATE utf8_general_ci NOT NULL,

  `c04` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c05` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c06` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c07` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c08` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c09` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c10` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c11` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c12` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c13` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c14` VARCHAR(0) COLLATE utf8_general_ci DEFAULT NULL,

  `c15` VARCHAR(0) COLLATE utf8_general_ci DEFAULT NULL,

  `c16` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c17` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c18` VARCHAR(0) COLLATE utf8_general_ci DEFAULT NULL,

  `c19` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c20` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c21` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c22` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,

  `c23` VARCHAR(9) COLLATE utf8_general_ci DEFAULT NULL,

  `c24` VARCHAR(9) COLLATE utf8_general_ci DEFAULT NULL,

  `c25` VARCHAR(12) COLLATE utf8_general_ci DEFAULT NULL,

  `c26` VARCHAR(7) COLLATE utf8_general_ci DEFAULT NULL,

  `c27` VARCHAR(7) COLLATE utf8_general_ci DEFAULT NULL,

  `c29` VARCHAR(381) COLLATE utf8_general_ci DEFAULT NULL,

  `c30` VARCHAR(317) COLLATE utf8_general_ci DEFAULT NULL,

  `c31` VARCHAR(192) COLLATE utf8_general_ci DEFAULT NULL,

  `c32` VARCHAR(0) COLLATE utf8_general_ci DEFAULT NULL,

  `c33` VARCHAR(3) COLLATE utf8_general_ci DEFAULT NULL,

  `c34` VARCHAR(3) COLLATE utf8_general_ci DEFAULT NULL,

  `c35` VARCHAR(3) COLLATE utf8_general_ci DEFAULT NULL,

  `c36` VARCHAR(3) COLLATE utf8_general_ci DEFAULT NULL,

  `c37` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

  `c38` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL

) ENGINE=DEFAULT COLLATE = utf8_general_ci

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

# End of 4.1 tests

#

# Bug #31471: decimal_bin_size: Assertion `scale >= 0 &&

#             precision > 0 && scale <= precision'

#

CREATE TABLE t1 (a DECIMAL (1, 0) , b DECIMAL (1, 0) );

INSERT INTO t1 (a, b) VALUES (0, 0);

CREATE TABLE t2 SELECT IFNULL(a, b) FROM t1;

DESCRIBE t2;

Field	Type	Null	Default	Default_is_NULL	On_Update

IFNULL(a, b)	DECIMAL	YES		YES	

DROP TABLE t2;

CREATE TABLE t2 SELECT IFNULL(a, NULL) FROM t1;

DESCRIBE t2;

Field	Type	Null	Default	Default_is_NULL	On_Update

IFNULL(a, NULL)	DECIMAL	YES		YES	

DROP TABLE t2;

CREATE TABLE t2 SELECT IFNULL(NULL, b) FROM t1;

DESCRIBE t2;

Field	Type	Null	Default	Default_is_NULL	On_Update

IFNULL(NULL, b)	DECIMAL	YES		YES	

DROP TABLE t1, t2;

# End of 5.0 tests