coalescetests.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.coalesceTests

   Copyright 2004 The Apache Software Foundation or its licensors, as applicable.

   Licensed 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.

 */

package org.apache.derbyTesting.functionTests.tests.lang;

import java.io.*;
import java.sql.*;

import org.apache.derby.tools.ij;

/**
 * Coalesce/Value tests for various datatypes
 * coalesce/value function takes arguments and returns the first argument that is not null.
 * The arguments are evaluated in the order in which they are specified, and the result of the
 * function is the first argument that is not null. The result can be null only if all the arguments
 * can be null. The selected argument is converted, if necessary, to the attributes of the result.
 */
public class coalesceTests
{

	private static String VALID_DATE_STRING = "'2000-01-01'";
	private static String VALID_TIME_STRING = "'15:30:20'";
	private static String VALID_TIMESTAMP_STRING = "'2000-01-01 15:30:20'";
	private static String NULL_VALUE="NULL";

	private static String[] SQLTypes =
	{
		"SMALLINT",
		"INTEGER",
		"BIGINT",
		"DECIMAL(10,5)",
		"REAL",
		"DOUBLE",
		"CHAR(60)",
		"VARCHAR(60)",
		"LONG VARCHAR",
		"CHAR(60) FOR BIT DATA",
		"VARCHAR(60) FOR BIT DATA",
		"LONG VARCHAR FOR BIT DATA",
		"CLOB(1k)",
		"DATE",
		"TIME",
		"TIMESTAMP",
		"BLOB(1k)",

	};

	private static String[] ColumnNames =
	{
		"SMALLINTCOL",
		"INTEGERCOL",
		"BIGINTCOL",
		"DECIMALCOL",
		"REALCOL",
		"DOUBLECOL",
		"CHARCOL",
		"VARCHARCOL",
		"LONGVARCHARCOL",
		"CHARFORBITCOL",
		"VARCHARFORBITCOL",
		"LVARCHARFORBITCOL",
		"CLOBCOL",
		"DATECOL",
		"TIMECOL",
		"TIMESTAMPCOL",
		"BLOBCOL",

	};

 private static String[][]SQLData =
	{
		{NULL_VALUE, "0","1","2"},       // SMALLINT
		{NULL_VALUE,"11","111",NULL_VALUE},       // INTEGER
		{NULL_VALUE,"22","222","3333"},       // BIGINT
		{NULL_VALUE,"3.3","3.33",NULL_VALUE},      // DECIMAL(10,5)
		{NULL_VALUE,"4.4","4.44","4.444"},      // REAL,
		{NULL_VALUE,"5.5","5.55",NULL_VALUE},      // DOUBLE
		{NULL_VALUE,"'1992-01-06'","'1992-01-16'",NULL_VALUE},      // CHAR(60)
		{NULL_VALUE,"'1992-01-07'","'1992-01-17'",VALID_TIME_STRING},      //VARCHAR(60)",
		{NULL_VALUE,"'1992-01-08'","'1992-01-18'",VALID_TIMESTAMP_STRING},      // LONG VARCHAR
		{NULL_VALUE,"X'10aa'",NULL_VALUE,"X'10aaaa'"},  // CHAR(60)  FOR BIT DATA
		{NULL_VALUE,"X'10bb'",NULL_VALUE,"X'10bbbb'"},  // VARCHAR(60) FOR BIT DATA
		{NULL_VALUE,"X'10cc'",NULL_VALUE,"X'10cccc'"},  //LONG VARCHAR FOR BIT DATA
		{NULL_VALUE,"'13'","'14'",NULL_VALUE},     //CLOB(1k)
		{NULL_VALUE,VALID_DATE_STRING,VALID_DATE_STRING,NULL_VALUE},        // DATE
		{NULL_VALUE,VALID_TIME_STRING,VALID_TIME_STRING,NULL_VALUE},        // TIME
		{NULL_VALUE,VALID_TIMESTAMP_STRING,VALID_TIMESTAMP_STRING,NULL_VALUE},   // TIMESTAMP
		{NULL_VALUE,NULL_VALUE,NULL_VALUE,NULL_VALUE}                 // BLOB
	};

	/**
	   SQL Reference Guide for DB2 has section titled "Rules for result data types" at the following url
	   http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0008480.htm

	   I have constructed following table based on various tables and information under "Rules for result data types"
	   This table has FOR BIT DATA TYPES broken out into separate columns for clarity and testing
	**/


	public static final String[][]  resultDataTypeRulesTable = {

  // Types.             S  I  B  D  R  D  C  V  L  C  V  L  C  D  T  T  B
	//                    M  N  I  E  E  O  H  A  O  H  A  O  L  A  I  I  L
	//                    A  T  G  C  A  U  A  R  N  A  R  N  O  T  M  M  O
  //                    L  E  I  I  L  B  R  C  G  R  C  G  B  E  E  E  B
  //                    L  G  N  M     L     H  V  .  H  V           S
  //                    I  E  T  A     E     A  A  B  A  A           T
  //                    N  R     L           R  R  I  R  R           A
	//                    T                       C  T  .  .           M
  //                                            H     B  B           P
	//                                            A     I  I
	//                                            R     T   T
/* 0 SMALLINT */        { "SMALLINT", "INTEGER", "BIGINT", "DECIMAL", "DOUBLE", "DOUBLE", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
/* 1 INTEGER  */        { "INTEGER", "INTEGER", "BIGINT", "DECIMAL", "DOUBLE", "DOUBLE", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
/* 2 BIGINT   */        { "BIGINT", "BIGINT", "BIGINT", "DECIMAL", "DOUBLE", "DOUBLE", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
/* 3 DECIMAL  */        { "DECIMAL", "DECIMAL", "DECIMAL", "DECIMAL", "DOUBLE", "DOUBLE", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
/* 4 REAL     */        { "DOUBLE", "DOUBLE", "DOUBLE", "DOUBLE", "REAL", "DOUBLE", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
/* 5 DOUBLE   */        { "DOUBLE", "DOUBLE", "DOUBLE", "DOUBLE", "DOUBLE", "DOUBLE", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
/* 6 CHAR     */        { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "CHAR", "VARCHAR", "LONG VARCHAR", "ERROR", "ERROR", "ERROR", "CLOB", "DATE", "TIME", "TIMESTAMP", "ERROR" },
/* 7 VARCHAR  */        { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "VARCHAR", "VARCHAR","LONG VARCHAR", "ERROR", "ERROR", "ERROR", "CLOB", "DATE", "TIME", "TIMESTAMP", "ERROR" },
/* 8 LONGVARCHAR */     { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "LONG VARCHAR", "LONG VARCHAR", "LONG VARCHAR", "ERROR", "ERROR", "ERROR", "CLOB", "ERROR", "ERROR", "ERROR", "ERROR" },
/* 9 CHAR FOR BIT */    { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "CHAR () FOR BIT DATA", "VARCHAR () FOR BIT DATA", "LONG VARCHAR FOR BIT DATA", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
/* 10 VARCH. BIT   */   { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "VARCHAR () FOR BIT DATA", "VARCHAR () FOR BIT DATA", "LONG VARCHAR FOR BIT DATA", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
/* 11 LONGVAR. BIT */   { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "LONG VARCHAR FOR BIT DATA", "LONG VARCHAR FOR BIT DATA", "LONG VARCHAR FOR BIT DATA", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR" },
/* 12 CLOB         */   { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "CLOB", "CLOB", "CLOB", "ERROR", "ERROR", "ERROR", "CLOB", "ERROR", "ERROR", "ERROR", "ERROR" },
/* 13 DATE         */   { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "DATE", "DATE", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "DATE", "ERROR", "ERROR", "ERROR" },
/* 14 TIME         */   { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "TIME", "TIME", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "TIME", "ERROR", "ERROR" },
/* 15 TIMESTAMP    */   { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "TIMESTAMP", "TIMESTAMP", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "TIMESTAMP", "ERROR" },
/* 16 BLOB         */   { "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "ERROR", "BLOB" },

	};                                                                         

	public static void main (String[] argv) throws Throwable
	{
		ij.getPropertyArg(argv);
		Connection conn = ij.startJBMS();

		testCoalesceSyntax(conn);

		tablesForTestingAllDatatypesCombinations(conn);
		testCompatibleDatatypesCombinations(conn);
		testAllDatatypesCombinations(conn);

		testDateCoalesce(conn);
		testTimeCoalesce(conn);
		testTimeStampCoalesce(conn);
		testNumericCoalesce(conn);
		testMiscellaneousCoalesce(conn);
		testCharCoalesce(conn);
		testCharForBitDataCoalesce(conn);
	}

	public static void testCoalesceSyntax( Connection conn) throws Throwable
	{
    try {
			System.out.println("TestA - some syntax testing for Coalesce/Value function");

			PreparedStatement ps;
			Statement s = conn.createStatement();
			try {
			s.executeUpdate("drop table tA");
			} catch(Exception ex) {}
			s.executeUpdate("create table tA (c1 int, c2 char(254))");
			s.executeUpdate("insert into tA (c1) values(1)");

			System.out.println("TestAla - select coalesce from tA will give error because no arguments were supplied to the function");
			try {
				s.executeQuery("select coalesce from tA");
				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("TestAlb - select value from tA will give error because no arguments were supplied to the function");
			try {
				s.executeQuery("select value from tA");
				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("TestA2a - select coalesce from tA will give error because no arguments were supplied inside the parentheses");
			try {
				s.executeQuery("select coalesce() from tA");
				System.out.println("FAIL - should have gotten error for incorrect syntax");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("42X01"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("TestA2b - select value from tA will give error because no arguments were supplied inside the parentheses");
			try {
				s.executeQuery("select value() from tA");
				System.out.println("FAIL - should have gotten error for incorrect syntax");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("42X01"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("TestA3a - select coalesce from tA with only one argument will give error");
			try {
				s.executeQuery("select coalesce(c1) from tA");
				System.out.println("FAIL - should have gotten error for incorrect syntax");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("42605"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("TestA3b - select value from tA with only one argument will give error");
			try {
				s.executeQuery("select value(c1) from tA");
				System.out.println("FAIL - should have gotten error for incorrect syntax");
			}
			catch (SQLException e) {
				if (e.getSQLState().equals("42605"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			System.out.println("TestA4a - select coalesce from tA with incorrect column name will give error");
			try {
				s.executeQuery("select coalesce(c111) from tA");
				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("TestA4b - select value from tA with incorrect column name will give error");
			try {
				s.executeQuery("select value(c111) from tA");