db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r553557 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/LikeEscapeOperatorNode.java testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest2.java
Date Thu, 05 Jul 2007 16:44:20 GMT
Author: mamta
Date: Thu Jul  5 09:44:18 2007
New Revision: 553557

URL: http://svn.apache.org/viewvc?view=rev&rev=553557
Log:
DERBY-2777

Currently, the parameters in LIKE clause always pickup their collation from the compilation
schema. That logic is not 
complete. I am fixing that logic here along with addition of some tests.

For the sake of explanation, let me use the following syntax for LIKE clause
receiver LIKE leftOperand ESCAPE rightOperand
With the fix in this patch, if receiver is a parameter, it will set it's collation using following
logic
1)check if leftOperand is not a parameter. If yes, then receiver will pick up collation from
leftOperand. If not, goto step 2
2)check if rightOperand is not a parameter. If yes, then receiver will pick up collation from
rightOperand. If not, goto step 3
3)receiver picks up the collation of the compilation schema because everything in the LIKE
clause is ?

Next, if leftOperand is a parameter, it will set it's collation using receiver. By this time,
even if receiver is a
parameter, we have set correct collation for receiver and hence leftOperand can simply rely
on receiver for correct
collation IF leftOperand is a parameter.

Next, if rightOperand is a parameter, it will set it's collation using receiver. By this time,
even if receiver is a
parameter, we have set correct collation for receiver and hence rightOperand can simply rely
on receiver for correct
collation IF rightOperand is a parameter.


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/LikeEscapeOperatorNode.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/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=553557&r1=553556&r2=553557
==============================================================================
--- 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
Thu Jul  5 09:44:18 2007
@@ -189,9 +189,23 @@
             receiver.setType(
                 new DataTypeDescriptor(
                     TypeId.getBuiltInTypeId(Types.VARCHAR), true));
-			//collation of ? operand should be same as the compilation schema
-			receiver.setCollationUsingCompilationSchema(
-					StringDataValue.COLLATION_DERIVATION_IMPLICIT);
+            //check if this parameter can pick up it's collation from pattern
+            //or escape clauses in that order. If not, then it will take it's
+            //collation from the compilation schema.
+            if (!leftOperand.requiresTypeFromContext()) {
+            	receiver.getTypeServices().setCollationDerivation(
+            			leftOperand.getTypeServices().getCollationDerivation());
+            	receiver.getTypeServices().setCollationType(
+            			leftOperand.getTypeServices().getCollationType());
+            } else if (rightOperand != null && !rightOperand.requiresTypeFromContext())
{
+            	receiver.getTypeServices().setCollationDerivation(
+            			rightOperand.getTypeServices().getCollationDerivation());
+            	receiver.getTypeServices().setCollationType(
+            			rightOperand.getTypeServices().getCollationType());            	
+            } else {
+    			receiver.setCollationUsingCompilationSchema(
+    					StringDataValue.COLLATION_DERIVATION_IMPLICIT);            	
+            }
         }
 
         /* 
@@ -217,9 +231,14 @@
                     new DataTypeDescriptor(
                         TypeId.getBuiltInTypeId(Types.VARCHAR), true));
             }
-			//collation of ? operand should be same as the compilation schema
-			leftOperand.setCollationUsingCompilationSchema(
-					StringDataValue.COLLATION_DERIVATION_IMPLICIT);
+			//collation of ? operand should be picked up from the context.
+            //By the time we come here, receiver will have correct collation
+            //set on it and hence we can rely on it to get correct collation
+            //for the other ? in LIKE clause
+			leftOperand.getTypeServices().setCollationDerivation(
+					receiver.getTypeServices().getCollationDerivation());
+			leftOperand.getTypeServices().setCollationType(
+        			receiver.getTypeServices().getCollationType());            	
         }
 
         /* 
@@ -244,9 +263,14 @@
                     new DataTypeDescriptor(
                         TypeId.getBuiltInTypeId(Types.VARCHAR), true));
             }
-			//collation of ? operand should be same as the compilation schema
-			rightOperand.setCollationUsingCompilationSchema(
-					StringDataValue.COLLATION_DERIVATION_IMPLICIT);
+			//collation of ? operand should be picked up from the context.
+            //By the time we come here, receiver will have correct collation
+            //set on it and hence we can rely on it to get correct collation
+            //for the other ? in LIKE clause
+			rightOperand.getTypeServices().setCollationDerivation(
+					receiver.getTypeServices().getCollationDerivation());
+			rightOperand.getTypeServices().setCollationType(
+        			receiver.getTypeServices().getCollationType());            	
         }
 
         bindToBuiltIn();

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=553557&r1=553556&r2=553557
==============================================================================
--- 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
Thu Jul  5 09:44:18 2007
@@ -585,16 +585,20 @@
         s.close();
     }
 
-    private void checkOneParamQuery(
+    private void checkParamQuery(
     Connection  conn,
     String      query, 
-    String      param,
+    String[]      param,
+    int    paramNumber,
     String[][]  expectedResult, 
     boolean     ordered) 
         throws SQLException 
     {
         PreparedStatement   ps = conn.prepareStatement(query);
-        ps.setString(1, param);
+        for (int i=0; i < paramNumber;i++)
+        {
+        	ps.setString(i+1, param[i]);
+        }
         ResultSet           rs = ps.executeQuery();
 
         if (expectedResult == null) //expecting empty resultset from the query
@@ -611,7 +615,10 @@
 
 
         // re-execute it to test path through the cache
-        ps.setString(1, param);
+        for (int i=0; i < paramNumber;i++)
+        {
+        	ps.setString(i+1, param[i]);
+        }
         rs = ps.executeQuery();
 
         if (expectedResult == null) //expecting empty resultset from the query
@@ -1165,10 +1172,11 @@
             // now check prepared query
 
             // '<' test
-            checkOneParamQuery(
+            checkParamQuery(
                 conn, 
                 "SELECT ID, NAME FROM CUSTOMER where NAME < ? ORDER BY NAME",
-                NAMES[expected_order[i]],
+                new String[] {NAMES[expected_order[i]]},
+                1,
                 full_row_set(
                     expected_order, 
                     0, 
@@ -1177,10 +1185,11 @@
                 true);
 
             // '<=' test
-            checkOneParamQuery(
+            checkParamQuery(
                 conn, 
                 "SELECT ID, NAME FROM CUSTOMER where NAME <= ? ORDER BY NAME",
-                NAMES[expected_order[i]],
+                new String[] {NAMES[expected_order[i]]},
+                1,
                 full_row_set(
                     expected_order, 
                     0, 
@@ -1189,10 +1198,11 @@
                 true);
 
             // '=' test
-            checkOneParamQuery(
+            checkParamQuery(
                 conn, 
                 "SELECT ID, NAME FROM CUSTOMER where NAME = ? ORDER BY NAME",
-                NAMES[expected_order[i]],
+                new String[] {NAMES[expected_order[i]]},
+                1,
                 full_row_set(
                     expected_order, 
                     i, 
@@ -1201,10 +1211,11 @@
                 true);
 
             // '>=' test
-            checkOneParamQuery(
+            checkParamQuery(
                 conn, 
                 "SELECT ID, NAME FROM CUSTOMER where NAME >= ? ORDER BY NAME",
-                NAMES[expected_order[i]],
+                new String[] {NAMES[expected_order[i]]},
+                1,
                 full_row_set(
                     expected_order, 
                     i, 
@@ -1213,10 +1224,11 @@
                 true);
 
             // '>' test
-            checkOneParamQuery(
+            checkParamQuery(
                 conn, 
                 "SELECT ID, NAME FROM CUSTOMER where NAME > ? ORDER BY NAME",
-                NAMES[expected_order[i]],
+                new String[] {NAMES[expected_order[i]]},
+                1,
                 full_row_set(
                     expected_order, 
                     i + 1, 
@@ -1415,11 +1427,12 @@
                 true);
 
             // varchar column - parameter pattern
-            checkOneParamQuery(
+            checkParamQuery(
                 conn,
                 "SELECT ID, NAME_VARCHAR FROM CUSTOMER " + 
                     "WHERE NAME_VARCHAR LIKE ?",
-                LIKE_TEST_CASES[i],
+                new String[] {LIKE_TEST_CASES[i]},
+                1,
                 full_row_single_value(
                     EXPECTED_LIKE_RESULTS[db_index][i],
                     LIKE_NAMES),
@@ -1436,11 +1449,12 @@
                 true);
 
             // long varchar column - parameter
-            checkOneParamQuery(
+            checkParamQuery(
                 conn,
                 "SELECT ID, NAME_LONGVARCHAR FROM CUSTOMER " + 
                     "WHERE NAME_LONGVARCHAR LIKE ?",
-                LIKE_TEST_CASES[i],
+                new String[] {LIKE_TEST_CASES[i]},
+                1,
                 full_row_single_value(
                     EXPECTED_LIKE_RESULTS[db_index][i],
                     LIKE_NAMES),
@@ -1457,10 +1471,11 @@
                 true);
 
             // clob column - parameter
-            checkOneParamQuery(
+            checkParamQuery(
                 conn,
                 "SELECT ID, NAME_CLOB FROM CUSTOMER WHERE NAME_CLOB LIKE ?",
-                LIKE_TEST_CASES[i],
+                new String[] {LIKE_TEST_CASES[i]},
+                1,
                 full_row_single_value(
                     EXPECTED_LIKE_RESULTS[db_index][i],
                     LIKE_NAMES),
@@ -1479,10 +1494,11 @@
 
             // char column, char includes blank padding so alter all these
             // tests cases to match for blanks at end also.
-            checkOneParamQuery(
+            checkParamQuery(
                 conn,
                 "SELECT ID, NAME_CHAR FROM CUSTOMER WHERE NAME_CHAR LIKE ?",
-                LIKE_CHAR_TEST_CASES[i] + "%",
+                new String[] {LIKE_CHAR_TEST_CASES[i] + "%"},
+                1,
                 full_row_single_value(
                     EXPECTED_LIKE_RESULTS[db_index][i],
                     LIKE_NAMES),
@@ -1498,6 +1514,8 @@
             "SELECT * from SYS.SYSCOLUMNS where COLUMNNAME like ?";
         String zero_row_syscat_query_param2 = 
             "SELECT * from SYS.SYSCOLUMNS where ? like COLUMNNAME";
+        String zero_row_syscat_query_param3 = 
+            "SELECT count(*) from SYS.SYSCOLUMNS where ? like ?";
 
         if (!isDatabaseBasicCollation(conn))
         {
@@ -1507,17 +1525,35 @@
 
             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);
+            //The following 2 queries will work because ? in the query will
+            //take it's collation from the context, which in this case would
+            //mean from COLUMNNAME column in SYS.SYSCOLUMNS
+            //
+            checkParamQuery(
+                    conn, zero_row_syscat_query_param1, 
+                    new String[] {"nonmatchiing"}, 1, null, true);
+            checkParamQuery(
+                    conn, zero_row_syscat_query_param2, 
+                    new String[] {"nonmatchiing"}, 1, null, true);
+            checkParamQuery(
+                    conn, zero_row_syscat_query_param3, 
+                    new String[] {"nonmatching", "nonmatching"}, 2, 
+                    new String[][] {{"124"}}, true);
         }
         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);
+            checkParamQuery(
+                    conn, zero_row_syscat_query_param1, 
+                    new String[] {"nonmatchiing"}, 1, null, true);
+            checkParamQuery(
+                    conn, zero_row_syscat_query_param2, 
+                    new String[] {"nonmatchiing"}, 1, null, true);
+            checkParamQuery(
+                    conn, zero_row_syscat_query_param3, 
+                    new String[] {"nonmatching", "nonmatching"}, 2, 
+                    new String[][] {{"124"}}, true);
         }
 
         dropLikeTable(conn);



Mime
View raw message