nulls.out

derby database source code.good for you.

ij> --
-- this test shows the current supported null value functionality
--
autocommit off;
ij> -- trying to define null and not null for a column
create table a(a1 int null not null);
ERROR 42X01: Syntax error: Encountered "null" at line 2, column 23.
ij> -- same as above, except that it's in reverse order
create table a(a1 int not null null);
ERROR 42X01: Syntax error: Encountered "null" at line 2, column 32.
ij> -- defining null constraint for a column now does not work
create table a(a1 int not null , a2 int not null);
0 rows inserted/updated/deleted
ij> -- alter table adding explicitly nullable column and primary key column
-- constraint on it fails
alter table a add column a3 int null constraint ap1 primary key;
ERROR 42X01: Syntax error: Encountered "null" at line 3, column 33.
ij> -- alter table table level primary key constraint on nullable column
-- doesn't give an error
alter table a add constraint ap1 primary key(a1,a2);
0 rows inserted/updated/deleted
ij> drop table a;
0 rows inserted/updated/deleted
ij> -- create table with not null column and unique key should work
create table a (a int not null unique );
0 rows inserted/updated/deleted
ij> insert into a values (1);
1 row inserted/updated/deleted
ij> -- second insert should fail
insert into a values (1);
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'xxxxGENERATED-IDxxxx' defined on 'A'.
ij> drop table a;
0 rows inserted/updated/deleted
ij> -- alter nullability on a unique column should fail
create table a ( a int not null unique);
0 rows inserted/updated/deleted
ij> alter table a modify a null;
ERROR 42X01: Syntax error: Encountered "modify" at line 1, column 15.
ij> drop table a;
0 rows inserted/updated/deleted
ij> -- try adding a primary key where there is null data
-- this should error
create table a (a1 int not null, a2 int);
0 rows inserted/updated/deleted
ij> insert into a values(1, NULL);
1 row inserted/updated/deleted
ij> alter table a add constraint ap1 primary key(a1, a2);
ERROR 42831: 'A2' cannot be a column of a primary key or unique key because it can contain null values.
ij> drop table a;
0 rows inserted/updated/deleted
ij> -- try with multiple columns
create table a (a1 int, a2 int, a3 int);
0 rows inserted/updated/deleted
ij> -- This is an error in DB2 compatibility mode
alter table a add constraint ap1 primary key(a1, a2, a3);
ERROR 42831: 'A1' cannot be a column of a primary key or unique key because it can contain null values.
ij> drop table a;
0 rows inserted/updated/deleted
ij> -- try with multiple null columns
create table a (a1 int not null, a2 int, a3 int);
0 rows inserted/updated/deleted
ij> insert into a values(1,1,1);
1 row inserted/updated/deleted
ij> -- table with no null data should work
alter table a add constraint ap1 primary key(a1, a2, a3);
ERROR 42831: 'A2' cannot be a column of a primary key or unique key because it can contain null values.
ij> -- insert a null into one of the primary key columns should fail
insert into a values(1, NULL, 1);
1 row inserted/updated/deleted
ij> drop table a;
0 rows inserted/updated/deleted
ij> -- try with multiple null columns
create table a (a1 int not null, a2 int default null, a3 int default null);
0 rows inserted/updated/deleted
ij> insert into a values(1,NULL,1);
1 row inserted/updated/deleted
ij> -- table with some null data should fail
alter table a add constraint ap1 primary key(a1, a2, a3);
ERROR 42831: 'A2' cannot be a column of a primary key or unique key because it can contain null values.
ij> -- defining primarykey column constraint for explicitly nullable column
-- gives error
create table a1(ac1 int null primary key);
ERROR 42X01: Syntax error: Encountered "null" at line 3, column 25.
ij> -- defining primarykey table constraint on explicitly nullable columns
-- give error
create table a1(ac1 int null, ac2 int not null, primary key(ac1,ac2));
ERROR 42X01: Syntax error: Encountered "null" at line 3, column 25.
ij> -- say null twice should fail
create table a2(ac1 int null null);
ERROR 42X01: Syntax error: Encountered "null" at line 2, column 25.
ij> -- say not null, null and no null for a column. This is to make sure the flags
-- stay proper for a column
create table a3(ac1 int not null null not null);
ERROR 42X01: Syntax error: Encountered "null" at line 3, column 34.
ij> -- first statement says null and second one says not null. This is to make sure
-- the flag for the first one doesn't affect the second one
create table a3(ac1 int default null);
0 rows inserted/updated/deleted
ij> create table a4(ac1 int not null);
0 rows inserted/updated/deleted
ij> -- one column says null and second one says not null
create table a5(ac1 int default null, ac2 int not null);
0 rows inserted/updated/deleted
ij> -- statement1 says null, 2nd says nothing but says primary key
create table a6(ac1 int default null);
0 rows inserted/updated/deleted
ij> create table a7(ac1 int not null primary key);
0 rows inserted/updated/deleted
ij> -- create a table with null and non-null columns
create table t (i int, i_d int default null, i_n int not null,
		s smallint, s_d smallint default null, s_n smallint not null);
0 rows inserted/updated/deleted
ij> -- insert non-nulls into null and non-null columns
insert into t (i, i_d, i_n, s, s_d, s_n) values (1, 1, 1, 1, 1, 1);
1 row inserted/updated/deleted
ij> -- insert nulls into those columns that take nulls
insert into t values (null, null, 2, null, null, 2);
1 row inserted/updated/deleted
ij> -- insert a null as a default value into the first default null column
insert into t (i, i_n, s, s_d, s_n) values (3, 3, 3, 3, 3);
1 row inserted/updated/deleted
ij> -- insert a null as a default value into the other default null columns
insert into t (i, i_d, i_n, s, s_n) values (4, 4, 4, 4, 4);
1 row inserted/updated/deleted
ij> -- insert nulls as default values into all default null columns
insert into t (i, i_n, s, s_n) values (5, 5, 5, 5);
1 row inserted/updated/deleted
ij> -- attempt to insert default values into the columns that don't accept nulls
insert into t (i, i_d, s, s_d) values (6, 6, 6, 6);
ERROR 23502: Column 'I_N'  cannot accept a NULL value.
ij> -- insert default nulls into nullable columns that have no explicit defaults
insert into t (i_d, i_n, s_d, s_n) values (7, 7, 7, 7);
1 row inserted/updated/deleted
ij> -- attempt to insert an explicit null into a column that doesn't accept nulls
insert into t values (8, 8, null, 8, 8, 8);
ERROR 23502: Column 'I_N'  cannot accept a NULL value.
ij> -- attempt to insert an explicit null into the other columns
-- that doesn't accept nulls
insert into t values (9, 9, 9, 9, 9, null);
ERROR 23502: Column 'S_N'  cannot accept a NULL value.
ij> -- select all the successfully inserted rows
select * from t;
I          |I_D        |I_N        |S     |S_D   |S_N   
--------------------------------------------------------
1          |1          |1          |1     |1     |1     
NULL       |NULL       |2          |NULL  |NULL  |2     
3          |NULL       |3          |3     |3     |3     
4          |4          |4          |4     |NULL  |4     
5          |NULL       |5          |5     |NULL  |5     
NULL       |7          |7          |NULL  |7     |7     
ij> -- create a table with a non-null column with a default value of null
-- and verify that nulls are not allowed
create table s (x int default null not null, y int);
0 rows inserted/updated/deleted
ij> insert into s (y) values(1);
ERROR 23502: Column 'X'  cannot accept a NULL value.
ij> select * from s;
X          |Y          
-----------------------
ij> -- is null/is not null on an integer type
create table u (c1 integer);
0 rows inserted/updated/deleted
ij> insert into u values null;
1 row inserted/updated/deleted
ij> insert into u values 1;
1 row inserted/updated/deleted
ij> insert into u values null;
1 row inserted/updated/deleted
ij> insert into u values 2;
1 row inserted/updated/deleted
ij> select * from u where c1 is null;
C1         
-----------
NULL       
NULL       
ij> select * from u where c1 is not null;
C1         
-----------
1          
2          
ij> -- is [not] null and parameters
prepare p1 as 'select * from u where cast (? as varchar(1)) is null';
ij> execute p1 using 'values (''a'')';
C1         
-----------
ij> prepare p2 as 'select * from u where cast (? as varchar(1)) is not null';
ij> execute p2 using 'values (''a'')';
C1         
-----------
NULL       
1          
NULL       
2          
ij> select count(*) from u where c1 is null;
1          
-----------
2          
ij> insert into u select * from (values null) as X;
1 row inserted/updated/deleted
ij> select count(*) from u where c1 is null;
1          
-----------
3          
ij> -- cleanup
drop table t;
0 rows inserted/updated/deleted
ij> drop table s;
0 rows inserted/updated/deleted
ij> drop table u;
0 rows inserted/updated/deleted
ij> drop table a;
0 rows inserted/updated/deleted
ij> drop table a3;
0 rows inserted/updated/deleted
ij> drop table a4;
0 rows inserted/updated/deleted
ij> drop table a5;
0 rows inserted/updated/deleted
ij> drop table a6;
0 rows inserted/updated/deleted
ij> drop table a7;
0 rows inserted/updated/deleted
ij>