You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by km...@apache.org on 2007/09/11 21:29:29 UTC
svn commit: r574674 - in /db/derby/code/branches/10.3/java:
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/tests/lang/
testing/org/apache/derbyTesting/functionTests/tests/tools/
Author: kmarsden
Date: Tue Sep 11 12:29:28 2007
New Revision: 574674
URL: http://svn.apache.org/viewvc?rev=574674&view=rev
Log:
DERBY-2972 Update or select with function in the where clause causes with TERRITORY_BASED collation fails with ERROR 42818: Comparisons between 'VARCHAR' and 'VARCHAR' are not supported.
Modified:
db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/JavaToSQLValueNode.java
db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/JavaValueNode.java
db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java
db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/NewInvocationNode.java
db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportProcedureTest.java
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/JavaToSQLValueNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/JavaToSQLValueNode.java?rev=574674&r1=574673&r2=574674&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/JavaToSQLValueNode.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/JavaToSQLValueNode.java Tue Sep 11 12:29:28 2007
@@ -23,6 +23,7 @@
import org.apache.derby.iapi.sql.dictionary.DataDictionary;
import org.apache.derby.iapi.types.DataTypeDescriptor;
+import org.apache.derby.iapi.types.StringDataValue;
import org.apache.derby.iapi.types.TypeId;
import org.apache.derby.iapi.sql.compile.TypeCompiler;
import org.apache.derby.iapi.reference.SQLState;
@@ -250,9 +251,15 @@
throw StandardException.newException(SQLState.LANG_NO_CORRESPONDING_S_Q_L_TYPE,
javaNode.getJavaTypeName());
}
-
+ // For functions returning string types we should set the collation to match the
+ // java method's schema DERBY-2972. This is propogated from
+ // RoutineAliasInfo to javaNode.
+ if (dts.getTypeId().isStringTypeId()){
+ dts.setCollationType(javaNode.getCollationType());
+ dts.setCollationDerivation(StringDataValue.COLLATION_DERIVATION_IMPLICIT);
+ }
setType(dts);
-
+
return this;
}
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/JavaValueNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/JavaValueNode.java?rev=574674&r1=574673&r2=574674&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/JavaValueNode.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/JavaValueNode.java Tue Sep 11 12:29:28 2007
@@ -74,6 +74,9 @@
/* Name of field holding receiver value, if any */
private LocalField receiverField;
+ // * Collation type of schema where method is defined.
+ private int collationType;
+
public boolean isPrimitiveType() throws StandardException
{
JSQLType myType = getJSQLType();
@@ -444,4 +447,25 @@
SQLState.LANG_JAVA_METHOD_CALL_OR_FIELD_REF
);
}
+
+ /**
+ * @return collationType as set by setCollationType
+ */
+ public int getCollationType() {
+ return collationType;
+ }
+
+ /**
+ * Set the collation type.
+ * This will be used to determine the collation type for
+ * the SQLToJavaValueNode.
+ *
+ * @param type one of <code>StringDataValue.COLLATION_TYPE_UCS_BASIC </code> or
+ * <code>StringDataValue.COLLATION_TYPE_TERRITORY_BASED </code>
+ */
+ public void setCollationType(int type) {
+ collationType = type;
+ }
+
+
}
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java?rev=574674&r1=574673&r2=574674&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java Tue Sep 11 12:29:28 2007
@@ -735,9 +735,13 @@
// type we need to promote to an object so we can return null.
if (promoteName != null)
typeName = promoteName;
- }
+ //propogate collation type from RoutineAliasInfo to
+ // MethodCallNode DERBY-2972
+ if (routineInfo.getReturnType() != null)
+ setCollationType(routineInfo.getReturnType().getCollationType());
+ }
setJavaTypeName( typeName );
-
+
methodParameterTypes = classInspector.getParameterTypes(method);
for (int i = 0; i < methodParameterTypes.length; i++)
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/NewInvocationNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/NewInvocationNode.java?rev=574674&r1=574673&r2=574674&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/NewInvocationNode.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/NewInvocationNode.java Tue Sep 11 12:29:28 2007
@@ -37,6 +37,8 @@
import org.apache.derby.iapi.sql.compile.CompilerContext;
import org.apache.derby.iapi.sql.dictionary.TableDescriptor;
+import org.apache.derby.iapi.types.DataTypeDescriptor;
+import org.apache.derby.iapi.types.StringDataValue;
import org.apache.derby.iapi.reference.SQLState;
@@ -45,6 +47,7 @@
import org.apache.derby.iapi.util.JBitSet;
import org.apache.derby.catalog.AliasInfo;
+import org.apache.derby.catalog.TypeDescriptor;
import java.lang.reflect.Member;
import java.lang.reflect.Modifier;
@@ -332,8 +335,15 @@
" actual is " + classInspector.getType(method));
}
setJavaTypeName( javaClassName );
-
- return this;
+ if (routineInfo != null)
+ {
+ TypeDescriptor returnType = routineInfo.getReturnType();
+ if (returnType != null)
+ {
+ setCollationType(returnType.getCollationType());
+ }
+ }
+ return this;
}
/**
Modified: db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java?rev=574674&r1=574673&r2=574674&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java (original)
+++ db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java Tue Sep 11 12:29:28 2007
@@ -278,8 +278,9 @@
returnType.isNullable(),
returnType.getMaximumWidth()
);
-
-
+ // DERBY-2972 Match the collation of the RoutineAliasInfo
+ returnValueDtd.setCollationType(returnType.getCollationType());
+ returnValueDtd.setCollationDerivation(StringDataValue.COLLATION_DERIVATION_IMPLICIT);
ValueNode returnValueToSQL = (ValueNode) getNodeFactory().getNode(
C_NodeTypes.JAVA_TO_SQL_VALUE_NODE,
this,
@@ -296,7 +297,7 @@
C_NodeTypes.SQL_TO_JAVA_VALUE_NODE,
returnValueCastNode,
getContextManager());
-
+ returnValueToJava.setCollationType(returnType.getCollationType());
return returnValueToJava.bindExpression(fromList, subqueryList, aggregateVector);
}
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=574674&r1=574673&r2=574674&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 Tue Sep 11 12:29:28 2007
@@ -109,6 +109,13 @@
new String[][] {{"4","Acorn"},{"0","Smith"},{"1","Zebra"},
{"6","aacorn"}, {"2","\u0104corn"},{"5","\u015Amith"},{"3","\u017Bebra"} });
+ // Order by expresssion
+ s.executeUpdate("CREATE FUNCTION mimic(val VARCHAR(32000)) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.CollationTest.mimic' LANGUAGE JAVA PARAMETER STYLE JAVA");
+ checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER ORDER BY MIMIC(NAME)",
+ new String[][] {{"4","Acorn"},{"0","Smith"},{"1","Zebra"},
+ {"6","aacorn"}, {"2","\u0104corn"},{"5","\u015Amith"},{"3","\u017Bebra"} });
+
+ s.executeUpdate("DROP FUNCTION mimic");
//COMPARISONS INVOLVING CONSTANTS
//In default JVM territory, 'aacorn' is != 'Acorn'
checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
@@ -289,6 +296,14 @@
new String[][] {{"6","aacorn"}, {"4","Acorn"}, {"2","\u0104corn"},
{"0","Smith"},{"5","\u015Amith"}, {"1","Zebra"},{"3","\u017Bebra"} });
+ // Order by expresssion
+ s.executeUpdate("CREATE FUNCTION mimic(val VARCHAR(32000)) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.CollationTest.mimic' LANGUAGE JAVA PARAMETER STYLE JAVA");
+ checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER ORDER BY MIMIC(NAME)",
+ new String[][] {{"6","aacorn"}, {"4","Acorn"}, {"2","\u0104corn"},
+ {"0","Smith"},{"5","\u015Amith"}, {"1","Zebra"},{"3","\u017Bebra"} });
+
+ s.executeUpdate("DROP FUNCTION mimic");
+
//COMPARISONS INVOLVING CONSTANTS
//In Polish, 'aacorn' is != 'Acorn'
checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
@@ -354,6 +369,14 @@
new String[][] {{"4","Acorn"}, {"2","\u0104corn"},{"0","Smith"},
{"5","\u015Amith"}, {"1","Zebra"},{"3","\u017Bebra"}, {"6","aacorn"} });
+ // Order by expresssion
+ s.executeUpdate("CREATE FUNCTION mimic(val VARCHAR(32000)) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.CollationTest.mimic' LANGUAGE JAVA PARAMETER STYLE JAVA");
+ checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER ORDER BY MIMIC(NAME)",
+ new String[][] {{"4","Acorn"}, {"2","\u0104corn"},{"0","Smith"},
+ {"5","\u015Amith"}, {"1","Zebra"},{"3","\u017Bebra"}, {"6","aacorn"} });
+
+ s.executeUpdate("DROP FUNCTION mimic");
+
//COMPARISONS INVOLVING CONSTANTS
//In Norway, 'aacorn' is != 'Acorn'
checkLangBasedQuery(s, "SELECT ID, NAME FROM CUSTOMER where 'aacorn' = 'Acorn' ",
@@ -1005,6 +1028,34 @@
new String[][] {{"1",null}});
}
+ // Test Collation for functions DERBY-2972
+ s.executeUpdate("CREATE FUNCTION HELLO () RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.CollationTest.hello' LANGUAGE JAVA PARAMETER STYLE JAVA");
+ s.executeUpdate("create table testing (a varchar(2024))");
+ s.executeUpdate("insert into testing values('hello')");
+ rs = s.executeQuery("select * from testing where a = HELLO()");
+ JDBC.assertSingleValueResultSet(rs, "hello");
+ s.executeUpdate("DROP FUNCTION hello");
+ s.executeUpdate("DROP TABLE testing");
+
+ // Test system functions. Should have UCS_BASIC collation
+ // so a statement like this won't work, we need to cast the function.
+ assertStatementError("42818",s,"VALUES case WHEN SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.stream.error.logSeverityLevel') = '50000' THEN 'LOGSHUTDOWN ERRORS' ELSE 'DONT KNOW' END");
+ // cast function output and we it will match the compilation schema and run
+ rs = s.executeQuery("VALUES case WHEN CAST(SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.stream.error.logSeverityLevel') AS VARCHAR(30000)) = '50000' THEN 'LOGSHUTDOWN ERRORS' ELSE 'DONT KNOW' END");
+ JDBC.assertSingleValueResultSet(rs,"DONT KNOW");
+
+ // Test system table function. Should have UCS_BASIC collation
+ s.executeUpdate("create table lockfunctesttable (i int)");
+ conn.setAutoCommit(false);
+ s.executeUpdate("insert into lockfunctesttable values(1)");
+ // This statement should error because of collation mismatch
+ assertStatementError("42818",s,"select * from SYSCS_DIAG.LOCK_TABLE where tablename = 'LOCKFUNCTESTTABLE'");
+ // we have to cast for it to work.
+ rs = s.executeQuery("select * from SYSCS_DIAG.LOCK_TABLE where CAST(tablename as VARCHAR(128))= 'LOCKFUNCTESTTABLE'");
+ JDBC.assertDrainResults(rs,2);
+ s.executeUpdate("drop table lockfunctesttable");
+
+
//DERBY-2910
// Test proper collation is set for implicit cast with
// UPPER(CURRENT_DATE) and concatonation.
@@ -1021,7 +1072,29 @@
assertEquals(1,JDBC.assertDrainResults(rs));
assertStatementError("42818",s,"select TABLENAME FROM SYS.SYSTABLES WHERE UPPER(CURRENT_DATE) = TABLENAME");
s.close();
+
}
+
+// methods used for function testing.
+
+/**
+ * Name says it all
+ * @return hello
+ */
+public static String hello() {
+ return "hello";
+}
+
+/**
+ * Just return the value as passed in. Used to make sure
+ * order by works properly with collation with order by expression
+ * @param val value to return
+ * @return
+ */
+public static String mimic(String val) {
+ return val;
+}
+
private void setUpTable(Statement s) throws SQLException {
Modified: db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportProcedureTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportProcedureTest.java?rev=574674&r1=574673&r2=574674&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportProcedureTest.java (original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/tools/ImportExportProcedureTest.java Tue Sep 11 12:29:28 2007
@@ -2140,6 +2140,7 @@
+ "'extinout/t1.dat' , ';', ';', null, 1) ");
assertStatementError("38000", cSt);
+ SupportFilesSetup.deleteFile("extinout/t1.dat");
Connection conn = getConnection();
conn.setAutoCommit(false);