db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rhille...@apache.org
Subject svn commit: r714186 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/derby/shared/common/reference/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTe...
Date Fri, 14 Nov 2008 23:23:34 GMT
Author: rhillegas
Date: Fri Nov 14 15:23:26 2008
New Revision: 714186

URL: http://svn.apache.org/viewvc?rev=714186&view=rev
Log:
DERBY-3923: Make datatype declaration optional when you specify a generation clause in a CREATE/ALTER TABLE statement.

Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
    db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
    db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java?rev=714186&r1=714185&r2=714186&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/AlterTableNode.java Fri Nov 14 15:23:26 2008
@@ -337,6 +337,14 @@
 					if (tableElementList.elementAt(i) instanceof ColumnDefinitionNode) {
 						ColumnDefinitionNode cdn = (ColumnDefinitionNode) tableElementList.elementAt(i);
 						//check if we are dealing with add character column
+                        //
+                        // For generated columns which omit an explicit
+                        // datatype, we have to defer this work until we bind
+                        // the generation clause
+                        //
+
+                        if ( cdn.hasGenerationClause() && ( cdn.getType() == null ) ) { continue; }
+                        
 						if (cdn.getType().getTypeId().isStringTypeId()) {
 							//we found what we are looking for. Set the 
 							//collation type of this column to be the same as
@@ -404,7 +412,7 @@
 			 */
 			if  (numCheckConstraints > 0) { tableElementList.bindAndValidateCheckConstraints(fromList); }
 			if  (numGenerationClauses > 0)
-            { tableElementList.bindAndValidateGenerationClauses(fromList, generatedColumns ); }
+            { tableElementList.bindAndValidateGenerationClauses( schemaDescriptor, fromList, generatedColumns ); }
             if ( numReferenceConstraints > 0) { tableElementList.validateForeignKeysOnGenerationClauses( fromList, generatedColumns ); }
 		}
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java?rev=714186&r1=714185&r2=714186&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ColumnDefinitionNode.java Fri Nov 14 15:23:26 2008
@@ -379,6 +379,10 @@
 	{
 		String			columnTypeName;
 
+        // continue if this is a generated column and the datatype has been
+        // omitted. we can't check generation clauses until later on
+        if ( hasGenerationClause() && (getType() == null ) ) { return; }
+
 		/* Built-in types need no checking */
 		if (!getType().getTypeId().userType())
 			return;
@@ -452,9 +456,9 @@
 		throws StandardException
 	{
 		/* DB2 requires non-nullable columns to have a default in ALTER TABLE */
-		if (td != null && !getType().isNullable() && defaultNode == null)
+		if (td != null && !hasGenerationClause() && !getType().isNullable() && defaultNode == null)
 		{
-			if (!isAutoincrement && !hasGenerationClause())
+			if (!isAutoincrement )
 				throw StandardException.newException(SQLState.LANG_DB2_NOT_NULL_COLUMN_INVALID_DEFAULT, getColumnName());
 		}
 			

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java?rev=714186&r1=714185&r2=714186&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CreateTableNode.java Fri Nov 14 15:23:26 2008
@@ -238,6 +238,9 @@
 		int numUniqueConstraints = 0;
         int numGenerationClauses = 0;
 
+        SchemaDescriptor sd = getSchemaDescriptor
+            ( tableType != TableDescriptor.GLOBAL_TEMPORARY_TABLE_TYPE, true);
+
 		if (queryExpression != null)
 		{
 			FromList fromList = (FromList) getNodeFactory().getNode(
@@ -292,10 +295,6 @@
 				qeRCL.copyResultColumnNames(resultColumns);
 			}
 			
-			SchemaDescriptor sd = getSchemaDescriptor(
-				tableType != TableDescriptor.GLOBAL_TEMPORARY_TABLE_TYPE,
-				true);
-
 			int schemaCollationType = sd.getCollationType();
 	    
 			/* Create table element list from columns in query expression */
@@ -440,7 +439,7 @@
 			 * the check constraints and generation clauses.
 			 */
 			if  (numCheckConstraints > 0) { tableElementList.bindAndValidateCheckConstraints(fromList); }
-			if  (numGenerationClauses > 0) { tableElementList.bindAndValidateGenerationClauses(fromList, generatedColumns ); }
+			if  (numGenerationClauses > 0) { tableElementList.bindAndValidateGenerationClauses( sd, fromList, generatedColumns ); }
             if ( numReferenceConstraints > 0) { tableElementList.validateForeignKeysOnGenerationClauses( fromList, generatedColumns ); }
 		}
 	}

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java?rev=714186&r1=714185&r2=714186&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TableElementList.java Fri Nov 14 15:23:26 2008
@@ -118,7 +118,9 @@
 	 * of the character string types in create table node
 	 * @param sd
 	 */
-	void setCollationTypesOnCharacterStringColumns(SchemaDescriptor sd) {
+	void setCollationTypesOnCharacterStringColumns(SchemaDescriptor sd)
+        throws StandardException
+    {
 		int			size = size();
 		int collationType = sd.getCollationType();
 		for (int index = 0; index < size; index++)
@@ -128,14 +130,47 @@
 			if (tableElement instanceof ColumnDefinitionNode)
 			{
 				ColumnDefinitionNode cdn = (ColumnDefinitionNode) elementAt(index);
-				if (cdn.getType().getTypeId().isStringTypeId()) {
-					cdn.setCollationType(collationType);
-				}
+
+                setCollationTypeOnCharacterStringColumn( sd, cdn );
 			}
 		}
 	}
 
 	/**
+	 * Use the passed schema descriptor's collation type to set the collation
+	 * of a character string column.
+	 * @param sd
+	 */
+	void setCollationTypeOnCharacterStringColumn(SchemaDescriptor sd, ColumnDefinitionNode cdn )
+        throws StandardException
+    {
+		int collationType = sd.getCollationType();
+
+        //
+        // Only generated columns can omit the datatype specification during the
+        // early phases of binding--before we have been able to bind the
+        // generation clause.
+        //
+        DataTypeDescriptor  dtd = cdn.getType();
+        if ( dtd == null )
+        {
+            if ( cdn.hasGenerationClause() )
+            {
+                return;
+            }
+            else
+            {
+                throw StandardException.newException
+                    ( SQLState.LANG_NEEDS_DATATYPE, cdn.getColumnName() );
+            }
+        }
+        else
+        {
+            if ( dtd.getTypeId().isStringTypeId() ) { cdn.setCollationType(collationType); }
+        }
+    }
+    
+	/**
 	 * Validate this TableElementList.  This includes checking for
 	 * duplicate columns names, and checking that user types really exist.
 	 *
@@ -675,12 +710,13 @@
 	 * Bind and validate all of the generation clauses in this list against
 	 * the specified FromList.  
 	 *
+	 * @param fromList		Schema where the table lives.
 	 * @param fromList		The FromList in question.
 	 * @param generatedColumns Bitmap of generated columns in the table. Vacuous for CREATE TABLE, but may be non-trivial for ALTER TABLE. This routine may set bits for new generated columns.
 	 *
 	 * @exception StandardException		Thrown on error
 	 */
-	void bindAndValidateGenerationClauses(FromList fromList, FormatableBitSet generatedColumns )
+	void bindAndValidateGenerationClauses( SchemaDescriptor sd, FromList fromList, FormatableBitSet generatedColumns )
 		throws StandardException
 	{
 		CompilerContext cc;
@@ -748,7 +784,19 @@
                 //
                 DataTypeDescriptor  generationClauseType = generationTree.getTypeServices();
                 DataTypeDescriptor  declaredType = cdn.getType();
-                if ( declaredType == null ) { cdn.setType( generationClauseType ); }
+                if ( declaredType == null )
+                {
+                    cdn.setType( generationClauseType );
+
+                    //
+                    // We skipped these steps earlier on because we didn't have
+                    // a datatype. Now that we have a datatype, revisit these
+                    // steps.
+                    //
+                    setCollationTypeOnCharacterStringColumn( sd, cdn );
+                    cdn.checkUserType( table.getTableDescriptor() );
+                }
+                else
                 {
                     TypeId  declaredTypeId = declaredType.getTypeId();
                     TypeId  resolvedTypeId = generationClauseType.getTypeId();

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=714186&r1=714185&r2=714186&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Fri Nov 14 15:23:26 2008
@@ -3647,8 +3647,7 @@
 
 	/* identifier() used to be columnName() */
 	columnName = identifier(Limits.MAX_IDENTIFIER_LENGTH, true) 
-		( typeDescriptor[0] = dataTypeDDL() 
-		)
+	[ ( typeDescriptor[0] = dataTypeDDL() ) ]
 	[ defaultNode = defaultAndConstraints(typeDescriptor, tableElementList, columnName, autoIncrementInfo) ]
 	{
 		// Only pass autoincrement info for autoincrement columns

Modified: db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml?rev=714186&r1=714185&r2=714186&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/loc/messages.xml Fri Nov 14 15:23:26 2008
@@ -2020,6 +2020,12 @@
             </msg>
 
             <msg>
+                <name>42XA9</name>
+                <text>Column '{0}' needs an explicit datatype. The datatype can be omitted only for columns with generation clauses.</text>
+                <arg>columnName</arg>
+            </msg>
+
+            <msg>
                 <name>42Y00</name>
                 <text>Class '{0}' does not implement org.apache.derby.iapi.db.AggregateDefinition and thus cannot be used as an aggregate expression.</text>
                 <arg>className</arg>

Modified: db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java?rev=714186&r1=714185&r2=714186&view=diff
==============================================================================
--- db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java (original)
+++ db/derby/code/trunk/java/shared/org/apache/derby/shared/common/reference/SQLState.java Fri Nov 14 15:23:26 2008
@@ -903,6 +903,7 @@
     String LANG_BAD_FK_ON_GENERATED_COLUMN                           = "42XA6";
     String LANG_GEN_COL_DEFAULT                                                 = "42XA7";
     String LANG_GEN_COL_BAD_RENAME                                           = "42XA8";
+    String LANG_NEEDS_DATATYPE                                                      = "42XA9";
 	String LANG_INVALID_USER_AGGREGATE_DEFINITION2                     = "42Y00";
 	String LANG_INVALID_CHECK_CONSTRAINT                               = "42Y01";
 	// String LANG_NO_ALTER_TABLE_COMPRESS_ON_TARGET_TABLE                = "42Y02";

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out?rev=714186&r1=714185&r2=714186&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/db2Compatibility.out Fri Nov 14 15:23:26 2008
@@ -178,22 +178,22 @@
 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> create table NOTYPE(i int, b TINYINT);
-ERROR 42X01: Syntax error: Encountered "" at line 1, column 30.
+ERROR 42X01: Syntax error: Encountered "TINYINT" at line 1, column 30.
 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> create table NOTYPE(i int, b java.lang.String);
-ERROR 42X01: Syntax error: Encountered "" at line 1, column 30.
+ERROR 42X01: Syntax error: Encountered "java" at line 1, column 30.
 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> create table NOTYPE(i int, b com.acme.Address);
-ERROR 42X01: Syntax error: Encountered "" at line 1, column 30.
+ERROR 42X01: Syntax error: Encountered "com" at line 1, column 30.
 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> create table NOTYPE(i int, b org.apache.derby.vti.VTIEnvironment);
-ERROR 42X01: Syntax error: Encountered "" at line 1, column 30.
+ERROR 42X01: Syntax error: Encountered "org" at line 1, column 30.
 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.
@@ -587,12 +587,12 @@
 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> create table mm (x org.apache.derbyTesting.functionTests.util.ManyMethods);
-ERROR 42X01: Syntax error: Encountered "" at line 1, column 20.
+ERROR 42X01: Syntax error: Encountered "org" at line 1, column 20.
 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> create table sc (x org.apache.derbyTesting.functionTests.util.SubClass);
-ERROR 42X01: Syntax error: Encountered "" at line 1, column 20.
+ERROR 42X01: Syntax error: Encountered "org" at line 1, column 20.
 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.
@@ -717,7 +717,7 @@
 ERROR 0A000: Feature not implemented: NATIONAL CHAR VARYING.
 ij> -- tinyint datatype already disabled
 create table testtype10(col1 TINYINT);
-ERROR 42X01: Syntax error: Encountered "" at line 2, column 30.
+ERROR 42X01: Syntax error: Encountered "TINYINT" at line 2, column 30.
 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.
@@ -735,7 +735,7 @@
 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> create table testtype13 (a Tour);
-ERROR 42X01: Syntax error: Encountered "" at line 1, column 28.
+ERROR 42X01: Syntax error: Encountered "Tour" at line 1, column 28.
 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.

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java?rev=714186&r1=714185&r2=714186&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsHelper.java Fri Nov 14 15:23:26 2008
@@ -65,6 +65,7 @@
     protected static  final   String  BAD_FOREIGN_KEY_ACTION = "42XA6";
     protected static  final   String  ILLEGAL_ADD_DEFAULT = "42XA7";
     protected static  final   String  ILLEGAL_RENAME = "42XA8";
+    protected static  final   String  NEED_EXPLICIT_DATATYPE = "42XA9";
     
     protected static  final   String  NOT_NULL_VIOLATION = "23502";
     protected static  final   String  CONSTRAINT_VIOLATION = "23513";
@@ -77,6 +78,7 @@
     protected static  final   String  LACK_TABLE_PRIV = "42500";
     protected static  final   String  LACK_COLUMN_PRIV = "42502";
     protected static  final   String  LACK_EXECUTE_PRIV = "42504";
+    protected static  final   String  CANT_ADD_IDENTITY = "42601";
     
     protected static  final   String  CASCADED_COLUMN_DROP_WARNING = "01009";
     protected static  final   String  CONSTRAINT_DROPPED_WARNING = "01500";
@@ -223,6 +225,30 @@
     }
 
     /**
+     * Assert that a table has the correct column types.
+     */
+    protected void assertColumnTypes( Connection conn, String tableName, String[][] columnTypes )
+        throws Exception
+    {
+        PreparedStatement   ps = chattyPrepare
+            (
+             conn,
+             "select c.columnname, c.columndatatype\n" +
+             "from sys.syscolumns c, sys.systables t\n" +
+             "where t.tablename = ?\n" +
+             "and t.tableid = c.referenceid\n" +
+             "order by c.columnname\n"
+             );
+        ps.setString( 1, tableName );
+        ResultSet                   rs = ps.executeQuery();
+
+        assertResults( rs, columnTypes, true );
+
+        rs.close();
+        ps.close();
+    }
+        
+    /**
      * Assert that the statement returns the correct results.
      */
     protected void assertResults( Connection conn, String query, String[][] rows, boolean trimResults )
@@ -255,6 +281,7 @@
             for ( int j = 0; j < columnCount; j++ )
             {
                 String  expectedValue =  row[ j ];
+                //println( "(row, column ) ( " + i + ", " +  j + " ) should be " + expectedValue );
                 String  actualValue = null;
                 int         column = j+1;
 

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java?rev=714186&r1=714185&r2=714186&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/GeneratedColumnsTest.java Fri Nov 14 15:23:26 2008
@@ -3329,6 +3329,848 @@
              );
     }
 
+    /**
+     * <p>
+     * Test that CREATE/ALTER TABLE can omit the column datatype if there is a
+     * generation clause.
+     * </p>
+     */
+    public  void    test_022_omitDatatype()
+        throws Exception
+    {
+        Connection  conn = getConnection();
+
+        //
+        // Verify basic ALTER TABLE without a column datatype
+        //
+        goodStatement
+            (
+             conn,
+             "create table t_nd_1( a int )"
+             );
+        goodStatement
+            (
+             conn,
+             "alter table t_nd_1 add b generated always as ( -a )"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_1( a ) values ( 1 )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_1",
+             new String[][]
+             {
+                 { "A", "INTEGER" },
+                 { "B", "INTEGER" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_1 order by a",
+             new String[][]
+             {
+                 { "1", "-1" },
+             },
+             false
+             );
+
+        //
+        // Verify that you can't omit the datatype for other types of columns.
+        //
+        expectCompilationError
+            (
+             NEED_EXPLICIT_DATATYPE,
+             "create table t_nd_2( a generated always as identity )"
+             );
+        expectCompilationError
+            (
+             CANT_ADD_IDENTITY,
+             "alter table t_nd_1 add c generated always as identity"
+             );
+
+        //
+        // Verify basic CREATE TABLE omitting datatype on generated column
+        //
+        goodStatement
+            (
+             conn,
+             "create table t_nd_3( a int, b generated always as ( -a ) )"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_3( a ) values ( 100 )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_3",
+             new String[][]
+             {
+                 { "A", "INTEGER" },
+                 { "B", "INTEGER" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_3 order by a",
+             new String[][]
+             {
+                 { "100", "-100" },
+             },
+             false
+             );
+
+        //
+        // Now verify various datatypes are correctly resolved.
+        //
+        goodStatement
+            (
+             conn,
+             "create table t_nd_smallint\n" +
+             "(\n" +
+             "   a smallint,\n" +
+             "   b generated always as ( cast ( -a  as smallint ) ),\n" +
+             "   c generated always as ( cast ( -a as int ) ),\n" +
+             "   d generated always as ( cast( -a as bigint ) ),\n" +
+             "   e generated always as ( cast ( -a as decimal ) ),\n" +
+             "   f generated always as ( cast ( -a as real ) ),\n" +
+             "   g generated always as ( cast ( -a as double ) ),\n" +
+             "   h generated always as ( cast ( -a as float ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_smallint( a ) values ( 1 )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_SMALLINT",
+             new String[][]
+             {
+                 { "A", "SMALLINT" },
+                 { "B", "SMALLINT" },
+                 { "C", "INTEGER" },
+                 { "D", "BIGINT" },
+                 { "E", "DECIMAL(5,0)" },
+                 { "F", "REAL" },
+                 { "G", "DOUBLE" },
+                 { "H", "DOUBLE" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_smallint order by a",
+             new String[][]
+             {
+                 { "1" , "-1", "-1", "-1", "-1", "-1.0", "-1.0", "-1.0" },
+             },
+             false
+             );
+        
+        goodStatement
+            (
+             conn,
+             "create table t_nd_int\n" +
+             "(\n" +
+             "   a int,\n" +
+             "   b generated always as ( cast ( -a  as smallint ) ),\n" +
+             "   c generated always as ( cast ( -a as int ) ),\n" +
+             "   d generated always as ( cast( -a as bigint ) ),\n" +
+             "   e generated always as ( cast ( -a as decimal ) ),\n" +
+             "   f generated always as ( cast ( -a as real ) ),\n" +
+             "   g generated always as ( cast ( -a as double ) ),\n" +
+             "   h generated always as ( cast ( -a as float ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_int( a ) values ( 1 )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_INT",
+             new String[][]
+             {
+                 { "A", "INTEGER" },
+                 { "B", "SMALLINT" },
+                 { "C", "INTEGER" },
+                 { "D", "BIGINT" },
+                 { "E", "DECIMAL(5,0)" },
+                 { "F", "REAL" },
+                 { "G", "DOUBLE" },
+                 { "H", "DOUBLE" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_int order by a",
+             new String[][]
+             {
+                 { "1" , "-1", "-1", "-1", "-1", "-1.0", "-1.0", "-1.0" },
+             },
+             false
+             );
+        
+        goodStatement
+            (
+             conn,
+             "create table t_nd_bigint\n" +
+             "(\n" +
+             "   a bigint,\n" +
+             "   b generated always as ( cast ( -a  as smallint ) ),\n" +
+             "   c generated always as ( cast ( -a as int ) ),\n" +
+             "   d generated always as ( cast( -a as bigint ) ),\n" +
+             "   e generated always as ( cast ( -a as decimal ) ),\n" +
+             "   f generated always as ( cast ( -a as real ) ),\n" +
+             "   g generated always as ( cast ( -a as double ) ),\n" +
+             "   h generated always as ( cast ( -a as float ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_bigint( a ) values ( 1 )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_BIGINT",
+             new String[][]
+             {
+                 { "A", "BIGINT" },
+                 { "B", "SMALLINT" },
+                 { "C", "INTEGER" },
+                 { "D", "BIGINT" },
+                 { "E", "DECIMAL(5,0)" },
+                 { "F", "REAL" },
+                 { "G", "DOUBLE" },
+                 { "H", "DOUBLE" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_bigint order by a",
+             new String[][]
+             {
+                 { "1" , "-1", "-1", "-1", "-1", "-1.0", "-1.0", "-1.0" },
+             },
+             false
+             );
+        
+        goodStatement
+            (
+             conn,
+             "create table t_nd_decimal\n" +
+             "(\n" +
+             "   a decimal,\n" +
+             "   b generated always as ( cast ( -a  as smallint ) ),\n" +
+             "   c generated always as ( cast ( -a as int ) ),\n" +
+             "   d generated always as ( cast( -a as bigint ) ),\n" +
+             "   e generated always as ( cast ( -a as decimal ) ),\n" +
+             "   f generated always as ( cast ( -a as real ) ),\n" +
+             "   g generated always as ( cast ( -a as double ) ),\n" +
+             "   h generated always as ( cast ( -a as float ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_decimal( a ) values ( 1.0 )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_DECIMAL",
+             new String[][]
+             {
+                 { "A", "DECIMAL(5,0)" },
+                 { "B", "SMALLINT" },
+                 { "C", "INTEGER" },
+                 { "D", "BIGINT" },
+                 { "E", "DECIMAL(5,0)" },
+                 { "F", "REAL" },
+                 { "G", "DOUBLE" },
+                 { "H", "DOUBLE" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_decimal order by a",
+             new String[][]
+             {
+                 { "1" , "-1", "-1", "-1", "-1", "-1.0", "-1.0", "-1.0" },
+             },
+             false
+             );
+        
+        goodStatement
+            (
+             conn,
+             "create table t_nd_real\n" +
+             "(\n" +
+             "   a real,\n" +
+             "   b generated always as ( cast ( -a  as smallint ) ),\n" +
+             "   c generated always as ( cast ( -a as int ) ),\n" +
+             "   d generated always as ( cast( -a as bigint ) ),\n" +
+             "   e generated always as ( cast ( -a as decimal ) ),\n" +
+             "   f generated always as ( cast ( -a as real ) ),\n" +
+             "   g generated always as ( cast ( -a as double ) ),\n" +
+             "   h generated always as ( cast ( -a as float ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_real( a ) values ( 1.0 )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_REAL",
+             new String[][]
+             {
+                 { "A", "REAL" },
+                 { "B", "SMALLINT" },
+                 { "C", "INTEGER" },
+                 { "D", "BIGINT" },
+                 { "E", "DECIMAL(5,0)" },
+                 { "F", "REAL" },
+                 { "G", "DOUBLE" },
+                 { "H", "DOUBLE" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_real order by a",
+             new String[][]
+             {
+                 { "1.0" , "-1", "-1", "-1", "-1", "-1.0", "-1.0", "-1.0" },
+             },
+             false
+             );
+        
+        goodStatement
+            (
+             conn,
+             "create table t_nd_double\n" +
+             "(\n" +
+             "   a double,\n" +
+             "   b generated always as ( cast ( -a  as smallint ) ),\n" +
+             "   c generated always as ( cast ( -a as int ) ),\n" +
+             "   d generated always as ( cast( -a as bigint ) ),\n" +
+             "   e generated always as ( cast ( -a as decimal ) ),\n" +
+             "   f generated always as ( cast ( -a as real ) ),\n" +
+             "   g generated always as ( cast ( -a as double ) ),\n" +
+             "   h generated always as ( cast ( -a as float ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_double( a ) values ( 1.0 )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_DOUBLE",
+             new String[][]
+             {
+                 { "A", "DOUBLE" },
+                 { "B", "SMALLINT" },
+                 { "C", "INTEGER" },
+                 { "D", "BIGINT" },
+                 { "E", "DECIMAL(5,0)" },
+                 { "F", "REAL" },
+                 { "G", "DOUBLE" },
+                 { "H", "DOUBLE" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_double order by a",
+             new String[][]
+             {
+                 { "1.0" , "-1", "-1", "-1", "-1", "-1.0", "-1.0", "-1.0" },
+             },
+             false
+             );
+        
+        goodStatement
+            (
+             conn,
+             "create table t_nd_float\n" +
+             "(\n" +
+             "   a float,\n" +
+             "   b generated always as ( cast ( -a  as smallint ) ),\n" +
+             "   c generated always as ( cast ( -a as int ) ),\n" +
+             "   d generated always as ( cast( -a as bigint ) ),\n" +
+             "   e generated always as ( cast ( -a as decimal ) ),\n" +
+             "   f generated always as ( cast ( -a as real ) ),\n" +
+             "   g generated always as ( cast ( -a as double ) ),\n" +
+             "   h generated always as ( cast ( -a as float ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_float( a ) values ( 1.0 )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_FLOAT",
+             new String[][]
+             {
+                 { "A", "DOUBLE" },
+                 { "B", "SMALLINT" },
+                 { "C", "INTEGER" },
+                 { "D", "BIGINT" },
+                 { "E", "DECIMAL(5,0)" },
+                 { "F", "REAL" },
+                 { "G", "DOUBLE" },
+                 { "H", "DOUBLE" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_float order by a",
+             new String[][]
+             {
+                 { "1.0" , "-1", "-1", "-1", "-1", "-1.0", "-1.0", "-1.0" },
+             },
+             false
+             );
+        
+        goodStatement
+            (
+             conn,
+             "create table t_nd_char\n" +
+             "(\n" +
+             "   a char( 20 ),\n" +
+             "   b generated always as ( cast ( upper( a ) as char( 20 ) ) ),\n" +
+             "   c generated always as ( cast ( upper( a ) as varchar( 20 ) ) ),\n" +
+             "   d generated always as ( cast ( upper( a ) as long varchar ) ),\n" +
+             "   e generated always as ( cast ( upper( a ) as clob ) ),\n" +
+             "   f generated always as ( cast( a as date ) ),\n" +
+             "   g generated always as ( cast( '15:09:02' as time ) ),\n" +
+             "   h generated always as ( cast( ( trim( a ) || ' 03:23:34.234' ) as timestamp ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_char( a ) values ( '1994-02-23' )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_CHAR",
+             new String[][]
+             {
+                 { "A", "CHAR(20)" },
+                 { "B", "CHAR(20)" },
+                 { "C", "VARCHAR(20)" },
+                 { "D", "LONG VARCHAR" },
+                 { "E", "CLOB(2147483647)" },
+                 { "F", "DATE" },
+                 { "G", "TIME NOT NULL" },
+                 { "H", "TIMESTAMP" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_char order by a",
+             new String[][]
+             {
+                 { "1994-02-23          " , "1994-02-23          ", "1994-02-23          ", "1994-02-23          ", "1994-02-23          ", "1994-02-23", "15:09:02", "1994-02-23 03:23:34.234" },
+             },
+             false
+             );
+
+        goodStatement
+            (
+             conn,
+             "create table t_nd_varchar\n" +
+             "(\n" +
+             "   a varchar( 20 ),\n" +
+             "   b generated always as ( cast ( upper( a ) as char( 20 ) ) ),\n" +
+             "   c generated always as ( cast ( upper( a ) as varchar( 20 ) ) ),\n" +
+             "   d generated always as ( cast ( upper( a ) as long varchar ) ),\n" +
+             "   e generated always as ( cast ( upper( a ) as clob ) ),\n" +
+             "   f generated always as ( cast( a as date ) ),\n" +
+             "   g generated always as ( cast( '15:09:02' as time ) ),\n" +
+             "   h generated always as ( cast( ( trim( a ) || ' 03:23:34.234' ) as timestamp ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_varchar( a ) values ( '1994-02-23' )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_VARCHAR",
+             new String[][]
+             {
+                 { "A", "VARCHAR(20)" },
+                 { "B", "CHAR(20)" },
+                 { "C", "VARCHAR(20)" },
+                 { "D", "LONG VARCHAR" },
+                 { "E", "CLOB(2147483647)" },
+                 { "F", "DATE" },
+                 { "G", "TIME NOT NULL" },
+                 { "H", "TIMESTAMP" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_varchar order by a",
+             new String[][]
+             {
+                 { "1994-02-23" , "1994-02-23          ", "1994-02-23", "1994-02-23", "1994-02-23", "1994-02-23", "15:09:02", "1994-02-23 03:23:34.234" },
+             },
+             false
+             );
+
+        goodStatement
+            (
+             conn,
+             "create table t_nd_longvarchar\n" +
+             "(\n" +
+             "   a long varchar,\n" +
+             "   b generated always as ( cast ( upper( a ) as char( 20 ) ) ),\n" +
+             "   c generated always as ( cast ( upper( a ) as varchar( 20 ) ) ),\n" +
+             "   d generated always as ( cast ( upper( a ) as long varchar ) ),\n" +
+             "   e generated always as ( cast ( upper( a ) as clob ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_longvarchar( a ) values ( 'foo' )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_LONGVARCHAR",
+             new String[][]
+             {
+                 { "A", "LONG VARCHAR" },
+                 { "B", "CHAR(20)" },
+                 { "C", "VARCHAR(20)" },
+                 { "D", "LONG VARCHAR" },
+                 { "E", "CLOB(2147483647)" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_longvarchar",
+             new String[][]
+             {
+                 { "foo" , "FOO                 ", "FOO", "FOO", "FOO", },
+             },
+             false
+             );
+
+        goodStatement
+            (
+             conn,
+             "create table t_nd_clob\n" +
+             "(\n" +
+             "   a clob,\n" +
+             "   b generated always as ( cast ( upper( a ) as char( 20 ) ) ),\n" +
+             "   c generated always as ( cast ( upper( a ) as varchar( 20 ) ) ),\n" +
+             "   d generated always as ( cast ( upper( a ) as long varchar ) ),\n" +
+             "   e generated always as ( cast ( upper( a ) as clob ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_clob( a ) values ( 'foo' )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_CLOB",
+             new String[][]
+             {
+                 { "A", "CLOB(2147483647)" },
+                 { "B", "CHAR(20)" },
+                 { "C", "VARCHAR(20)" },
+                 { "D", "LONG VARCHAR" },
+                 { "E", "CLOB(2147483647)" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_clob",
+             new String[][]
+             {
+                 { "foo" , "FOO                 ", "FOO", "FOO", "FOO", },
+             },
+             false
+             );
+
+        goodStatement
+            (
+             conn,
+             "create table t_nd_charforbitdata\n" +
+             "(\n" +
+             "   a char( 4 ) for bit data,\n" +
+             "   b generated always as ( cast ( a as char( 4 ) for bit data ) ),\n" +
+             "   c generated always as ( cast ( a as varchar( 4 ) for bit data ) ),\n" +
+             "   d generated always as ( cast ( a as long varchar for bit data ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_charforbitdata( a ) values ( X'ABCDEFAB' )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_CHARFORBITDATA",
+             new String[][]
+             {
+                 { "A", "CHAR (4) FOR BIT DATA" },
+                 { "B", "CHAR (4) FOR BIT DATA" },
+                 { "C", "VARCHAR (4) FOR BIT DATA" },
+                 { "D", "LONG VARCHAR FOR BIT DATA" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_charforbitdata",
+             new String[][]
+             {
+                 { "abcdefab", "abcdefab", "abcdefab", "abcdefab", },
+             },
+             false
+             );
+
+        goodStatement
+            (
+             conn,
+             "create table t_nd_varcharforbitdata\n" +
+             "(\n" +
+             "   a varchar( 4 ) for bit data,\n" +
+             "   b generated always as ( cast ( a as char( 4 ) for bit data ) ),\n" +
+             "   c generated always as ( cast ( a as varchar( 4 ) for bit data ) ),\n" +
+             "   d generated always as ( cast ( a as long varchar for bit data ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_varcharforbitdata( a ) values ( X'ABCDEFAB' )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_VARCHARFORBITDATA",
+             new String[][]
+             {
+                 { "A", "VARCHAR (4) FOR BIT DATA" },
+                 { "B", "CHAR (4) FOR BIT DATA" },
+                 { "C", "VARCHAR (4) FOR BIT DATA" },
+                 { "D", "LONG VARCHAR FOR BIT DATA" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_varcharforbitdata",
+             new String[][]
+             {
+                 { "abcdefab", "abcdefab", "abcdefab", "abcdefab", },
+             },
+             false
+             );
+
+        goodStatement
+            (
+             conn,
+             "create table t_nd_longvarcharforbitdata\n" +
+             "(\n" +
+             "   a long varchar for bit data,\n" +
+             "   b generated always as ( cast ( a as char( 4 ) for bit data ) ),\n" +
+             "   c generated always as ( cast ( a as varchar( 4 ) for bit data ) ),\n" +
+             "   d generated always as ( cast ( a as long varchar for bit data ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_longvarcharforbitdata( a ) values ( X'ABCDEFAB' )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_LONGVARCHARFORBITDATA",
+             new String[][]
+             {
+                 { "A", "LONG VARCHAR FOR BIT DATA" },
+                 { "B", "CHAR (4) FOR BIT DATA" },
+                 { "C", "VARCHAR (4) FOR BIT DATA" },
+                 { "D", "LONG VARCHAR FOR BIT DATA" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_longvarcharforbitdata",
+             new String[][]
+             {
+                 { "abcdefab", "abcdefab", "abcdefab", "abcdefab", },
+             },
+             false
+             );
+
+        goodStatement
+            (
+             conn,
+             "create table t_nd_date\n" +
+             "(\n" +
+             "   a date,\n" +
+             "   b generated always as ( cast ( a as char( 20 ) ) ),\n" +
+             "   c generated always as ( cast ( a as varchar( 20 ) ) ),\n" +
+             "   d generated always as ( cast ( a as date ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_date( a ) values ( date('1994-02-23') )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_DATE",
+             new String[][]
+             {
+                 { "A", "DATE" },
+                 { "B", "CHAR(20)" },
+                 { "C", "VARCHAR(20)" },
+                 { "D", "DATE" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_date",
+             new String[][]
+             {
+                 { "1994-02-23", "1994-02-23          ", "1994-02-23", "1994-02-23", },
+             },
+             false
+             );
+
+        goodStatement
+            (
+             conn,
+             "create table t_nd_time\n" +
+             "(\n" +
+             "   a time,\n" +
+             "   b generated always as ( cast ( a as char( 20 ) ) ),\n" +
+             "   c generated always as ( cast ( a as varchar( 20 ) ) ),\n" +
+             "   d generated always as ( cast ( a as time ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_time( a ) values (  time('15:09:02')  )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_TIME",
+             new String[][]
+             {
+                 { "A", "TIME" },
+                 { "B", "CHAR(20)" },
+                 { "C", "VARCHAR(20)" },
+                 { "D", "TIME" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_time",
+             new String[][]
+             {
+                 { "15:09:02", "15:09:02            ", "15:09:02", "15:09:02", },
+             },
+             false
+             );
+
+        goodStatement
+            (
+             conn,
+             "create table t_nd_timestamp\n" +
+             "(\n" +
+             "   a  timestamp,\n" +
+             "   b  char( 30 ) generated always as ( cast( a as char( 30 ) ) ),\n" +
+             "   c  varchar( 30 ) generated always as ( cast ( a as varchar( 30 ) ) ),\n" +
+             "   d timestamp generated always as ( cast ( a as timestamp ) )\n" +
+             ")\n"
+             );
+        goodStatement
+            (
+             conn,
+             "insert into t_nd_timestamp( a ) values (  timestamp('1962-09-23 03:23:34.234')  )"
+             );
+        assertColumnTypes
+            (
+             conn,
+             "T_ND_TIMESTAMP",
+             new String[][]
+             {
+                 { "A", "TIMESTAMP" },
+                 { "B", "CHAR(30)" },
+                 { "C", "VARCHAR(30)" },
+                 { "D", "TIMESTAMP" },
+             }
+             );
+        assertResults
+            (
+             conn,
+             "select * from t_nd_timestamp",
+             new String[][]
+             {
+                 { "1962-09-23 03:23:34.234", "1962-09-23 03:23:34.234       ", "1962-09-23 03:23:34.234", "1962-09-23 03:23:34.234", },
+             },
+             false
+             );
+
+    }
+    
     ///////////////////////////////////////////////////////////////////////////////////
     //
     // MINIONS



Mime
View raw message