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. -- -- tests for column defaults -- negative -- ? in default create table neg(c1 int default ?); ERROR 42X01: Syntax error: Encountered "?" at line 22, column 33. ij> -- column reference in default create table neg(c1 int, c2 int default c1); ERROR 42X01: Syntax error: Encountered "c1" at line 2, column 41. ij> -- subquery in default create table neg(c1 int default (values 1)); ERROR 42X01: Syntax error: Encountered "(" at line 2, column 33. ij> -- type incompatibility at compile time create table neg(c1 date default 1); ERROR 42821: Columns of type 'DATE' cannot hold values of type 'INTEGER'. ij> -- type incompatibility at execution time -- bug 5585 - should fail at create table statement -- because the default value '1' is not valid create table neg(c1 int, c2 date default '1'); 0 rows inserted/updated/deleted ij> insert into neg (c1) values 1; ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> drop table neg; 0 rows inserted/updated/deleted ij> -- bug 5203 - built-in functions are not be allowed in a constantExpression -- because DB2 UDB returns SQLSTATE 42894 CREATE FUNCTION ASDF (DATA DOUBLE) RETURNS DOUBLE EXTERNAL NAME 'java.lang.Math.sin' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> create table neg(c1 int default asdf(0)); ERROR 42894: DEFAULT value or IDENTITY attribute value is not valid for column 'C1'. ij> drop table neg; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'NEG' because it does not exist. ij> -- DEFAULT only valid in VALUES within an insert values default; ERROR 42Y85: The DEFAULT keyword is only allowed in a VALUES clause when the VALUES clause appears within an INSERT statement. ij> values 1, default; ERROR 42Y85: The DEFAULT keyword is only allowed in a VALUES clause when the VALUES clause appears within an INSERT statement. ij> -- alter table modify default create table neg(c1 date); 0 rows inserted/updated/deleted ij> alter table neg modify x default null; ERROR 42X01: Syntax error: Encountered "modify" at line 1, column 17. ij> alter table neg add column x date default 1; ERROR 42821: Columns of type 'DATE' cannot hold values of type 'INTEGER'. ij> -- bug 5585 - should fail at alter table statement -- because the default value '1' is not valid alter table neg add column x date default '1'; 0 rows inserted/updated/deleted ij> insert into neg (c1) values default; ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> drop table neg; 0 rows inserted/updated/deleted ij> -- too many values in values clause create table neg(c1 int default 10); 0 rows inserted/updated/deleted ij> insert into neg values (1, default); ERROR 42X06: Too many result columns specified for table '"APP"."NEG"'. ij> insert into neg values (default, 1); ERROR 42802: The number of values assigned is not the same as the number of specified or implied columns. ij> drop table neg; 0 rows inserted/updated/deleted ij> -- positive -- create tables create table t1(c1 int, c2 int with default 5, c3 date default current_date, c4 int); 0 rows inserted/updated/deleted ij> -- verify that defaults work insert into t1 (c1) values 1; 1 row inserted/updated/deleted ij> insert into t1 (c4) values 4; 1 row inserted/updated/deleted ij> select c1, c2, c4 from t1; C1 |C2 |C4 ----------------------------------- 1 |5 |NULL NULL |5 |4 ij> select c1, c2, c4 from t1 where c3 = current_date; C1 |C2 |C4 ----------------------------------- 1 |5 |NULL NULL |5 |4 ij> -- update -- default for column whose default is null update t1 set c1 = default; 2 rows inserted/updated/deleted ij> select c1, c2, c4 from t1 where c3 = current_date; C1 |C2 |C4 ----------------------------------- NULL |5 |NULL NULL |5 |4 ij> -- default for column that has explicit default update t1 set c2 = 7; 2 rows inserted/updated/deleted ij> select c2 from t1; C2 ----------- 7 7 ij> update t1 set c2 = default; 2 rows inserted/updated/deleted ij> select c2 from t1; C2 ----------- 5 5 ij> -- insert default delete from t1; 2 rows inserted/updated/deleted ij> insert into t1 values (5, default, '1999-09-09', default); 1 row inserted/updated/deleted ij> insert into t1 values (default, 6, default, 5); 1 row inserted/updated/deleted ij> insert into t1 values (default, 6, default, 5), (7, default, '1997-07-07', 3); 2 rows inserted/updated/deleted ij> select c1, c2, c4 from t1 where c3 = current_date; C1 |C2 |C4 ----------------------------------- NULL |6 |5 NULL |6 |5 ij> select c1, c2, c4 from t1 where c3 <> current_date; C1 |C2 |C4 ----------------------------------- 5 |5 |NULL 7 |5 |3 ij> delete from t1; 4 rows inserted/updated/deleted ij> insert into t1 (c1, c3, c4) values (5, '1999-09-09', default); 1 row inserted/updated/deleted ij> insert into t1 (c1, c3, c4) values (default, default, 5); 1 row inserted/updated/deleted ij> insert into t1 (c1, c3, c4) values (default, default, default); 1 row inserted/updated/deleted ij> insert into t1 (c1, c3, c4) values (default, default, 5), (7, '1997-07-07', 3); 2 rows inserted/updated/deleted ij> select c1, c2, c4 from t1 where c3 = current_date; C1 |C2 |C4 ----------------------------------- NULL |5 |5 NULL |5 |NULL NULL |5 |5 ij> select c1, c2, c4 from t1 where c3 <> current_date; C1 |C2 |C4 ----------------------------------- 5 |5 |NULL 7 |5 |3 ij> -- delimited identifiers -- this schema create table "x1" ("c1" int); 0 rows inserted/updated/deleted ij> insert into "x1" values 1; 1 row inserted/updated/deleted ij> alter table "x1" add column "c2" char(1) default 'x'; 0 rows inserted/updated/deleted ij> select * from "x1"; c1 |c2 ---------------- 1 |x ij> -- another schema create schema "otherschema"; 0 rows inserted/updated/deleted ij> create table "otherschema"."y1" ("c11" int); 0 rows inserted/updated/deleted ij> insert into "otherschema"."y1" values 2; 1 row inserted/updated/deleted ij> alter table "otherschema"."y1" add column "c22" char(1) default 'y'; 0 rows inserted/updated/deleted ij> select * from "otherschema"."y1"; c11 |c22 ---------------- 2 |y ij> -- bug 3433 create table t7(c1 int default 10); 0 rows inserted/updated/deleted ij> insert into t7 values (default); 1 row inserted/updated/deleted ij> select * from t7; C1 ----------- 10 ij> -- JIRA issue Derby-331 create table t_331 (a int not null, b int default 0, unique (a)); 0 rows inserted/updated/deleted ij> insert into t_331 values (4, default); 1 row inserted/updated/deleted ij> insert into t_331 values (4, default); 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 'T_331'. ij> select * from t_331; A |B ----------------------- 4 |0 ij> -- begin DERBY-3013 create table tabWithUserAndSchemaDefaults( cUser CHAR(8) default user, cCurrent_user CHAR(8) default current_user, cSession_user CHAR(8) default session_user, cCurrent_schema CHAR(128) default current schema); 0 rows inserted/updated/deleted ij> -- Should work insert into tabWithUserAndSchemaDefaults values (default, default, default, default); 1 row inserted/updated/deleted ij> select * from tabWithUserAndSchemaDefaults; CUSER |CCURREN&|CSESSIO&|CCURRENT_SCHEMA ----------------------------------------------------------------------------------------------------------------------------------------------------------- APP |APP |APP |APP ij> -- Should fail: create table tabWithUserDefaultTooNarrowColumn( c1 CHAR(7) default user); ERROR 42894: DEFAULT value or IDENTITY attribute value is not valid for column 'C1'. ij> -- Should fail: create table tabWithSchemaDefaultTooNarrowColumn( c1 CHAR(127) default current sqlid); ERROR 42894: DEFAULT value or IDENTITY attribute value is not valid for column 'C1'. ij> drop table tabWithUserAndSchemaDefaults; 0 rows inserted/updated/deleted ij> -- end DERBY-3013 -- clean up drop function asdf; 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> drop table t7; 0 rows inserted/updated/deleted ij> drop table "x1"; 0 rows inserted/updated/deleted ij> drop table "otherschema"."y1"; 0 rows inserted/updated/deleted ij> drop schema "otherschema" restrict; 0 rows inserted/updated/deleted ij> drop table t_331; 0 rows inserted/updated/deleted ij>