db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r733094 - in /db/derby/code/branches/10.3/java: engine/org/apache/derby/iapi/types/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Fri, 09 Jan 2009 17:40:20 GMT
Author: mamta
Date: Fri Jan  9 09:40:19 2009
New Revision: 733094

URL: http://svn.apache.org/viewvc?rev=733094&view=rev
Log:
DERBY-3975: SELECT DISTINCT may return duplicates with territory-based collation

Merged revision 728822 from trunk.


Modified:
    db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLChar.java
    db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLClob.java
    db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLLongvarchar.java
    db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLVarchar.java
    db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/SQLChar.java
    db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java

Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLChar.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLChar.java?rev=733094&r1=733093&r2=733094&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLChar.java
(original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLChar.java
Fri Jan  9 09:40:19 2009
@@ -163,6 +163,16 @@
 		 return holderForCollationSensitiveInfo.stringCompare(char1, char2);
 	 }
 
+     /**
+      * Return a hash code that is consistent with
+      * {@link #stringCompare(SQLChar, SQLChar)}.
+      *
+      * @return hash code
+      */
+     public int hashCode() {
+         return hashCodeForCollation();
+     }
+
 	/**
 	 * This method implements the like function for char (with no escape value).
 	 * The difference in this method and the same method in superclass is that

Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLClob.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLClob.java?rev=733094&r1=733093&r2=733094&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLClob.java
(original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLClob.java
Fri Jan  9 09:40:19 2009
@@ -164,6 +164,16 @@
 		 return holderForCollationSensitiveInfo.stringCompare(char1, char2);
 	 }
 
+     /**
+      * Return a hash code that is consistent with
+      * {@link #stringCompare(SQLChar, SQLChar)}.
+      *
+      * @return hash code
+      */
+     public int hashCode() {
+         return hashCodeForCollation();
+     }
+
 	/**
 	 * This method implements the like function for char (with no escape value).
 	 * The difference in this method and the same method in superclass is that

Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLLongvarchar.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLLongvarchar.java?rev=733094&r1=733093&r2=733094&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLLongvarchar.java
(original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLLongvarchar.java
Fri Jan  9 09:40:19 2009
@@ -165,6 +165,16 @@
 		 return holderForCollationSensitiveInfo.stringCompare(char1, char2);
 	 }
 
+    /**
+      * Return a hash code that is consistent with
+      * {@link #stringCompare(SQLChar, SQLChar)}.
+      *
+      * @return hash code
+      */
+     public int hashCode() {
+         return hashCodeForCollation();
+     }
+
 	/**
 	 * This method implements the like function for char (with no escape value).
 	 * The difference in this method and the same method in superclass is that

Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLVarchar.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLVarchar.java?rev=733094&r1=733093&r2=733094&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLVarchar.java
(original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/CollatorSQLVarchar.java
Fri Jan  9 09:40:19 2009
@@ -171,6 +171,16 @@
 		 return holderForCollationSensitiveInfo.stringCompare(char1, char2);
 	 }
 
+     /**
+      * Return a hash code that is consistent with
+      * {@link #stringCompare(SQLChar, SQLChar)}.
+      *
+      * @return hash code
+      */
+     public int hashCode() {
+         return hashCodeForCollation();
+     }
+
 	/**
 	 * This method implements the like function for char (with no escape value).
 	 * The difference in this method and the same method in superclass is that

Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/SQLChar.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/SQLChar.java?rev=733094&r1=733093&r2=733094&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/SQLChar.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/iapi/types/SQLChar.java Fri Jan
 9 09:40:19 2009
@@ -2655,6 +2655,11 @@
      */
     public int hashCode()
     {
+        if (SanityManager.DEBUG) {
+            SanityManager.ASSERT(!(this instanceof CollationElementsInterface),
+                    "SQLChar.hashCode() does not work with collation");
+        }
+
         try {
             if (getString() == null)
             {
@@ -2703,6 +2708,24 @@
     }
 
     /**
+     * Hash code implementation for collator sensitive subclasses.
+     */
+    int hashCodeForCollation() {
+        CollationKey key = null;
+
+        try {
+            key = getCollationKey();
+        } catch (StandardException se) {
+            // ignore exceptions, like we do in hashCode()
+            if (SanityManager.DEBUG) {
+                SanityManager.THROWASSERT("Unexpected exception", se);
+            }
+        }
+
+        return key == null ? 0 : key.hashCode();
+    }
+
+    /**
      * Get a SQLVarchar for a built-in string function.  
      *
      * @return a SQLVarchar.

Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java?rev=733094&r1=733093&r2=733094&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
(original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
Fri Jan  9 09:40:19 2009
@@ -279,59 +279,78 @@
       //End of parameter testing
       
       s.close();
-      compareAgrave(conn,1,1);
+      compareAgrave(1, 1, 2);
       }
       
 
 public void testFrenchCollation() throws SQLException {
-    Connection conn = getConnection();
-    compareAgrave(conn,2,1);    
+    compareAgrave(2, 1, 1);
 }
 
 
-
    /**
    * For a TERRITORY_BASED collation french database, differences between pre-composed accents
such 
    * as "\u00C0" (A-grave) and combining accents such as "A\u0300" (A, combining-grave) should
match
    * for = and like. But they do not match for UCS_BASIC. We insert both into a table and
search
    * based on equal and like. 
    *  
-   * @param conn
    * @param expectedMatchCountForEqual  number of rows we expect back for =. 
    * 	2 for French, 1 for English 
    * @param expectedMatchCountForLike  number of rows we expect back for LIKE. 
    * 	1 for French and English 
+   * @param expectedDistinctRows number of rows expected from SELECT DISTINCT
    * @throws SQLException
    */
-   private void compareAgrave(Connection conn, int expectedMatchCountForEqual,
-		int expectedMatchCountForLike) throws SQLException {
-      
+   private void compareAgrave(int expectedMatchCountForEqual,
+        int expectedMatchCountForLike, int expectedDistinctRows)
+            throws SQLException {
+    String[] dataTypes = {"VARCHAR(5)", "CHAR(5)"};
+    for (int i = 0; i < dataTypes.length; i++) {
+        compareAgrave(dataTypes[i], expectedMatchCountForEqual,
+                expectedMatchCountForLike, expectedDistinctRows);
+    }
+}
+
+/**
+ * Helper for {@link #compareAgrave(int, int, int)} which performs the test
+ * for one data type.
+ */
+private void compareAgrave(String dataType, int expectedMatchCountForEqual,
+		int expectedMatchCountForLike, int expectedDistinctRows)
+            throws SQLException {
+
+      // Create the two strings that are supposed to be equal in French locale.
       String agrave = "\u00C0";
       String agraveCombined ="A\u0300";
-      Statement s = conn.createStatement();
+
+      Statement s = createStatement();
       
       try {
           s.executeUpdate("DROP TABLE T");
       }catch (SQLException se) {}
-      s.executeUpdate("CREATE TABLE T (vc varchar(30))");
-      PreparedStatement ps = conn.prepareStatement("INSERT INTO T VALUES (?)");
+      s.executeUpdate("CREATE TABLE T (vc " + dataType + ")");
+      PreparedStatement ps = prepareStatement("INSERT INTO T VALUES (?)");
       ps.setString(1,agrave);
       ps.executeUpdate();
       ps.setString(1,agraveCombined);
       ps.executeUpdate();
-      ps.close();
         
-      ps = conn.prepareStatement("SELECT COUNT(*) FROM T WHERE VC = ?");
+      ps = prepareStatement("SELECT COUNT(*) FROM T WHERE VC = ?");
       ps.setString(1, agrave);
       ResultSet rs = ps.executeQuery();
       JDBC.assertSingleValueResultSet(rs, Integer.toString(expectedMatchCountForEqual));
-      ps = conn.prepareStatement("SELECT COUNT(*) FROM T WHERE VC LIKE ?");
+      // Use '%' at the end of the pattern so that we also match the trailing
+      // blanks if the data type is CHAR instead of VARCHAR.
+      ps = prepareStatement("SELECT COUNT(*) FROM T WHERE VC LIKE ? || '%'");
       ps.setString(1, agrave);
       rs = ps.executeQuery();
       JDBC.assertSingleValueResultSet(rs, Integer.toString(expectedMatchCountForLike));
-      rs.close();
-      ps.close();
-      s.close();
+
+      // DERBY-3975: They should match for distinct, the same way as for =
+      int distinctRows = JDBC.assertDrainResults(
+              s.executeQuery("SELECT DISTINCT VC FROM T"));
+      assertEquals("Unexpected number of distinct rows",
+              expectedDistinctRows, distinctRows);
   }
 
 
@@ -545,8 +564,9 @@
 
 private void commonTestingForTerritoryBasedDB(Statement s) throws SQLException{
 	PreparedStatement ps;
-	ResultSet rs;
-    Connection conn = s.getConnection();		
+	ResultSet rs;	
+    
+    Connection conn = s.getConnection();
 
     s.executeUpdate("set schema APP");
     //Following sql will fail because the compilation schema is user schema
@@ -751,12 +771,12 @@
         		null);
         //Do some parameter testing for XMLSERIALIZE. ? is not supported inside
         //the XMLSERIALIZE function and hence following will result in errors.
-        checkPreparedStatementError(conn, "SELECT XMLSERIALIZE(x as CHAR(10)) " +
+        assertCompileError("42Z70", "SELECT XMLSERIALIZE(x as CHAR(10)) " +
         		" FROM xmlTable, SYS.SYSTABLES WHERE " +
-				" XMLSERIALIZE(? as CHAR(10)) = TABLENAME", "42Z70");
-        checkPreparedStatementError(conn, "SELECT XMLSERIALIZE(x as CHAR(10)) FROM " +
+				" XMLSERIALIZE(? as CHAR(10)) = TABLENAME");
+        assertCompileError("42Z70", "SELECT XMLSERIALIZE(x as CHAR(10)) FROM " +
         		" xmlTable, SYS.SYSTABLES WHERE XMLSERIALIZE(? as CHAR(10)) = " + 
-    			" CAST(TABLENAME AS CHAR(10))", "42Z70");
+    			" CAST(TABLENAME AS CHAR(10))");
     }
     
     //Start of user defined function testing
@@ -782,7 +802,7 @@
     //from the context which in this case is from TABLENAME and TABLENAME
     //has collation type of UCS_BASIC
     s.executeUpdate("set schema APP");
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
     		" ? = TABLENAME");
     ps.setString(1, "SYSCOLUMNS");
     rs = ps.executeQuery();
@@ -796,13 +816,13 @@
     //territory based since the result of SUBSTR always picks up the 
     //collation of it's first operand. So the comparison between left hand
     //side with terriotry based and right hand side with UCS_BASIC will fail.
-    checkPreparedStatementError(conn, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
-    		" SUBSTR(?,2) = TABLENAME", "42818");
+    assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    		" SUBSTR(?,2) = TABLENAME");
     //To fix the problem above, we need to CAST TABLENAME so that the result 
     //of CAST will pick up the collation of the current schema and this will
     //cause both the operands of SUBSTR(?,2) = CAST(TABLENAME AS CHAR(10)) 
     //to have same collation
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
 		" SUBSTR(?,2) = CAST(TABLENAME AS CHAR(10))");
     ps.setString(1, "aSYSCOLUMNS");
     rs = ps.executeQuery();
@@ -816,16 +836,16 @@
     //take collation from context which here will be TABLENAME and hence the
     //result of concatenation will have collation type of it's 2 operands,
     //namely UCS_BASIC
-    checkPreparedStatementError(conn, "SELECT TABLENAME FROM SYS.SYSTABLES " +
-    		" WHERE TABLENAME || ? LIKE 'SYSCOLUMNS '", "42ZA2");   
+    assertCompileError("42ZA2", "SELECT TABLENAME FROM SYS.SYSTABLES " +
+    		" WHERE TABLENAME || ? LIKE 'SYSCOLUMNS '");   
     //The query above can be made to work if we are in SYS schema or if we use
     //CAST while we are trying to run the query is user schema
     //Let's try CAST first
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
     		" CAST((TABLENAME || ?) AS CHAR(20)) LIKE 'SYSCOLUMNS'");   
     //try switching to SYS schema and then run the original query without CAST
     s.executeUpdate("set schema SYS");
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES " +
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES " +
     		" WHERE TABLENAME || ? LIKE 'SYSCOLUMNS'");   
     s.executeUpdate("set schema APP");
     //The following will fail because the left hand side of LIKE has collation
@@ -843,13 +863,13 @@
     //Do parameter testing for IS NULL
     //Following query will pass because it doesn't matter what the collation of
     //? is when doing a NULL check
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
     		" ? IS NULL");   
     ps.setString(1, " ");
     rs = ps.executeQuery();
 	JDBC.assertEmpty(rs);
 	//Now do the testing for IS NOT NULL
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
     		" ? IS NOT NULL");
     ps.setNull(1, java.sql.Types.VARCHAR);
     rs = ps.executeQuery();
@@ -859,28 +879,28 @@
     //Following query will fail because LENGTH operator is not allowed to take
     //a parameter. I just wanted to have a test case out for the changes that
     //are going into engine code (ie LengthOperatorNode)
-    checkPreparedStatementError(conn, "SELECT COUNT(*) FROM CUSTOMER WHERE " +
-    		" LENGTH(?) != 0", "42X36");   
+    assertCompileError("42X36", "SELECT COUNT(*) FROM CUSTOMER WHERE " +
+    		" LENGTH(?) != 0");   
 
     //Do parameter testing for BETWEEN
     //Following should pass for ? will take the collation from the context and
     //hence, it will be UCS_BASIC
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
 	" TABLENAME NOT BETWEEN ? AND TABLENAME");   
     ps.setString(1, " ");
     rs = ps.executeQuery();
 	JDBC.assertEmpty(rs);
 	//Following will fail because ? will take collation of territory based but
 	//the left hand side has collation of UCS_BASIC
-    checkPreparedStatementError(conn, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
-    		" TABLENAME NOT BETWEEN ? AND 'SYSCOLUMNS'", "42818");   
+    assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    		" TABLENAME NOT BETWEEN ? AND 'SYSCOLUMNS'");   
     
     //Do parameter testing with COALESCE
     //following will pass because the ? inside the COALESCE will take the 
     //collation type of the other operand which is TABLENAME. The result of
     //COALESCE will have collation type of UCS_BASIC and that is the same
     //collation that the ? on rhs of = will get.
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
 	" COALESCE(TABLENAME, ?) = ?");   
     ps.setString(1, " ");
     ps.setString(2, "SYSCOLUMNS ");
@@ -895,22 +915,22 @@
     //territory based since the result of LTRIM always picks up the 
     //collation of it's operand. So the comparison between left hand
     //side with terriotry based and right hand side with UCS_BASIC will fail.
-    checkPreparedStatementError(conn, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
-    		" LTRIM(?) = TABLENAME", "42818");
+    assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    		" LTRIM(?) = TABLENAME");
     //To fix the problem above, we need to CAST TABLENAME so that the result 
     //of CAST will pick up the collation of the current schema and this will
     //cause both the operands of LTRIM(?) = CAST(TABLENAME AS CHAR(10)) 
     //to have same collation
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
 		" LTRIM(?) = CAST(TABLENAME AS CHAR(10))");
     ps.setString(1, " SYSCOLUMNS");
     rs = ps.executeQuery();
     JDBC.assertFullResultSet(rs,new String[][] {{"SYSCOLUMNS"}});
 
     //Similar testing for RTRIM
-    checkPreparedStatementError(conn, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
-    		" RTRIM(?) = TABLENAME", "42818");
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    		" RTRIM(?) = TABLENAME");
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
 		" RTRIM(?) = CAST(TABLENAME AS CHAR(10))");
     ps.setString(1, "SYSCOLUMNS  ");
     rs = ps.executeQuery();
@@ -922,12 +942,12 @@
     //And the ? in TRIM will pick up it's collation from 'a' and hence the
     //comparison between territory based character string returned from TRIM
     //function will fail against UCS_BASIC based TABLENAME on the right
-    checkPreparedStatementError(conn, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
-    		" TRIM('a' FROM ?) = TABLENAME", "42818");
+    assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    		" TRIM('a' FROM ?) = TABLENAME");
     //The problem can be fixed by using CAST on TABLENAME so the resultant of
     //CAST string will compare fine with the output of TRIM. Note CAST always
     //picks up the collation of the compilation schema.
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
     		" TRIM('a' FROM ?) = CAST(TABLENAME AS CHAR(10))");
     ps.setString(1, "aSYSCOLUMNS");
     rs = ps.executeQuery();
@@ -937,7 +957,7 @@
     //from it's first parameter which is a SUBSTR on TABLENAME and hence the 
     //result of TRIM will have UCS_BASIC collation which matches the collation
     //on the right.
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
     		" TRIM(LEADING SUBSTR(TABLENAME, LENGTH(TABLENAME)) FROM ?) = TABLENAME");
     ps.setString(1, "SYSCOLUMNS");
     rs = ps.executeQuery();
@@ -948,13 +968,13 @@
     //Following will fail because 'LOOKFORME' has collation of territory based
     //but TABLENAME has collation of UCS_BASIC and hence LOCATE will fail 
     //because the collation types of it's two operands do not match
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
     		" LOCATE(?, TABLENAME) != 0");
     ps.setString(1, "ABC");
     rs = ps.executeQuery();
     JDBC.assertEmpty(rs);
     //Just switch the parameter position and try the sql again
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
     		" LOCATE(TABLENAME, ?) != 0");
     ps.setString(1, "ABC");
     rs = ps.executeQuery();
@@ -964,7 +984,7 @@
     //Following will work just fine because ? will take it's collation from the
     //context which in this case will be collation of TABLENAME which has 
     //collation type of UCS_BASIC. 
-    ps = conn.prepareStatement("SELECT COUNT(*) FROM CUSTOMER WHERE ? IN " +
+    ps = prepareStatement("SELECT COUNT(*) FROM CUSTOMER WHERE ? IN " +
     		" (SELECT TABLENAME FROM SYS.SYSTABLES)");
     ps.setString(1, "SYSCOLUMNS");
     rs = ps.executeQuery();
@@ -976,14 +996,14 @@
     //current schema which is the user schema and hence it's collation type
     //will be territory based. But that collation does not match the left hand
     //side on IN clause and hence it results in compliation error.
-    checkPreparedStatementError(conn, "SELECT TABLENAME FROM SYS.SYSTABLES " +
+    assertCompileError("42818", "SELECT TABLENAME FROM SYS.SYSTABLES " +
     		" WHERE TABLENAME NOT IN (?, ' SYSCOLUMNS ') AND " +
-			" CAST(TABLENAME AS CHAR(10)) = 'SYSCOLUMNS' ", "42818");
+			" CAST(TABLENAME AS CHAR(10)) = 'SYSCOLUMNS' ");
     //We can make the query work in 2 ways
     //1)Be in the SYS schema and then ? will take the collation of UCS_BASIC
     //because that is what the character string literal ' SYSCOLUMNS ' has.
     s.executeUpdate("set schema SYS");
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES " +
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES " +
     		" WHERE TABLENAME NOT IN (?, ' SYSCOLUMNS ') AND " +
 			" CAST(TABLENAME AS CHAR(10)) = 'SYSCOLUMNS' ");
     ps.setString(1, "aSYSCOLUMNS");
@@ -992,7 +1012,7 @@
     //2)The other way to fix the query would be to do a CAST on TABLENAME so
     //it will have the collation of current schema which is APP 
     s.executeUpdate("set schema APP");
-    ps = conn.prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " + 
+    ps = prepareStatement("SELECT TABLENAME FROM SYS.SYSTABLES WHERE " + 
 	" CAST(TABLENAME AS CHAR(10)) NOT IN (?, ' SYSCOLUMNS ') AND " +
 	" CAST(TABLENAME AS CHAR(10)) = 'SYSCOLUMNS' ");
     ps.setString(1, "aSYSCOLUMNS");
@@ -1002,7 +1022,7 @@
     //Following will not fail because collation of ? here does not matter 
     //since we are not doing a collation related method 
     s.executeUpdate("set schema SYS");
-    ps = conn.prepareStatement("INSERT INTO APP.CUSTOMER(NAME) VALUES(?)");
+    ps = prepareStatement("INSERT INTO APP.CUSTOMER(NAME) VALUES(?)");
     ps.setString(1, "SYSCOLUMNS");
     ps.executeUpdate();
     ps.close();
@@ -1039,7 +1059,7 @@
     s.execute("create table assoc (x char(10) not null primary key, "+
     		" y char(100))");
     s.execute("create table assocout(x char(10))");
-    ps = conn.prepareStatement("insert into assoc values (?, 'hello')");
+    ps = prepareStatement("insert into assoc values (?, 'hello')");
     ps.setString(1, new Integer(10).toString());
     ps.executeUpdate();     
     
@@ -1080,7 +1100,7 @@
     //by number of collation elements that special character _ represents
     s.executeUpdate("create table DERBY_2967(c11 int)"); 
     s.executeUpdate("insert into DERBY_2967 values 1"); 
-    ps = conn.prepareStatement("select 1 from DERBY_2967 where '\uFA2D' like ?");
+    ps = prepareStatement("select 1 from DERBY_2967 where '\uFA2D' like ?");
     String[] match = { "%", "_", "\uFA2D" }; 
     for (int i = 0; i < match.length; i++) { 
         ps.setString(1, match[i]); 
@@ -1201,24 +1221,6 @@
 }
 
 /**
- * Make sure that attempt to prepare the statement will give the passed error
- * 
- * @param con Connection on which query should be prepared
- * @param query Query to be prepared
- * @param error Prepared statement will give this error for the passed query
- */
-private void checkPreparedStatementError(Connection con, String query, 
-		String error)
-{
-	try{
-	    con.prepareStatement(query);
-        fail("Expected error '" + error  + "' but no error was thrown.");
-	} catch (SQLException sqle) {
-        assertSQLState(error, sqle);		
-	}
-	
-}
-/**
  * Execute the passed statement and compare the results against the
  * expectedResult 
  *



Mime
View raw message