db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mi...@apache.org
Subject svn commit: r543554 - in /db/derby/code/trunk/java: engine/org/apache/derby/catalog/ engine/org/apache/derby/catalog/types/ engine/org/apache/derby/iapi/types/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/ shared/org/apache/der...
Date Fri, 01 Jun 2007 17:54:19 GMT
Author: mikem
Date: Fri Jun  1 10:54:18 2007
New Revision: 543554

URL: http://svn.apache.org/viewvc?view=rev&rev=543554
Log:
DERBY-2717
Throw new error if operands of LIKE do not have matching collations.
Adds tests for this case in CollationTest2.java


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/catalog/TypeDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
    db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/StringDataValue.java
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/LikeEscapeOperatorNode.java
    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/tests/lang/CollationTest2.java

Modified: db/derby/code/trunk/java/engine/org/apache/derby/catalog/TypeDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/catalog/TypeDescriptor.java?view=diff&rev=543554&r1=543553&r2=543554
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/catalog/TypeDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/catalog/TypeDescriptor.java Fri Jun 
1 10:54:18 2007
@@ -220,5 +220,14 @@
 	 */
 	public void setCollationDerivation(int collationDerivationValue);
 
+	/**
+	 * Gets the name of the collation type in this descriptor.
+     * <p>
+     * Used to generate strings decribing collation type for error messages.
+	 * 
+	 *
+	 *  @return	the name of the collation being used in this type.
+	 */
+	public String getCollationName();
 }
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java?view=diff&rev=543554&r1=543553&r2=543554
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java
Fri Jun  1 10:54:18 2007
@@ -414,6 +414,22 @@
 	}
 
 	/**
+	 * Gets the name of the collation type in this descriptor.
+     * <p>
+     * Used to generate strings decribing collation type for error messages.
+	 * 
+	 *
+	 *  @return	the name of the collation being used in this type.
+	 */
+	public String getCollationName()
+    {
+        return(
+            collationType == StringDataValue.COLLATION_TYPE_UCS_BASIC ? 
+                StringDataValue.COLLATION_TYPE_UCS_BASIC_STRING : 
+                StringDataValue.COLLATION_TYPE_TERRITORY_BASED_STRING);
+    }
+
+	/**
 	 * Converts this data type descriptor (including length/precision)
 	 * to a string. E.g.
 

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java?view=diff&rev=543554&r1=543553&r2=543554
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java Fri
Jun  1 10:54:18 2007
@@ -855,6 +855,20 @@
 		return typeDescriptor.getCollationType();
 	}
 
+	/**
+	 * Gets the name of this datatype.
+     * <p>
+     * Used to generate strings decribing collation type for error messages.
+	 * 
+	 *
+	 *  @return	the name of the collation being used in this type.
+	 */
+	public String getCollationName()
+	{
+        return(typeDescriptor.getCollationType() == 0 ?
+                    "UCS_BASIC" : "TERRITORY_BASED");
+	}
+
 	/** @see TypeDescriptor#setCollationType(int) */
 	public void	setCollationType(int collationTypeValue)
 	{

Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/StringDataValue.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/StringDataValue.java?view=diff&rev=543554&r1=543553&r2=543554
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/StringDataValue.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/StringDataValue.java Fri Jun
 1 10:54:18 2007
@@ -58,12 +58,17 @@
 	 * Data types will start with collation type defaulting to UCS_BASIC in
 	 * TypeDescriptorImpl. This collation type ofcourse makes sense fpr 
 	 * character string types only. It will be ignored for the rest of the
-	 * types. If a character's collation type should be TERRIOTRY_BASED, then
+	 * types. If a character's collation type should be TERRITORY_BASED, then
 	 * DTD.setCollationType can be called to change the default of UCS_BASIC.
 	 */
 	public	static final int COLLATION_TYPE_UCS_BASIC = 0;
 	/** @see StringDataValue#COLLATION_TYPE_UCS_BASIC */
 	public	static final int COLLATION_TYPE_TERRITORY_BASED = 1;
+
+    public  static final String COLLATION_TYPE_UCS_BASIC_STRING = 
+        "USC_BASIC";
+    public  static final String COLLATION_TYPE_TERRITORY_BASED_STRING = 
+        "TERRITORY_BASED";
 
 	/**
 	 * The SQL concatenation '||' operator.

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/LikeEscapeOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/LikeEscapeOperatorNode.java?view=diff&rev=543554&r1=543553&r2=543554
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/LikeEscapeOperatorNode.java
(original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/LikeEscapeOperatorNode.java
Fri Jun  1 10:54:18 2007
@@ -304,6 +304,28 @@
             rightConstant = true;
         }
 
+        /* If we are comparing a char with a national char then
+         * we generate a cast above the receiver to force preprocess to
+         * not attempt any of the > <= optimizations since there is no
+         * way to determine the 'next' character for the <= operand.
+         *
+         * TODO-COLLATE - probably need to do something about different 
+         *                collation types here.
+         */
+
+        // The left and the pattern of the LIKE must be same collation type.
+        if (receiver.getTypeServices().getCollationType() !=
+                leftOperand.getTypeServices().getCollationType())
+        {
+            // throw error.
+            throw StandardException.newException(
+                        SQLState.LANG_LIKE_COLLATION_MISMATCH, 
+                        receiver.getTypeServices().getSQLstring(),
+                        receiver.getTypeServices().getCollationName(),
+                        leftOperand.getTypeServices().getSQLstring(),
+                        leftOperand.getTypeServices().getCollationName());
+        }
+
         /* If the left side of LIKE is a ColumnReference and right side is a 
          * string constant without a wildcard (eg. column LIKE 'Derby') then we 
          * transform the LIKE into the equivalent LIKE AND =.  
@@ -400,33 +422,6 @@
                     return newAnd;
                 }
             }
-        }
-
-        /* If we are comparing a char with a national char then
-         * we generate a cast above the receiver to force preprocess to
-         * not attempt any of the > <= optimizations since there is no
-         * way to determine the 'next' character for the <= operand.
-         *
-         * TODO-COLLATE - probably need to do something about different 
-         *                collation types here.
-         */
-
-        TypeId leftTypeId     = leftOperand.getTypeId();
-        TypeId receiverTypeId = receiver.getTypeId();
-
-        if (receiverTypeId.isNationalStringTypeId() && 
-            !leftTypeId.isNationalStringTypeId())
-        {
-            // if column is national column and pattern is not national column
-            receiver = 
-                castArgToNationalString(receiver, receiverTC, receiverTypeId);
-        }
-        else if (leftTypeId.isNationalStringTypeId() && 
-                 !receiverTypeId.isNationalStringTypeId())
-        {
-            // if patern is national column and column is not national column
-            leftOperand = 
-                castArgToNationalString(leftOperand, leftTC, leftTypeId);
         }
 
         finishBindExpr();

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?view=diff&rev=543554&r1=543553&r2=543554
==============================================================================
--- 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 Jun  1 10:54:18
2007
@@ -1119,8 +1119,8 @@
             <msg>
                 <name>42818</name>
                 <text>Comparisons between '{0}' and '{1}' are not supported.</text>
-                <arg>value</arg>
-                <arg>alue1</arg>
+                <arg>type</arg>
+                <arg>type</arg>
             </msg>
 
             <msg>
@@ -2455,6 +2455,15 @@
                 <name>42ZA1</name>
                 <text>Invalid SQL in Batch: '{0}'.</text>
                 <arg>batch</arg>
+            </msg>
+
+            <msg>
+                <name>42ZA2</name>
+                <text>Operand of LIKE predicate with type {0} and collation {1} is
not compatable with LIKE pattern operand with type {2} and collation {3}.</text>
+                <arg>type</arg>
+                <arg>value</arg>
+                <arg>type</arg>
+                <arg>value</arg>
             </msg>
 
         </family>

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?view=diff&rev=543554&r1=543553&r2=543554
==============================================================================
--- 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 Jun  1 10:54:18 2007
@@ -1026,24 +1026,25 @@
 	String LANG_SHARE_TABLE											   = "42Z87.U";
 	String LANG_SHARE_ROW											   = "42Z88.U";
 
-	// MORE GENERIC LANGUAGE STUFF
-	// String LANG_UPDATABLE_VTI_BAD_GETRESULTSETCONCURRENCY			   = "42Z89";
-	String LANG_UPDATABLE_VTI_NON_UPDATABLE_RS						   = "42Z90";
-    String LANG_SUBQUERY                                               = "42Z91";
-    String LANG_REPEATABLE_READ                                        = "42Z92";
-    String LANG_MULTIPLE_CONSTRAINTS_WITH_SAME_COLUMNS                 = "42Z93";
-	// String LANG_ALTER_SYSTEM_TABLE_ATTEMPTED                            = "42Z94"; -- replaced
by 42X62
-	// String LANG_ALTER_TABLE_ON_NON_TABLE                                = "42Z95"; -- replaced
by 42Y62
-	String LANG_RENAME_COLUMN_WILL_BREAK_CHECK_CONSTRAINT              = "42Z97";
-	// beetle 2758.  For now just raise an error for literals > 64K
-	String LANG_INVALID_LITERAL_LENGTH                                 = "42Z99";
-    String LANG_READ_UNCOMMITTED                                       = "42Z9A";
-    String LANG_VTI_BLOB_CLOB_UNSUPPORTED                              = "42Z9B";
-	String LANG_UNSUPPORTED_TRIGGER_STMT		   					   = "42Z9D";
-	String LANG_UNSUPPORTED_TRIGGER_PROC		   					   = "42Z9D.S.1";
-	String LANG_DROP_CONSTRAINT_TYPE                                   = "42Z9E";
-	String LANG_QUERY_TOO_COMPLEX                                      = "42ZA0";
-    String LANG_INVALID_SQL_IN_BATCH                                   = "42ZA1";
+    // MORE GENERIC LANGUAGE STUFF
+    // String LANG_UPDATABLE_VTI_BAD_GETRESULTSETCONCURRENCY          = "42Z89";
+    String LANG_UPDATABLE_VTI_NON_UPDATABLE_RS                        = "42Z90";
+    String LANG_SUBQUERY                                              = "42Z91";
+    String LANG_REPEATABLE_READ                                       = "42Z92";
+    String LANG_MULTIPLE_CONSTRAINTS_WITH_SAME_COLUMNS                = "42Z93";
+    // String LANG_ALTER_SYSTEM_TABLE_ATTEMPTED                       = "42Z94"; -- replaced
by 42X62
+    // String LANG_ALTER_TABLE_ON_NON_TABLE                           = "42Z95"; -- replaced
by 42Y62
+    String LANG_RENAME_COLUMN_WILL_BREAK_CHECK_CONSTRAINT             = "42Z97";
+    // beetle 2758.  For now just raise an error for literals > 64K
+    String LANG_INVALID_LITERAL_LENGTH                                = "42Z99";
+    String LANG_READ_UNCOMMITTED                                      = "42Z9A";
+    String LANG_VTI_BLOB_CLOB_UNSUPPORTED                             = "42Z9B";
+    String LANG_UNSUPPORTED_TRIGGER_STMT                              = "42Z9D";
+    String LANG_UNSUPPORTED_TRIGGER_PROC                              = "42Z9D.S.1";
+    String LANG_DROP_CONSTRAINT_TYPE                                  = "42Z9E";
+    String LANG_QUERY_TOO_COMPLEX                                     = "42ZA0";
+    String LANG_INVALID_SQL_IN_BATCH                                  = "42ZA1";
+    String LANG_LIKE_COLLATION_MISMATCH                               = "42ZA2";
 
 	//following 3 matches the DB2 sql states
 	String LANG_DECLARED_GLOBAL_TEMP_TABLE_ONLY_IN_SESSION_SCHEMA = "428EK";

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java?view=diff&rev=543554&r1=543553&r2=543554
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java
Fri Jun  1 10:54:18 2007
@@ -355,6 +355,43 @@
      */
 
     /**
+     * Assert that the query does not compile and throws
+     * a SQLException with the expected state.
+     * 
+     * @param conn      Connection to correct datatabase.
+     * @param sqlState  expected sql state.
+     * @param query     the query to compile.
+     */
+    private void assertCompileError(
+    Connection  conn,
+    String      sqlState, 
+    String      query) 
+    {
+        try 
+        {
+            PreparedStatement pSt = conn.prepareStatement(query);
+
+            if (usingDerbyNet())
+            {
+                /* For JCC the prepares are deferred until execution,
+                 * so we have to actually execute in order to see the
+                 * expected error.  Note that we don't need to worry
+                 * about binding the parameters (if any); the compile
+                 * error should occur before the execution-time error
+                 * about unbound parameters.
+                 */
+                pSt.execute();
+            }
+            fail("expected compile error: " + sqlState);
+            pSt.close();
+        } 
+        catch (SQLException se) 
+        {
+            assertSQLState(sqlState, se);
+        }
+    }
+
+    /**
      * RESOLVE - unfinished LIKE test with dataset of all unicode characters
      **/
     private static final void printRuleBasedCollator()
@@ -757,6 +794,27 @@
                java_version.equals("J2ME Foundation Specification v1.1"));
 	}
 
+    private boolean isDatabaseBasicCollation(Connection conn)
+        throws SQLException
+    {
+        PreparedStatement ps = 
+            conn.prepareStatement(
+                "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY" + 
+                    "('derby.database.collation')");
+
+        ResultSet rs = ps.executeQuery();
+
+        Assert.assertTrue(rs.next());
+
+        String collation = rs.getString(1);
+
+        Assert.assertFalse(rs.next());
+
+        rs.close();
+        ps.close();
+
+        return(collation.equals("UCS_BASIC"));
+    }
 
 
     /**************************************************************************
@@ -1346,27 +1404,49 @@
                     "Running like test[" + i + "] = " + LIKE_TEST_CASES[i]);
             }
 
-            // varchar column
+            // varchar column - constant pattern
             checkLangBasedQuery(
                 conn,
-                "SELECT ID, NAME_VARCHAR FROM CUSTOMER WHERE NAME_VARCHAR LIKE " +
-                    "'" + LIKE_TEST_CASES[i] + "'",
+                "SELECT ID, NAME_VARCHAR FROM CUSTOMER " + 
+                    "WHERE NAME_VARCHAR LIKE '" + LIKE_TEST_CASES[i] + "'",
+                full_row_single_value(
+                    EXPECTED_LIKE_RESULTS[db_index][i],
+                    LIKE_NAMES),
+                true);
+
+            // varchar column - parameter pattern
+            checkOneParamQuery(
+                conn,
+                "SELECT ID, NAME_VARCHAR FROM CUSTOMER " + 
+                    "WHERE NAME_VARCHAR LIKE ?",
+                LIKE_TEST_CASES[i],
                 full_row_single_value(
                     EXPECTED_LIKE_RESULTS[db_index][i],
                     LIKE_NAMES),
                 true);
 
-            // long varchar column
+            // long varchar column - constant
             checkLangBasedQuery(
                 conn,
-                "SELECT ID, NAME_LONGVARCHAR FROM CUSTOMER WHERE NAME_LONGVARCHAR LIKE "
+
-                    "'" + LIKE_TEST_CASES[i] + "'",
+                "SELECT ID, NAME_LONGVARCHAR FROM CUSTOMER " + 
+                    "WHERE NAME_LONGVARCHAR LIKE '" + LIKE_TEST_CASES[i] + "'",
+                full_row_single_value(
+                    EXPECTED_LIKE_RESULTS[db_index][i],
+                    LIKE_NAMES),
+                true);
+
+            // long varchar column - parameter
+            checkOneParamQuery(
+                conn,
+                "SELECT ID, NAME_LONGVARCHAR FROM CUSTOMER " + 
+                    "WHERE NAME_LONGVARCHAR LIKE ?",
+                LIKE_TEST_CASES[i],
                 full_row_single_value(
                     EXPECTED_LIKE_RESULTS[db_index][i],
                     LIKE_NAMES),
                 true);
 
-            // clob column
+            // clob column - constant
             checkLangBasedQuery(
                 conn,
                 "SELECT ID, NAME_CLOB FROM CUSTOMER WHERE NAME_CLOB LIKE " +
@@ -1376,6 +1456,16 @@
                     LIKE_NAMES),
                 true);
 
+            // clob column - parameter
+            checkOneParamQuery(
+                conn,
+                "SELECT ID, NAME_CLOB FROM CUSTOMER WHERE NAME_CLOB LIKE ?",
+                LIKE_TEST_CASES[i],
+                full_row_single_value(
+                    EXPECTED_LIKE_RESULTS[db_index][i],
+                    LIKE_NAMES),
+                true);
+
             // char column, char includes blank padding so alter all these
             // tests cases to match for blanks at end also.
             checkLangBasedQuery(
@@ -1386,6 +1476,48 @@
                     EXPECTED_LIKE_RESULTS[db_index][i],
                     LIKE_NAMES),
                 true);
+
+            // char column, char includes blank padding so alter all these
+            // tests cases to match for blanks at end also.
+            checkOneParamQuery(
+                conn,
+                "SELECT ID, NAME_CHAR FROM CUSTOMER WHERE NAME_CHAR LIKE ?",
+                LIKE_CHAR_TEST_CASES[i] + "%",
+                full_row_single_value(
+                    EXPECTED_LIKE_RESULTS[db_index][i],
+                    LIKE_NAMES),
+                true);
+        }
+
+        // test error thrown from LIKE on mismatched collation 
+        String zero_row_syscat_query1 = 
+            "SELECT * from SYS.SYSCOLUMNS where COLUMNNAME like 'nonmatchiing'";
+        String zero_row_syscat_query2 = 
+            "SELECT * from SYS.SYSCOLUMNS where 'nonmatchiing' like COLUMNNAME";
+        String zero_row_syscat_query_param1 = 
+            "SELECT * from SYS.SYSCOLUMNS where COLUMNNAME like ?";
+        String zero_row_syscat_query_param2 = 
+            "SELECT * from SYS.SYSCOLUMNS where ? like COLUMNNAME";
+
+        if (!isDatabaseBasicCollation(conn))
+        {
+            // collation of 'fred' picked up from current schema which is
+            // territory based collation, but system column will have basic
+            // collation.
+
+            assertCompileError(conn, "42ZA2", zero_row_syscat_query1);
+            assertCompileError(conn, "42ZA2", zero_row_syscat_query2);
+            assertCompileError(conn, "42ZA2", zero_row_syscat_query_param1);
+            assertCompileError(conn, "42ZA2", zero_row_syscat_query_param2);
+        }
+        else
+        {
+            checkLangBasedQuery(conn, zero_row_syscat_query1, null, true);
+            checkLangBasedQuery(conn, zero_row_syscat_query2, null, true);
+            checkOneParamQuery(
+                conn, zero_row_syscat_query_param1, "nonmatchiing", null, true);
+            checkOneParamQuery(
+                conn, zero_row_syscat_query_param2, "nonmatchiing", null, true);
         }
 
         dropLikeTable(conn);
@@ -1907,12 +2039,14 @@
     public void testDefaultCollation() throws SQLException
     {
         Connection conn = setUpDBandOpenConnection(TEST_DEFAULT);
+        Assert.assertTrue(isDatabaseBasicCollation(conn));
         runTestIter(conn, TEST_DEFAULT);
         conn.close();
     }
     public void testEnglishCollation() throws SQLException
     {
         Connection conn = setUpDBandOpenConnection(TEST_ENGLISH);
+        Assert.assertTrue(!isDatabaseBasicCollation(conn));
         runTestIter(conn, TEST_ENGLISH);
         conn.close();
     }
@@ -1920,12 +2054,14 @@
     public void testPolishCollation() throws SQLException
     {
         Connection conn = setUpDBandOpenConnection(TEST_POLISH);
+        Assert.assertTrue(!isDatabaseBasicCollation(conn));
         runTestIter(conn, TEST_POLISH);
         conn.close();
     }
     public void testNorwayCollation() throws SQLException
     {
         Connection conn = setUpDBandOpenConnection(TEST_NORWAY);
+        Assert.assertTrue(!isDatabaseBasicCollation(conn));
         runDerby2670(conn);
         runTestIter(conn, TEST_NORWAY);
         conn.close();
@@ -1966,6 +2102,7 @@
         if (run_test)
         {
             Connection conn = setUpDBandOpenConnection(TEST_DEFAULT_TERRITORY);
+            Assert.assertTrue(!isDatabaseBasicCollation(conn));
             runTestIter(conn, db_index);
             conn.close();
         }



Mime
View raw message