ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- -- 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 constraint auniq 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 'AUNIQ' 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>