coalescetests.java

derby database source code.good for you.

				System.out.println("FAIL - should have gotten error for incorrect syntax");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("42X04"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("TestA5a - create table with table name as coalesce and column name as coalesce will pass because coalesce is not a reserved-word");
			s.executeUpdate("create table coalesce (coalesce int, c12 int)");
			s.executeUpdate("insert into coalesce(coalesce) values(null)");
			s.executeUpdate("insert into coalesce values(null,1)");
			dumpRS(s.executeQuery("select coalesce(coalesce,c12) from coalesce"));
			s.executeUpdate("drop table coalesce");

			System.out.println("TestA5b - create table with table name as value and column name as value will pass because value is not a reserved-word");
			s.executeUpdate("create table value (value int, c12 int)");
			s.executeUpdate("insert into value(value) values(null)");
			s.executeUpdate("insert into value values(null,1)");
			dumpRS(s.executeQuery("select coalesce(value,c12) from value"));
			s.executeUpdate("drop table value");

			System.out.println("TestA6a - All arguments to coalesce function passed as parameters is an error");
			try {
				ps = conn.prepareStatement("select coalesce(?,?) from tA");
				System.out.println("FAIL - should have gotten error for using parameters for all the arguments");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("42610"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("TestA6b - All arguments to value function passed as parameters is an error");
			try {
				ps = conn.prepareStatement("select value(?,?) from tA");
				System.out.println("FAIL - should have gotten error for using parameters for all the arguments");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("42610"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

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

	public static void tablesForTestingAllDatatypesCombinations( Connection conn) throws Throwable
	{
		System.out.println("Set up by creating table for testing all datatypes combinations");

		Statement s = conn.createStatement();

		try {
			s.executeUpdate("DROP TABLE AllDataTypesTable");
		}
		catch(SQLException se) {}

		StringBuffer createSQL = new StringBuffer("create table AllDataTypesTable (");
		for (int type = 0; type < SQLTypes.length - 1; type++)
		{
			createSQL.append(ColumnNames[type] + " " + SQLTypes[type] + ",");
		}
		createSQL.append(ColumnNames[SQLTypes.length - 1] + " " + SQLTypes[SQLTypes.length - 1] + ")");
		System.out.println(createSQL);
		s.executeUpdate(createSQL.toString());

		for (int row = 0; row < SQLData[0].length; row++)
		{
			createSQL = new StringBuffer("insert into AllDataTypesTable values(");
			for (int type = 0; type < SQLTypes.length - 1; type++)
			{
				createSQL.append(SQLData[type][row] + ",");
			}
			createSQL.append(SQLData[SQLTypes.length - 1][row]+")");
			System.out.println(createSQL);
			s.executeUpdate(createSQL.toString());
		}

		s.close();
		conn.commit();
	}

	public static void testAllDatatypesCombinations( Connection conn) throws Throwable
	{
		System.out.println("Start testing all datatypes combinations in COALESCE/VALUE function");

		Statement s = conn.createStatement();

		// Try COALESCE with 2 datatype combinations at a time
		for (int firstColumnType = 0; firstColumnType < SQLTypes.length; firstColumnType++) {
			for (int secondColumnType = 0; secondColumnType < SQLTypes.length; secondColumnType++) {
				try {
					String coalesceString = "SELECT COALESCE(" + ColumnNames[firstColumnType] + "," + ColumnNames[secondColumnType] + ") from AllDataTypesTable";
					System.out.println(coalesceString);
					printExpectedResultDataType(firstColumnType,secondColumnType);
					dumpRS(s.executeQuery(coalesceString));
					isSupportedCoalesce(firstColumnType,secondColumnType, true);
				} catch (SQLException e)
				{
					if (e.getSQLState().equals("22007"))
						System.out.println("expected exception because char value does not match a time/timestamp format " + e.getMessage());
					else if (!isSupportedCoalesce(firstColumnType,secondColumnType, false)  && e.getSQLState().equals("42815"))
						System.out.println("expected exception " + e.getMessage());
					else
						dumpSQLExceptions(e);
				}
				try {
					String valueString = "SELECT VALUE(" + ColumnNames[firstColumnType] + "," + ColumnNames[secondColumnType] + ") from AllDataTypesTable";
					System.out.println(valueString);
					printExpectedResultDataType(firstColumnType,secondColumnType);
					dumpRS(s.executeQuery(valueString));
					isSupportedCoalesce(firstColumnType,secondColumnType, true);
				} catch (SQLException e)
				{
					if (e.getSQLState().equals("22007"))
						System.out.println("expected exception because char value does not match a time/timestamp format " + e.getMessage());
					else if (!isSupportedCoalesce(firstColumnType,secondColumnType, false)  && e.getSQLState().equals("42815"))
						System.out.println("expected exception " + e.getMessage());
					else
						dumpSQLExceptions(e);
				}
			}
		}
	}

	public static void testCompatibleDatatypesCombinations( Connection conn) throws Throwable
	{
		System.out.println("Start testing all compatible datatypes combinations in COALESCE/VALUE function");

		Statement s = conn.createStatement();

		for (int firstColumnType = 0; firstColumnType < SQLTypes.length; firstColumnType++) {
			StringBuffer coalesceString = new StringBuffer("SELECT COALESCE(" + ColumnNames[firstColumnType]);
			for (int secondColumnType = 0; secondColumnType < SQLTypes.length; secondColumnType++) {
				try {
					if (resultDataTypeRulesTable[firstColumnType][secondColumnType].equals("ERROR"))
						continue; //the datatypes are incompatible, don't try them in COALESCE/VALUE
					coalesceString.append("," + ColumnNames[secondColumnType]);
					System.out.println(coalesceString + ") from AllDataTypesTable");
					dumpRS(s.executeQuery(coalesceString + ") from AllDataTypesTable"));
				} catch (SQLException e)
				{
					if (e.getSQLState().equals("22007"))
						System.out.println("expected exception because char value does not match a time/timestamp format " + e.getMessage());
					else if (isClobWithCharAndDateTypeArguments(coalesceString.toString())  && e.getSQLState().equals("42815"))
						System.out.println("expected exception because mixing CLOB and DATA/TIME/TIMESTAMP arugments " + e.getMessage());
					else if (!isSupportedCoalesce(firstColumnType,secondColumnType, false)  && e.getSQLState().equals("42815"))
						System.out.println("expected exception " + e.getMessage());
					else
						dumpSQLExceptions(e);
				}
			}
		}
	}

	private static void printExpectedResultDataType(int oneType, int anotherType)
	{
		String coalesceDescription;
		if (resultDataTypeRulesTable[oneType][anotherType].equals("ERROR"))
		{
			coalesceDescription = " Operands " +
			SQLTypes[oneType] +
			" , " + SQLTypes[anotherType] + " are incompatible for Coalesce/Value function";
		}
		else
		{
			coalesceDescription = " Coalesc/Value with operands " +
			SQLTypes[oneType] +
			" , " + SQLTypes[anotherType] + " will have result data type of " + resultDataTypeRulesTable[oneType][anotherType];
		}
		System.out.println(coalesceDescription);
	}

	public static boolean isClobWithCharAndDateTypeArguments(String coalesceString) throws Throwable
	{
		if(coalesceString.indexOf("CLOB") != -1)
		{
			if(coalesceString.indexOf("CHAR") != -1 && (coalesceString.indexOf("DATE") != -1 || coalesceString.indexOf("TIME") != -1))
					return true;
		}
		return false;
	}

	private static boolean isSupportedCoalesce(int oneType, int anotherType, boolean throwError)
	{
		String coalesceDescription = " Coalesc/Value with operands " +
			SQLTypes[oneType] +
			" , " + SQLTypes[anotherType];

		if (throwError && resultDataTypeRulesTable[oneType][anotherType].equals("ERROR"))
				System.out.println("FAIL:" +  coalesceDescription +
							   " should not be supported");

		return (!(resultDataTypeRulesTable[oneType][anotherType].equals("ERROR")));
	}

	public static void testMiscellaneousCoalesce( Connection conn) throws Throwable
	{
    try {
			Statement s = conn.createStatement();
			PreparedStatement ps;
    try {
			s.executeUpdate("drop table tD");
    } catch(Exception ex) {}
			s.executeUpdate("create table tD (c1 int, c2 char(254))");
			s.executeUpdate("insert into tD (c1,c2) values(1,'abcdefgh')");
			s.executeUpdate("insert into tD (c1) values(2)");

			System.out.println("TestD - some miscellaneous testing for Coalesce/Value function");

			System.out.println("TestD1a - test coalesce function in values clause");
			dumpRS(s.executeQuery("values coalesce(cast('asdfghj' as char(30)),cast('asdf' as char(50)))"));

			System.out.println("TestD1b - test value function in values clause");
			dumpRS(s.executeQuery("values value(cast('asdfghj' as char(30)),cast('asdf' as char(50)))"));

			System.out.println("TestD2a - First argument to coalesce function passed as parameter with non-null value");
			ps = conn.prepareStatement("select coalesce(?,c2) from tD");
			ps.setString(1,"first argument to coalesce");
			dumpRS(ps.executeQuery());

			System.out.println("TestD2b - First argument to value function passed as parameter with non-null value");
			ps = conn.prepareStatement("select value(?,c2) from tD");
			ps.setString(1,"first argument to value");
			dumpRS(ps.executeQuery());

			System.out.println("TestD3a - First argument to coalesce function passed as parameter with null value");
			ps = conn.prepareStatement("select coalesce(?,c2) from tD");
			ps.setNull(1,Types.CHAR);
			dumpRS(ps.executeQuery());

			System.out.println("TestD3b - First argument to value function passed as parameter with null value");
			ps = conn.prepareStatement("select value(?,c2) from tD");
			ps.setNull(1,Types.BIGINT);
			dumpRS(ps.executeQuery());

			System.out.println("TestD4a - Pass incompatible value for parameter to coalesce function");
			ps = conn.prepareStatement("select coalesce(c1,?) from tD");
			try {
				ps.setString(1,"abc");
				dumpRS(ps.executeQuery());
				System.out.println("FAIL - should have gotten error because result type is int and we are trying to pass a parameter of type char");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("22018"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

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

	public static void testDateCoalesce( Connection conn) throws Throwable
	{
    try {
			Statement s = conn.createStatement();