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. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. 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. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- subquery in default create table neg(c1 int default (values 1)); ERROR 42X01: Syntax error: Encountered "(" at line 2, column 33. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. 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. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. 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>