nulls.sql

derby database source code.good for you.

--
-- this test shows the current supported null value functionality
--
autocommit off;

-- trying to define null and not null for a column
create table a(a1 int null not null);

-- same as above, except that it's in reverse order
create table a(a1 int not null null);

-- defining null constraint for a column now does not work
create table a(a1 int not null , a2 int not null);

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

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

drop table a;

-- create table with not null column and unique key should work
create table a (a int not null unique );
insert into a values (1);
-- second insert should fail
insert into a values (1);

drop table a;

-- alter nullability on a unique column should fail
create table a ( a int not null unique);
alter table a modify a null;

drop table a;

-- try adding a primary key where there is null data
-- this should error
create table a (a1 int not null, a2 int);
insert into a values(1, NULL);

alter table a add constraint ap1 primary key(a1, a2);

drop table a;

-- try with multiple columns
create table a (a1 int, a2 int, a3 int);

-- This is an error in DB2 compatibility mode
alter table a add constraint ap1 primary key(a1, a2, a3);

drop table a;

-- try with multiple null columns
create table a (a1 int not null, a2 int, a3 int);
insert into a values(1,1,1);

-- table with no null data should work
alter table a add constraint ap1 primary key(a1, a2, a3);

-- insert a null into one of the primary key columns should fail
insert into a values(1, NULL, 1);

drop table a;

-- try with multiple null columns
create table a (a1 int not null, a2 int default null, a3 int default null);
insert into a values(1,NULL,1);

-- table with some null data should fail
alter table a add constraint ap1 primary key(a1, a2, a3);

-- defining primarykey column constraint for explicitly nullable column
-- gives error
create table a1(ac1 int null primary key);

-- defining primarykey table constraint on explicitly nullable columns
-- give error
create table a1(ac1 int null, ac2 int not null, primary key(ac1,ac2));

-- say null twice should fail
create table a2(ac1 int null null);

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

-- 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);
create table a4(ac1 int not null);

-- one column says null and second one says not null
create table a5(ac1 int default null, ac2 int not null);

-- statement1 says null, 2nd says nothing but says primary key
create table a6(ac1 int default null);
create table a7(ac1 int not null primary key);

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

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

-- insert nulls into those columns that take nulls
insert into t values (null, null, 2, null, null, 2);

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

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

-- insert nulls as default values into all default null columns
insert into t (i, i_n, s, s_n) values (5, 5, 5, 5);

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

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

-- attempt to insert an explicit null into a column that doesn't accept nulls
insert into t values (8, 8, null, 8, 8, 8);

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

-- select all the successfully inserted rows
select * from t;

-- 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);
insert into s (y) values(1);
select * from s;

-- is null/is not null on an integer type
create table u (c1 integer);
insert into u values null;
insert into u values 1;
insert into u values null;
insert into u values 2;
select * from u where c1 is null;
select * from u where c1 is not null;

-- is [not] null and parameters
prepare p1 as 'select * from u where cast (? as varchar(1)) is null';
execute p1 using 'values (''a'')';
prepare p2 as 'select * from u where cast (? as varchar(1)) is not null';
execute p2 using 'values (''a'')';

select count(*) from u where c1 is null;
insert into u select * from (values null) as X;
select count(*) from u where c1 is null;

-- cleanup
drop table t;
drop table s;
drop table u;
drop table a;
drop table a3;
drop table a4;
drop table a5;
drop table a6;
drop table a7;