coalescetests.java

derby database source code.good for you.

			try {
				System.out.println("TestG7b - value(varcharCol,timeCol) will fail because one row has invalid string representation of time in the varchar column");
				dumpRSwithScale(s.executeQuery("select value(charCol,timeCol) from tG"));
				System.out.println("TestG7b - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("22007"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("TestG - Following will work fine with invalid string representation of time because timeCol is not null and hence we don't look at invalid time string in char/varchar columns");
			System.out.println("TestG8a - coalesce(timeCol,charCol) will pass because timeCol is non-null for all rows in table TG and hence we don't look at charCol's invalid time string");
			dumpRSwithScale(s.executeQuery("select coalesce(timeCol,charCol) from tG"));

			System.out.println("TestG8b - value(timeCol,charCol) will pass because timeCol is non-null for all rows in table TG and hence we don't look at charCol's invalid time string");
			dumpRSwithScale(s.executeQuery("select coalesce(timeCol,charCol) from tG"));

			System.out.println("TestG9a - coalesce(timeCol,varcharCol) will pass because timeCol is non-null for all rows in table TG and hence we don't look at varcharCol's invalid time string");
			dumpRSwithScale(s.executeQuery("select coalesce(timeCol,varcharCol) from tG"));

			System.out.println("TestG9b - value(timeCol,varcharCol) will pass because timeCol is non-null for all rows in table TG and hence we don't look at varcharCol's invalid time string");
			dumpRSwithScale(s.executeQuery("select coalesce(timeCol,varcharCol) from tG"));

			s.executeUpdate("drop table tG");
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
	}

	public static void testNumericCoalesce( Connection conn) throws Throwable
	{
    try {
			Statement s = conn.createStatement();
			PreparedStatement ps;
    try {
			s.executeUpdate("drop table tE");
    } catch(Exception ex) {}
			s.executeUpdate("create table tE (smallintCol smallint, intCol integer, bigintCol bigint, decimalCol1 decimal(22,2), decimalCol2 decimal(8,6), decimalCol3 decimal(31,28), realCol real, doubleCol double)");
			s.executeUpdate("insert into tE values(1, 2, 3, 4, 5.5, 6.6, 7.7, 3.4028235E38)");
			s.executeUpdate("insert into tE values(null,null,null,null,null,null,null,null)");

			System.out.println("TestE - focus on smallint datatypes");
			System.out.println("TestE1 - coalesce(smallintCol,smallintCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,smallintCol) from tE"));

			System.out.println("TestE1a - coalesce(smallintCol,intCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,intCol) from tE"));

			System.out.println("TestE1b - coalesce(smallintCol,bigintCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,bigintCol) from tE"));

			System.out.println("TestE1c - coalesce(SMALLINT,DECIMAL) with decimal(w,x) will give result decimal(p,x) where p=x+max(w-x,5) and if that gives p>31, then p is set to 31");
			System.out.println("TestE1c1 - coalesce(smallintCol,decimalCol1) with decimal(22,2) will give result decimal(22,2)");
			dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,decimalCol1) from tE"));

			System.out.println("TestE1c2 - coalesce(smallintCol,decimalCol2) with decimal(8,6) will give result decimal(11,6)");
			dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,decimalCol2) from tE"));

			System.out.println("TestE1c3 - coalesce(smallintCol,decimalCol3) with decimal(31,28) will give result decimal(31,28) rather than giving error for precision  > 31");
			dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,decimalCol3) from tE"));

			System.out.println("TestE1d - coalesce(smallintCol,realCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,realCol) from tE"));

			System.out.println("TestE1e - coalesce(smallintCol,doubleCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(smallintCol,doubleCol) from tE"));

			System.out.println("TestE - focus on int datatypes");
			System.out.println("TestE1 - coalesce(intCol,intCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(intCol,intCol) from tE"));

			System.out.println("TestE1f - coalesce(intCol,smallintCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(intCol,smallintCol) from tE"));

			System.out.println("TestE1g - coalesce(intCol,bigintCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(intCol,bigintCol) from tE"));

			System.out.println("TestE1h - coalesce(INT,DECIMAL) with decimal(w,x) will give result decimal(p,x) where p=x+max(w-x,11) and if that gives p>31, then p is set to 31");
			System.out.println("TestE1h1 - coalesce(intCol,decimalCol1) with decimal(22,2) will give result decimal(22,2)");
			dumpRSwithScale(s.executeQuery("select coalesce(intCol,decimalCol1) from tE"));

			System.out.println("TestE1h2 - coalesce(intCol,decimalCol2) with decimal(8,6) will give result decimal(17,6)");
			dumpRSwithScale(s.executeQuery("select coalesce(intCol,decimalCol2) from tE"));

			System.out.println("TestE1h3 - coalesce(intCol,decimalCol3) with decimal(31,28) will give result decimal(31,28) rather than giving error for precision  > 31");
			dumpRSwithScale(s.executeQuery("select coalesce(intCol,decimalCol3) from tE"));

			System.out.println("TestE1i - coalesce(intCol,realCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(intCol,realCol) from tE"));

			System.out.println("TestE1j - coalesce(intCol,doubleCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(intCol,doubleCol) from tE"));

			System.out.println("TestE - focus on bigint datatypes");
			System.out.println("TestE1 - coalesce(bigintCol,bigintCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,bigintCol) from tE"));

			System.out.println("TestE1k - coalesce(bigintCol,smallintCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,smallintCol) from tE"));

			System.out.println("TestE1l - coalesce(bigintCol,intCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,intCol) from tE"));

			System.out.println("TestE1m - coalesce(BIGINT,DECIMAL) with decimal(w,x) will give result decimal(p,x) where p=x+max(w-x,19) and if that gives p>31, then p is set to 31");
			System.out.println("TestE1m1 - coalesce(bigintCol,decimalCol1) with decimal(22,2) will give result decimal(22,2)");
			dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,decimalCol1) from tE"));

			System.out.println("TestE1m2 - coalesce(bigintCol,decimalCol2) with decimal(8,6) will give result decimal(21,6)");
			dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,decimalCol2) from tE"));

			System.out.println("TestE1m3 - coalesce(bigintCol,decimalCol3) with decimal(31,28) will give result decimal(31,28) rather than giving error for precision  > 31");
			dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,decimalCol3) from tE"));

			System.out.println("TestE1n - coalesce(bigintCol,realCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,realCol) from tE"));

			System.out.println("TestE1o - coalesce(bigintCol,doubleCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(bigintCol,doubleCol) from tE"));

			System.out.println("TestE - focus on decimal datatypes");

			System.out.println("TestE1 - coalesce(DECIMAL,DECIMAL) with decimal(w,x), decimal(y,z) will give result decimal(p,s)");
			System.out.println("  where p=max(x,z)+max(w-x,y-z), s=max(x,z) and if that gives p>31, then p is set to 31");
			System.out.println("TestE11 - coalesce(decimalCol1,decimalCol1) with decimal(22,2) will give result decimal(22,2)");
			dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,decimalCol1) from tE"));

			System.out.println("TestE12 - coalesce(decimalCol1,decimalCol2) with decimal(22,2) and decimal(8,6) will give result decimal(26,6)");
			dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,decimalCol2) from tE"));

			System.out.println("TestE13 - coalesce(decimalCol1,decimalCol3) with decimal(22,2) and decimal(31,28) will give result decimal(31,28) rather than giving error for precision  > 31");
			dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,decimalCol3) from tE"));

			System.out.println("TestE1p - coalesce(decimalCol1,smallintCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,smallintCol) from tE"));

			System.out.println("TestE1q - coalesce(decimalCol1,intCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,intCol) from tE"));

			System.out.println("TestE1r - coalesce(decimalCol1,bigintCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,bigintCol) from tE"));

			System.out.println("TestE1s - coalesce(decimalCol1,realCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,realCol) from tE"));

			System.out.println("TestE1t - coalesce(decimalCol1,doubleCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(decimalCol1,doubleCol) from tE"));

			System.out.println("TestE - focus on real datatypes");
			System.out.println("TestE1 - coalesce(realCol,realCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(realCol,realCol) from tE"));

			System.out.println("TestE1u - coalesce(realCol,smallintCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(realCol,smallintCol) from tE"));

			System.out.println("TestE1v - coalesce(realCol,intCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(realCol,intCol) from tE"));

			System.out.println("TestE1w - coalesce(realCol,bigintCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(realCol,bigintCol) from tE"));

			System.out.println("TestE1x - coalesce(realCol,decimalCol1)");
			dumpRSwithScale(s.executeQuery("select coalesce(realCol,decimalCol1) from tE"));

			System.out.println("TestE1y - coalesce(realCol,doubleCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(realCol,doubleCol) from tE"));

			System.out.println("TestE - focus on double datatypes");
			System.out.println("TestE1 - coalesce(doubleCol,doubleCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(doubleCol,doubleCol) from tE"));

			System.out.println("TestE1z - coalesce(doubleCol,smallintCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(doubleCol,smallintCol) from tE"));

			System.out.println("TestE2a - coalesce(doubleCol,intCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(doubleCol,intCol) from tE"));

			System.out.println("TestE2b - coalesce(doubleCol,bigintCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(doubleCol,bigintCol) from tE"));

			System.out.println("TestE2c - coalesce(doubleCol,decimalCol1)");
			dumpRSwithScale(s.executeQuery("select coalesce(doubleCol,decimalCol1) from tE"));

			System.out.println("TestE2d - coalesce(doubleCol,realCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(doubleCol,realCol) from tE"));

			s.executeUpdate("drop table tE");
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
	}

	public static void testCharCoalesce( Connection conn) throws Throwable
	{
    try {
			Statement s = conn.createStatement();
			PreparedStatement ps;
    try {
			s.executeUpdate("drop table tB");
    } catch(Exception ex) {}
			s.executeUpdate("create table tB (c1 char(254), c2 char(40), vc1 varchar(253), vc2 varchar(2000), lvc1 long varchar, lvc2 long varchar, clob1 CLOB(200), clob2 CLOB(33K))");
			s.executeUpdate("insert into tB values('c1 not null', 'c2 not null', 'vc1 not null', 'vc2 not null', 'lvc1 not null', 'lvc2 not null', 'clob1 not null', 'clob2 not null')");
			s.executeUpdate("insert into tB values('c1 not null but c2 is', null, 'vc1 is not null but vc2 is', null, null, null,null,null)");
			s.executeUpdate("insert into tB values(null,'c2 not null but c1 is', null, 'vc2 is not null but vc1 is', 'lvc1 not null again', 'lvc2 not null again', 'clob1 not null again', 'clob2 not null again')");
			s.executeUpdate("insert into tB values(null,null, null, null, null, null, null, null)");

			System.out.println("TestB - Focus on CHAR as atleast one of the operands");
			System.out.println("TestB1a - 2 CHAR operands coalesce(c1,c2) with c1(254) and c2(40)");
			dumpRS(s.executeQuery("select coalesce(c1,c2) from tB"));

			System.out.println("TestB1b - 2 CHAR operands value(c1,c2) with c1(254) and c2(40)");
			dumpRS(s.executeQuery("select value(c1,c2) from tB"));

			System.out.println("TestB2a - 2 CHAR operands coalesce(c2,c1) with c2(40) and c1(254)");
			dumpRS(s.executeQuery("select coalesce(c2,c1) from tB"));

			System.out.println("TestB2b - 2 CHAR operands value(c2,c1) with c2(40) and c1(254)");
			dumpRS(s.executeQuery("select value(c2,c1) from tB"));

			System.out.println("TestB3a - CHAR and VARCHAR operands coalesce(c1,vc1) with c1(254) and vc1(253)");
			dumpRS(s.executeQuery("select coalesce(c1,vc1) from tB"));

			System.out.println("TestB3b - CHAR and VARCHAR operands value(c1,vc1) with c1(254) and vc1(253)");
			dumpRS(s.executeQuery("select value(c1,vc1) from tB"));

			System.out.println("TestB4a - VARCHAR and CHAR operands coalesce(vc1,c1) with vc1(253) and c1(254)");
			dumpRS(s.executeQuery("select coalesce(vc1,c1) from tB"));

			System.out.println("TestB4b - VARCHAR AND CHAR operands value(vc1,c1) with vc1(253) and c1(254)");
			dumpRS(s.executeQuery("select value(vc1,c1) from tB"));

			System.out.println("TestB - Focus on VARCHAR as atleast one of the operands");
			System.out.println("TestB5a - 2 VARCHAR operands coalesce(vc1,vc2) with vc1(253) and vc2(2000)");
			dumpRS(s.executeQuery("select coalesce(vc1,vc2) from tB"));

			System.out.println("TestB5b - 2 VARCHAR operands value(vc1,vc2) with vc1(253) and vc2(2000)");
			dumpRS(s.executeQuery("select value(vc1,vc2) from tB"));

			System.out.println("TestB6a - 2 VARCHAR operands coalesce(vc2,vc1) with vc2(2000) and vc1(253)");
			dumpRS(s.executeQuery("select coalesce(vc2,vc1) from tB"));

			System.out.println("TestB6b - 2 VARCHAR operands value(vc2,vc1) with vc2(2000) and vc1(253)");
			dumpRS(s.executeQuery("select value(vc2,vc1) from tB"));

			System.out.println("TestB - Focus on LONG VARCHAR as atleast one of the operands");
			System.out.println("TestB7a - CHAR and LONG VARCHAR operands coalesce(c1,lvc1) with c1(254)");
			dumpRS(s.executeQuery("select coalesce(c1,lvc1) from tB"));

			System.out.println("TestB7b - CHAR and LONG VARCHAR operands value(c1,lvc1) with c1(254)");
			dumpRS(s.executeQuery("select value(c1,lvc1) from tB"));

			System.out.println("TestB8a - LONG VARCHAR and CHAR operands coalesce(lvc1,c1) with c1(254)");
			dumpRS(s.executeQuery("select coalesce(lvc1,c1) from tB"));

			System.out.println("TestB8b - LONG VARCHAR and CHAR operands value(lvc1,c1) with c1(254)");
			dumpRS(s.executeQuery("select value(lvc1,c1) from tB"));

			System.out.println("TestB9a - VARCHAR and LONG VARCHAR operands coalesce(vc1,lvc1) with vc1(253)");
			dumpRS(s.executeQuery("select coalesce(vc1,lvc1) from tB"));

			System.out.println("TestB9b - VARCHAR and LONG VARCHAR operands value(vc1,lvc1) with vc1(253)");
			dumpRS(s.executeQuery("select value(vc1,lvc1) from tB"));

			System.out.println("TestB10a - LONG VARCHAR and VARCHAR operands coalesce(lvc1,vc1) with vc1(253)");
			dumpRS(s.executeQuery("select coalesce(lvc1,vc1) from tB"));

			System.out.println("TestB10b - LONG VARCHAR and VARCHAR operands value(lvc1,vc1) with vc1(253)");