coalescetests.out

derby database source code.good for you.

TestA - some syntax testing for Coalesce/Value function
TestAla - select coalesce from tA will give error because no arguments were supplied to the function
expected exception Column 'COALESCE' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'COALESCE' is not a column in the target table.
TestAlb - select value from tA will give error because no arguments were supplied to the function
expected exception Column 'VALUE' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'VALUE' is not a column in the target table.
TestA2a - select coalesce from tA will give error because no arguments were supplied inside the parentheses
expected exception Syntax error: Encountered ")" at line 1, column 17.
TestA2b - select value from tA will give error because no arguments were supplied inside the parentheses
expected exception Syntax error: Encountered ")" at line 1, column 14.
TestA3a - select coalesce from tA with only one argument will give error
expected exception The number of arguments for function 'COALESCE' is incorrect.
TestA3b - select value from tA with only one argument will give error
expected exception The number of arguments for function 'VALUE' is incorrect.
TestA4a - select coalesce from tA with incorrect column name will give error
expected exception Column 'C111' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'C111' is not a column in the target table.
TestA4b - select value from tA with incorrect column name will give error
expected exception Column 'C111' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'C111' is not a column in the target table.
TestA5a - create table with table name as coalesce and column name as coalesce will pass because coalesce is not a reserved-word
	 COL1(datatype : INTEGER, precision : 10, scale : 0)
	 ---------------------------------------------------
	{null}
	{1}
TestA5b - create table with table name as value and column name as value will pass because value is not a reserved-word
	 COL1(datatype : INTEGER, precision : 10, scale : 0)
	 ---------------------------------------------------
	{null}
	{1}
TestA6a - All arguments to coalesce function passed as parameters is an error
expected exception All the arguments to the COALESCE/VALUE function cannot be parameters. The function needs at least one argument that is not a parameter.
TestA6b - All arguments to value function passed as parameters is an error
expected exception All the arguments to the COALESCE/VALUE function cannot be parameters. The function needs at least one argument that is not a parameter.
Set up by creating table for testing all datatypes combinations
create table AllDataTypesTable (SMALLINTCOL SMALLINT,INTEGERCOL INTEGER,BIGINTCOL BIGINT,DECIMALCOL DECIMAL(10,5),REALCOL REAL,DOUBLECOL DOUBLE,CHARCOL CHAR(60),VARCHARCOL VARCHAR(60),LONGVARCHARCOL LONG VARCHAR,CHARFORBITCOL CHAR(60) FOR BIT DATA,VARCHARFORBITCOL VARCHAR(60) FOR BIT DATA,LVARCHARFORBITCOL LONG VARCHAR FOR BIT DATA,CLOBCOL CLOB(1k),DATECOL DATE,TIMECOL TIME,TIMESTAMPCOL TIMESTAMP,BLOBCOL BLOB(1k))
insert into AllDataTypesTable values(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
insert into AllDataTypesTable values(0,11,22,3.3,4.4,5.5,'1992-01-06','1992-01-07','1992-01-08',X'10aa',X'10bb',X'10cc','13','2000-01-01','15:30:20','xxxxxxFILTERED-TIMESTAMPxxxxx,NULL)
insert into AllDataTypesTable values(1,111,222,3.33,4.44,5.55,'1992-01-16','1992-01-17','1992-01-18',NULL,NULL,NULL,'14','2000-01-01','15:30:20','xxxxxxFILTERED-TIMESTAMPxxxxx,NULL)
insert into AllDataTypesTable values(2,NULL,3333,NULL,4.444,NULL,NULL,'15:30:20','xxxxxxFILTERED-TIMESTAMPxxxxx,X'10aaaa',X'10bbbb',X'10cccc',NULL,NULL,NULL,NULL,NULL)
Start testing all compatible datatypes combinations in COALESCE/VALUE function
SELECT COALESCE(SMALLINTCOL,SMALLINTCOL) from AllDataTypesTable
	 COL1(datatype : SMALLINT, precision : 5, scale : 0)
	 ---------------------------------------------------
	{null}
	{0}
	{1}
	{2}
SELECT COALESCE(SMALLINTCOL,SMALLINTCOL,INTEGERCOL) from AllDataTypesTable
	 COL1(datatype : INTEGER, precision : 10, scale : 0)
	 ---------------------------------------------------
	{null}
	{0}
	{1}
	{2}
SELECT COALESCE(SMALLINTCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL) from AllDataTypesTable
	 COL1(datatype : BIGINT, precision : 19, scale : 0)
	 --------------------------------------------------
	{null}
	{0}
	{1}
	{2}
SELECT COALESCE(SMALLINTCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL) from AllDataTypesTable
	 COL1(datatype : DECIMAL, precision : 24, scale : 5)
	 ---------------------------------------------------
	{null}
	{0.00000}
	{1.00000}
	{2.00000}
SELECT COALESCE(SMALLINTCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL,REALCOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{0.0}
	{1.0}
	{2.0}
SELECT COALESCE(SMALLINTCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL,REALCOL,DOUBLECOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{0.0}
	{1.0}
	{2.0}
SELECT COALESCE(INTEGERCOL,SMALLINTCOL) from AllDataTypesTable
	 COL1(datatype : INTEGER, precision : 10, scale : 0)
	 ---------------------------------------------------
	{null}
	{11}
	{111}
	{2}
SELECT COALESCE(INTEGERCOL,SMALLINTCOL,INTEGERCOL) from AllDataTypesTable
	 COL1(datatype : INTEGER, precision : 10, scale : 0)
	 ---------------------------------------------------
	{null}
	{11}
	{111}
	{2}
SELECT COALESCE(INTEGERCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL) from AllDataTypesTable
	 COL1(datatype : BIGINT, precision : 19, scale : 0)
	 --------------------------------------------------
	{null}
	{11}
	{111}
	{2}
SELECT COALESCE(INTEGERCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL) from AllDataTypesTable
	 COL1(datatype : DECIMAL, precision : 24, scale : 5)
	 ---------------------------------------------------
	{null}
	{11.00000}
	{111.00000}
	{2.00000}
SELECT COALESCE(INTEGERCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL,REALCOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{11.0}
	{111.0}
	{2.0}
SELECT COALESCE(INTEGERCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL,REALCOL,DOUBLECOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{11.0}
	{111.0}
	{2.0}
SELECT COALESCE(BIGINTCOL,SMALLINTCOL) from AllDataTypesTable
	 COL1(datatype : BIGINT, precision : 19, scale : 0)
	 --------------------------------------------------
	{null}
	{22}
	{222}
	{3333}
SELECT COALESCE(BIGINTCOL,SMALLINTCOL,INTEGERCOL) from AllDataTypesTable
	 COL1(datatype : BIGINT, precision : 19, scale : 0)
	 --------------------------------------------------
	{null}
	{22}
	{222}
	{3333}
SELECT COALESCE(BIGINTCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL) from AllDataTypesTable
	 COL1(datatype : BIGINT, precision : 19, scale : 0)
	 --------------------------------------------------
	{null}
	{22}
	{222}
	{3333}
SELECT COALESCE(BIGINTCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL) from AllDataTypesTable
	 COL1(datatype : DECIMAL, precision : 24, scale : 5)
	 ---------------------------------------------------
	{null}
	{22.00000}
	{222.00000}
	{3333.00000}
SELECT COALESCE(BIGINTCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL,REALCOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{22.0}
	{222.0}
	{3333.0}
SELECT COALESCE(BIGINTCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL,REALCOL,DOUBLECOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{22.0}
	{222.0}
	{3333.0}
SELECT COALESCE(DECIMALCOL,SMALLINTCOL) from AllDataTypesTable
	 COL1(datatype : DECIMAL, precision : 10, scale : 5)
	 ---------------------------------------------------
	{null}
	{3.30000}
	{3.33000}
	{2.00000}
SELECT COALESCE(DECIMALCOL,SMALLINTCOL,INTEGERCOL) from AllDataTypesTable
	 COL1(datatype : DECIMAL, precision : 15, scale : 5)
	 ---------------------------------------------------
	{null}
	{3.30000}
	{3.33000}
	{2.00000}
SELECT COALESCE(DECIMALCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL) from AllDataTypesTable
	 COL1(datatype : DECIMAL, precision : 24, scale : 5)
	 ---------------------------------------------------
	{null}
	{3.30000}
	{3.33000}
	{2.00000}
SELECT COALESCE(DECIMALCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL) from AllDataTypesTable
	 COL1(datatype : DECIMAL, precision : 24, scale : 5)
	 ---------------------------------------------------
	{null}
	{3.30000}
	{3.33000}
	{2.00000}
SELECT COALESCE(DECIMALCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL,REALCOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{3.3}
	{3.33}
	{2.0}
SELECT COALESCE(DECIMALCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL,REALCOL,DOUBLECOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{3.3}
	{3.33}
	{2.0}
SELECT COALESCE(REALCOL,SMALLINTCOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{4.400000095367432}
	{4.440000057220459}
	{4.443999767303467}
SELECT COALESCE(REALCOL,SMALLINTCOL,INTEGERCOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{4.400000095367432}
	{4.440000057220459}
	{4.443999767303467}
SELECT COALESCE(REALCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{4.400000095367432}
	{4.440000057220459}
	{4.443999767303467}
SELECT COALESCE(REALCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{4.400000095367432}
	{4.440000057220459}
	{4.443999767303467}
SELECT COALESCE(REALCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL,REALCOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{4.400000095367432}
	{4.440000057220459}
	{4.443999767303467}
SELECT COALESCE(REALCOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL,REALCOL,DOUBLECOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{4.400000095367432}
	{4.440000057220459}
	{4.443999767303467}
SELECT COALESCE(DOUBLECOL,SMALLINTCOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{5.5}
	{5.55}
	{2.0}
SELECT COALESCE(DOUBLECOL,SMALLINTCOL,INTEGERCOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{5.5}
	{5.55}