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 ma...@apache.org on 2007/05/21 18:44:58 UTC
svn commit: r540201 - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/types/ engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: mamta
Date: Mon May 21 09:44:56 2007
New Revision: 540201
URL: http://svn.apache.org/viewvc?view=rev&rev=540201
Log:
DERBY-2599
Commiting patch attached to DERBY2599_Set_collation_for_aggregates_v1_diff.txt which does the job of setting the correct collation type and
derivation for aggregates. As per SQL standard, if the operands of the aggregate methods are string operands and they do not all have the
same collation derivaiton and type on them, then the resultant string operand will have collation derivaiton of NONE. One thing that is
missing is as per SQL spec, in a comparison operator, as far as there is one operand with non-NONE collation derivation, the comparison
should work. I do not have that part working yet. If the two operands of the comparison operator do not have the same collation, the
comparison will fail. Also, if both the sides of the comparison operator have NONE collation, the current DERBY10.3 code will not catch
that. Both of these issues can go as a subsequent patch.
The aggregate methods that are covered by the patch are COALESCE, CONCATENATE, NULLIF, CASE
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CastNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java
db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNodeList.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
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=540201&r1=540200&r2=540201
==============================================================================
--- 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 Mon May 21 09:44:56 2007
@@ -369,7 +369,9 @@
precision,
scale,
isNullable,
- maximumWidth);
+ maximumWidth,
+ source.getCollationType(),
+ source.getCollationDerivation());
}
/**
@@ -385,8 +387,13 @@
{
this.typeId = source.typeId;
typeDescriptor = new TypeDescriptorImpl(source.typeDescriptor,
- isNullable,
- maximumWidth);
+ source.getPrecision(),
+ source.getScale(),
+ isNullable,
+ maximumWidth,
+ source.getCollationType(),
+ source.getCollationDerivation());
+
}
/**
@@ -663,8 +670,9 @@
scale = higherType.getScale();
}
+
higherType = new DataTypeDescriptor(higherType,
- precision, scale, nullable, maximumWidth);
+ precision, scale, nullable, maximumWidth);
return higherType;
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CastNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CastNode.java?view=diff&rev=540201&r1=540200&r2=540201
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CastNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/CastNode.java Mon May 21 09:44:56 2007
@@ -21,24 +21,15 @@
package org.apache.derby.impl.sql.compile;
-import org.apache.derby.iapi.services.context.ContextManager;
-
import org.apache.derby.iapi.services.compiler.MethodBuilder;
import org.apache.derby.iapi.services.compiler.LocalField;
-import org.apache.derby.iapi.services.monitor.Monitor;
-
import org.apache.derby.iapi.services.sanity.SanityManager;
import org.apache.derby.iapi.error.StandardException;
-import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
-
-import org.apache.derby.iapi.sql.compile.CompilerContext;
import org.apache.derby.iapi.sql.compile.C_NodeTypes;
-import org.apache.derby.iapi.sql.dictionary.DataDictionary;
-
import org.apache.derby.iapi.types.DataTypeUtilities;
import org.apache.derby.iapi.types.TypeId;
import org.apache.derby.iapi.reference.Limits;
@@ -46,26 +37,18 @@
import org.apache.derby.iapi.reference.SQLState;
import org.apache.derby.iapi.types.DataTypeDescriptor;
-import org.apache.derby.iapi.types.DataValueFactory;
import org.apache.derby.iapi.types.DataValueDescriptor;
-import org.apache.derby.iapi.types.VariableSizeDataValue;
import org.apache.derby.iapi.sql.compile.TypeCompiler;
-import org.apache.derby.iapi.reference.SQLState;
import org.apache.derby.iapi.util.StringUtil;
import org.apache.derby.iapi.reference.ClassName;
import org.apache.derby.iapi.reference.JDBC30Translation;
import org.apache.derby.iapi.services.classfile.VMOpcode;
-import org.apache.derby.iapi.types.DataValueDescriptor;
-
-import org.apache.derby.iapi.services.loader.ClassInspector;
-
import org.apache.derby.iapi.sql.compile.Visitable;
import org.apache.derby.iapi.sql.compile.Visitor;
-import org.apache.derby.iapi.sql.compile.C_NodeTypes;
import java.lang.reflect.Modifier;
@@ -75,14 +58,6 @@
import org.apache.derby.iapi.util.JBitSet;
import org.apache.derby.iapi.util.ReuseFactory;
-import org.apache.derby.catalog.AliasInfo;
-import org.apache.derby.catalog.TypeDescriptor;
-
-import org.apache.derby.iapi.types.SQLReal;
-
-import java.sql.Date;
-import java.sql.Time;
-import java.sql.Timestamp;
import java.sql.Types;
import java.util.Vector;
@@ -385,10 +360,12 @@
destCTI = castTarget.getTypeId();
sourceCTI = castOperand.getTypeId();
- //If we are dealing with result type of cast to be string data type,
- //then that data type should get it's collation type from the
- //current schema. That is what we are doing below
+ //If the result type of cast is string data type, then that data type
+ //should get it's collation type from the current schema.
if (destCTI.isStringTypeId()) {
+ //set the collation type to be same as the current schema's
+ //collation type. Collation derivation is already initialized
+ //to correct value by default which is "IMPLICIT"
getTypeServices().setCollationType(
getLanguageConnectionContext().getDefaultSchema().getCollationType());
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java?view=diff&rev=540201&r1=540200&r2=540201
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ConcatenationOperatorNode.java Mon May 21 09:44:56 2007
@@ -27,14 +27,10 @@
import org.apache.derby.iapi.error.StandardException;
-import org.apache.derby.iapi.sql.dictionary.DataDictionary;
-import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
import org.apache.derby.iapi.types.TypeId;
-import org.apache.derby.iapi.types.ConcatableDataValue;
-import org.apache.derby.iapi.types.BitDataValue;
-
import org.apache.derby.iapi.sql.compile.TypeCompiler;
+import org.apache.derby.iapi.types.StringDataValue;
import org.apache.derby.iapi.types.DataTypeDescriptor;
import org.apache.derby.iapi.services.compiler.MethodBuilder;
@@ -136,8 +132,15 @@
else
leftType = TypeId.getBuiltInTypeId(Types.VARCHAR);
}
-
- leftOperand.setType(new DataTypeDescriptor(leftType, true));
+
+ leftOperand.setType(new DataTypeDescriptor(leftType, true));
+ if (rightOperand.getTypeId().isStringTypeId())
+ {//collation of ? operand should be same as the other operand
+ leftOperand.getTypeServices().setCollationDerivation(
+ rightOperand.getTypeServices().getCollationDerivation());
+ leftOperand.getTypeServices().setCollationType(
+ rightOperand.getTypeServices().getCollationType());
+ }
}
/*
@@ -179,11 +182,17 @@
else
rightType = TypeId.getBuiltInTypeId(Types.VARCHAR);
}
-
- rightOperand.setType(
+ rightOperand.setType(
new DataTypeDescriptor(
rightType,
true));
+ if (leftOperand.getTypeId().isStringTypeId())
+ {//collation of ? operand should be same as the other operand
+ rightOperand.getTypeServices().setCollationDerivation(
+ leftOperand.getTypeServices().getCollationDerivation());
+ rightOperand.getTypeServices().setCollationType(
+ leftOperand.getTypeServices().getCollationType());
+ }
}
/* If the left operand is not a built-in type, then generate a bound conversion
@@ -476,11 +485,24 @@
** It's OK to call the implementation of the DataTypeDescriptorFactory
** here, because we're in the same package.
*/
- return new DataTypeDescriptor(
+ DataTypeDescriptor returnDTD = new DataTypeDescriptor(
TypeId.getBuiltInTypeId(higherType),
nullable,
resultLength
);
+
+ //Check if collation derivations and collation types of 2 operands match?
+ //If they do, then the result of the concatenation will get the smae
+ //collation information. But if not, then the collation derivation of
+ //the result will be NONE.
+ if (leftType.getCollationDerivation() != rightType.getCollationDerivation()
+ || leftType.getCollationType() != rightType.getCollationType())
+ returnDTD.setCollationDerivation(StringDataValue.COLLATION_DERIVATION_NONE);
+ else {
+ returnDTD.setCollationDerivation(leftType.getCollationDerivation());
+ returnDTD.setCollationType(leftType.getCollationType());
+ }
+ return returnDTD;
}
/*
Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNodeList.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNodeList.java?view=diff&rev=540201&r1=540200&r2=540201
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNodeList.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ValueNodeList.java Mon May 21 09:44:56 2007
@@ -25,12 +25,9 @@
import org.apache.derby.iapi.error.StandardException;
-import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
-
-import org.apache.derby.iapi.sql.dictionary.DataDictionary;
-
import org.apache.derby.iapi.types.DataTypeDescriptor;
import org.apache.derby.iapi.types.DataValueDescriptor;
+import org.apache.derby.iapi.types.StringDataValue;
import org.apache.derby.iapi.types.TypeId;
import org.apache.derby.iapi.sql.compile.TypeCompiler;
@@ -143,8 +140,32 @@
}
/**
- * Get the dominant DataTypeServices from the elements in the list.
- *
+ * Get the dominant DataTypeServices from the elements in the list. This
+ * method will also check if it is dealing with character string datatypes.
+ * If yes, then it will check if all the character string datatypes have
+ * the same collation derivation and collation type associated with them.
+ * If not, then the resultant DTD from this method will have collation
+ * derivation of NONE. If yes, then the resultant DTD from this method will
+ * have the same collation derivation and collation type as all the
+ * character string datatypes.
+ *
+ * eg consider we are dealing with a database with territory based
+ * collation. Now consider following example first
+ * sysCharColumn || userCharColumn
+ * The result of this concatenation will have collation derivation of NONE
+ * because the first operand has collation derivation of IMPLICIT and
+ * collation type of UCS_BASIC whereas the 2nd opernad has collation
+ * derivation of IMPLICIT and collation type of territory based. Since the
+ * 2 operands don't have matching collaiton information, the result of this
+ * concatenation will have collation derivation of NONE.
+ *
+ * Now consider following example
+ * sysCharColumn1 || sysCharColumn2
+ * Since in this example, both the operands have the same collation
+ * derivation of IMPLICIT and same collation type of UCS_BASIC, the
+ * resultant type will have collation derivation of IMPLICIT and collation
+ * type of UCS_BASIC
+ *
* @return DataTypeServices The dominant DataTypeServices.
*
* @exception StandardException Thrown on error
@@ -152,6 +173,18 @@
public DataTypeDescriptor getDominantTypeServices() throws StandardException
{
DataTypeDescriptor dominantDTS = null;
+ //Following 2 will hold the collation derivation and type of the first
+ //string operand. This collation information will be checked against
+ //the collation derivation and type of other string operands. If a
+ //mismatch is found, foundCollationMisMatch will be set to true.
+ int firstCollationDerivation = -1;
+ int firstCollationType = -1;
+ //As soon as we find 2 strings with different collations, we set the
+ //following flag to true. At the end of the method, if this flag is set
+ //to true then it means that we have operands with different collation
+ //types and hence the resultant dominant type will have to have the
+ //collation derivation of NONE.
+ boolean foundCollationMisMatch = false;
for (int index = 0; index < size(); index++)
{
@@ -162,6 +195,23 @@
continue;
DataTypeDescriptor valueNodeDTS = valueNode.getTypeServices();
+ if (valueNodeDTS.getTypeId().isStringTypeId())
+ {
+ if (firstCollationDerivation == -1)
+ {
+ //found first string type. Initialize firstCollationDerivation
+ //and firstCollationType with collation information from
+ //that first string type operand.
+ firstCollationDerivation = valueNodeDTS.getCollationDerivation();
+ firstCollationType = valueNodeDTS.getCollationType();
+ } else if (!foundCollationMisMatch)
+ {
+ if (firstCollationDerivation != valueNodeDTS.getCollationDerivation())
+ foundCollationMisMatch = true;//collation derivations don't match
+ else if (firstCollationType != valueNodeDTS.getCollationType())
+ foundCollationMisMatch = true;//collation types don't match
+ }
+ }
if (dominantDTS == null)
{
dominantDTS = valueNodeDTS;
@@ -170,6 +220,22 @@
{
dominantDTS = dominantDTS.getDominantType(valueNodeDTS, getClassFactory());
}
+ }
+
+ //if following if returns true, then it means that we are dealing with
+ //string operands.
+ if (firstCollationDerivation != -1)
+ {
+ if (foundCollationMisMatch) {
+ //if we come here that it means that alll the string operands
+ //do not have matching collation information on them. Hence the
+ //resultant dominant DTD should have collation derivation of
+ //NONE.
+ dominantDTS.setCollationDerivation(StringDataValue.COLLATION_DERIVATION_NONE);
+ }
+ //if we didn't fine any collation mismatch, then resultant dominant
+ //DTD already has the correct collation information on it and hence
+ //we don't need to do anything.
}
return dominantDTS;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java?view=diff&rev=540201&r1=540200&r2=540201
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/CollationTest.java Mon May 21 09:44:56 2007
@@ -141,6 +141,58 @@
" (TABLENAME AS CHAR(15)) = 'CUSTOMER' ",
new String[][] {{"1"} });
+ //Do some testing using CASE WHEN THEN ELSE
+ //following will work with no problem for a database with UCS_BASIC
+ //collation for system and user schemas
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE CASE " +
+ " WHEN 1=1 THEN TABLENAME ELSE 'c' END = 'SYSCOLUMNS'",
+ new String[][] {{"SYSCOLUMNS"} });
+ //Using cast for result of CASE expression in the query above would not
+ //affect the sql in any ways.
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE CAST " +
+ " ((CASE WHEN 1=1 THEN TABLENAME ELSE 'c' END) AS CHAR(12)) = " +
+ " 'SYSCOLUMNS'",
+ new String[][] {{"SYSCOLUMNS"} });
+
+ //Do some testing using CONCATENATION
+ //following will work with no problem for a database with UCS_BASIC
+ //collation for system and user schemas
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " TABLENAME || ' ' = 'SYSCOLUMNS '",
+ new String[][] {{"SYSCOLUMNS"} });
+ //Using cast for result of CAST expression in the query above would not
+ //affect the sql in any ways.
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " CAST (TABLENAME || ' ' AS CHAR(12)) = " +
+ " 'SYSCOLUMNS '",
+ new String[][] {{"SYSCOLUMNS"} });
+
+ //Do some testing using COALESCE
+ //following will work with no problem for a database with UCS_BASIC
+ //collation for system and user schemas
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " COALESCE(TABLENAME, 'c') = 'SYSCOLUMNS'",
+ new String[][] {{"SYSCOLUMNS"} });
+ //Using cast for result of COALESCE expression in the query above would not
+ //affect the sql in any ways.
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " CAST (COALESCE (TABLENAME, 'c') AS CHAR(12)) = " +
+ " 'SYSCOLUMNS'",
+ new String[][] {{"SYSCOLUMNS"} });
+
+ //Do some testing using NULLIF
+ //following will work with no problem for a database with UCS_BASIC
+ //collation for system and user schemas
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " NULLIF(TABLENAME, 'c') = 'SYSCOLUMNS'",
+ new String[][] {{"SYSCOLUMNS"} });
+ //Using cast for result of NULLIF expression in the query above would not
+ //affect the sql in any ways.
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " CAST (NULLIF (TABLENAME, 'c') AS CHAR(12)) = " +
+ " 'SYSCOLUMNS'",
+ new String[][] {{"SYSCOLUMNS"} });
+
s.close();
conn.commit();
@@ -222,6 +274,80 @@
" (TABLENAME AS CHAR(15)) = 'CUSTOMER' ",
new String[][] {{"1"} });
+ //Do some testing using CASE WHEN THEN ELSE
+ //following sql will not work for a database with territory based
+ //collation for user schemas. This is because the resultant string type
+ //from the CASE expression below will have collation derivation of NONE.
+ //The reason for collation derivation of NONE is that the CASE's 2
+ //operands have different collation types and as per SQL standards, if an
+ //aggregate method has operands with different collations, then the
+ //result will have collation derivation of NONE. The right side of =
+ //operation has collation type of territory based and hence the following
+ //sql fails.
+ assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE CASE " +
+ " WHEN 1=1 THEN TABLENAME ELSE 'c' END = 'SYSCOLUMNS'");
+ //CASTing the result of the CASE expression will solve the problem in the
+ //query above. Now both the operands around = operation will have
+ //collation type of territory based and hence the sql won't fail
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE CAST " +
+ " ((CASE WHEN 1=1 THEN TABLENAME ELSE 'c' END) AS CHAR(12)) = " +
+ " 'SYSCOLUMNS'",
+ new String[][] {{"SYSCOLUMNS"} });
+
+ //Do some testing using CONCATENATION
+ //following will fail because result string of concatenation has
+ //collation derivation of NONE. That is because it's 2 operands have
+ //different collation types. TABLENAME has collation type of UCS_BASIC
+ //but constant character string ' ' has collation type of territory based
+ //So the left hand side of = operator has collation derivation of NONE
+ //and right hand side has collation derivation of territory based and
+ //that causes the = comparison to fail
+ assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " TABLENAME || ' ' = 'SYSCOLUMNS '");
+ //CASTing the result of the concat expression will solve the problem in
+ //the query above. Now both the operands around = operation will have
+ //collation type of territory based and hence the sql won't fail
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " CAST (TABLENAME || ' ' AS CHAR(12)) = " +
+ " 'SYSCOLUMNS '",
+ new String[][] {{"SYSCOLUMNS"} });
+
+ //Do some testing using COALESCE
+ //following will fail because result string of COALESCE has
+ //collation derivation of NONE. That is because it's 2 operands have
+ //different collation types. TABLENAME has collation type of UCS_BASIC
+ //but constant character string 'c' has collation type of territory based
+ //So the left hand side of = operator has collation derivation of NONE
+ //and right hand side has collation derivation of territory based and
+ //that causes the = comparison to fail
+ assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " COALESCE(TABLENAME, 'c') = 'SYSCOLUMNS'");
+ //CASTing the result of the COALESCE expression will solve the problem in
+ //the query above. Now both the operands around = operation will have
+ //collation type of territory based and hence the sql won't fail
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " CAST (COALESCE (TABLENAME, 'c') AS CHAR(12)) = " +
+ " 'SYSCOLUMNS'",
+ new String[][] {{"SYSCOLUMNS"} });
+
+ //Do some testing using NULLIF
+ //following will fail because result string of NULLIF has
+ //collation derivation of NONE. That is because it's 2 operands have
+ //different collation types. TABLENAME has collation type of UCS_BASIC
+ //but constant character string 'c' has collation type of territory based
+ //So the left hand side of = operator has collation derivation of NONE
+ //and right hand side has collation derivation of territory based and
+ //that causes the = comparison to fail
+ assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " NULLIF(TABLENAME, 'c') = 'SYSCOLUMNS'");
+ //CASTing the result of the NULLIF expression will solve the problem in
+ //the query above. Now both the operands around = operation will have
+ //collation type of territory based and hence the sql won't fail
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " NULLIF (CAST (TABLENAME AS CHAR(12)), 'c' ) = " +
+ " 'SYSCOLUMNS'",
+ new String[][] {{"SYSCOLUMNS"} });
+
s.close();
conn.commit();
@@ -302,6 +428,80 @@
" (TABLENAME AS CHAR(15)) = 'CUSTOMER' ",
new String[][] {{"1"} });
+ //Do some testing using CASE WHEN THEN ELSE
+ //following sql will not work for a database with territory based
+ //collation for user schemas. This is because the resultant string type
+ //from the CASE expression below will have collation derivation of NONE.
+ //The reason for collation derivation of NONE is that the CASE's 2
+ //operands have different collation types and as per SQL standards, if an
+ //aggregate method has operands with different collations, then the
+ //result will have collation derivation of NONE. The right side of =
+ //operation has collation type of territory based and hence the following
+ //sql fails.
+ assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE CASE " +
+ " WHEN 1=1 THEN TABLENAME ELSE 'c' END = 'SYSCOLUMNS'");
+ //CASTing the result of the CASE expression will solve the problem in the
+ //query above. Now both the operands around = operation will have
+ //collation type of territory based and hence the sql won't fail
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE CAST " +
+ " ((CASE WHEN 1=1 THEN TABLENAME ELSE 'c' END) AS CHAR(12)) = " +
+ " 'SYSCOLUMNS'",
+ new String[][] {{"SYSCOLUMNS"} });
+
+ //Do some testing using CONCATENATION
+ //following will fail because result string of concatenation has
+ //collation derivation of NONE. That is because it's 2 operands have
+ //different collation types. TABLENAME has collation type of UCS_BASIC
+ //but constant character string ' ' has collation type of territory based
+ //So the left hand side of = operator has collation derivation of NONE
+ //and right hand side has collation derivation of territory based and
+ //that causes the = comparison to fail
+ assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " TABLENAME || ' ' = 'SYSCOLUMNS '");
+ //CASTing the result of the concat expression will solve the problem in
+ //the query above. Now both the operands around = operation will have
+ //collation type of territory based and hence the sql won't fail
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " CAST (TABLENAME || ' ' AS CHAR(12)) = " +
+ " 'SYSCOLUMNS '",
+ new String[][] {{"SYSCOLUMNS"} });
+
+ //Do some testing using COALESCE
+ //following will fail because result string of COALESCE has
+ //collation derivation of NONE. That is because it's 2 operands have
+ //different collation types. TABLENAME has collation type of UCS_BASIC
+ //but constant character string 'c' has collation type of territory based
+ //So the left hand side of = operator has collation derivation of NONE
+ //and right hand side has collation derivation of territory based and
+ //that causes the = comparison to fail
+ assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " COALESCE(TABLENAME, 'c') = 'SYSCOLUMNS'");
+ //CASTing the result of the COALESCE expression will solve the problem in
+ //the query above. Now both the operands around = operation will have
+ //collation type of territory based and hence the sql won't fail
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " CAST (COALESCE (TABLENAME, 'c') AS CHAR(12)) = " +
+ " 'SYSCOLUMNS'",
+ new String[][] {{"SYSCOLUMNS"} });
+
+ //Do some testing using NULLIF
+ //following will fail because result string of NULLIF has
+ //collation derivation of NONE. That is because it's 2 operands have
+ //different collation types. TABLENAME has collation type of UCS_BASIC
+ //but constant character string 'c' has collation type of territory based
+ //So the left hand side of = operator has collation derivation of NONE
+ //and right hand side has collation derivation of territory based and
+ //that causes the = comparison to fail
+ assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " NULLIF(TABLENAME, 'c') = 'SYSCOLUMNS'");
+ //CASTing the result of the NULLIF expression will solve the problem in
+ //the query above. Now both the operands around = operation will have
+ //collation type of territory based and hence the sql won't fail
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " NULLIF (CAST (TABLENAME AS CHAR(12)), 'c' ) = " +
+ " 'SYSCOLUMNS'",
+ new String[][] {{"SYSCOLUMNS"} });
+
s.close();
conn.commit();
@@ -384,6 +584,80 @@
checkLangBasedQuery(s, "SELECT 1 FROM SYS.SYSTABLES WHERE CAST " +
" (TABLENAME AS CHAR(15)) = 'CUSTOMER' ",
new String[][] {{"1"} });
+
+ //Do some testing using CASE WHEN THEN ELSE
+ //following sql will not work for a database with territory based
+ //collation for user schemas. This is because the resultant string type
+ //from the CASE expression below will have collation derivation of NONE.
+ //The reason for collation derivation of NONE is that the CASE's 2
+ //operands have different collation types and as per SQL standards, if an
+ //aggregate method has operands with different collations, then the
+ //result will have collation derivation of NONE. The right side of =
+ //operation has collation type of territory based and hence the following
+ //sql fails.
+ assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE CASE " +
+ " WHEN 1=1 THEN TABLENAME ELSE 'c' END = 'SYSCOLUMNS'");
+ //CASTing the result of the CASE expression will solve the problem in the
+ //query above. Now both the operands around = operation will have
+ //collation type of territory based and hence the sql won't fail
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE CAST " +
+ " ((CASE WHEN 1=1 THEN TABLENAME ELSE 'c' END) AS CHAR(12)) = " +
+ " 'SYSCOLUMNS'",
+ new String[][] {{"SYSCOLUMNS"} });
+
+ //Do some testing using CONCATENATION
+ //following will fail because result string of concatenation has
+ //collation derivation of NONE. That is because it's 2 operands have
+ //different collation types. TABLENAME has collation type of UCS_BASIC
+ //but constant character string ' ' has collation type of territory based
+ //So the left hand side of = operator has collation derivation of NONE
+ //and right hand side has collation derivation of territory based and
+ //that causes the = comparison to fail
+ assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " TABLENAME || ' ' = 'SYSCOLUMNS '");
+ //CASTing the result of the concat expression will solve the problem in
+ //the query above. Now both the operands around = operation will have
+ //collation type of territory based and hence the sql won't fail
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " CAST (TABLENAME || ' ' AS CHAR(12)) = " +
+ " 'SYSCOLUMNS '",
+ new String[][] {{"SYSCOLUMNS"} });
+
+ //Do some testing using COALESCE
+ //following will fail because result string of COALESCE has
+ //collation derivation of NONE. That is because it's 2 operands have
+ //different collation types. TABLENAME has collation type of UCS_BASIC
+ //but constant character string 'c' has collation type of territory based
+ //So the left hand side of = operator has collation derivation of NONE
+ //and right hand side has collation derivation of territory based and
+ //that causes the = comparison to fail
+ assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " COALESCE(TABLENAME, 'c') = 'SYSCOLUMNS'");
+ //CASTing the result of the COALESCE expression will solve the problem in
+ //the query above. Now both the operands around = operation will have
+ //collation type of territory based and hence the sql won't fail
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " CAST (COALESCE (TABLENAME, 'c') AS CHAR(12)) = " +
+ " 'SYSCOLUMNS'",
+ new String[][] {{"SYSCOLUMNS"} });
+
+ //Do some testing using NULLIF
+ //following will fail because result string of NULLIF has
+ //collation derivation of NONE. That is because it's 2 operands have
+ //different collation types. TABLENAME has collation type of UCS_BASIC
+ //but constant character string 'c' has collation type of territory based
+ //So the left hand side of = operator has collation derivation of NONE
+ //and right hand side has collation derivation of territory based and
+ //that causes the = comparison to fail
+ assertStatementError("42818", s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " NULLIF(TABLENAME, 'c') = 'SYSCOLUMNS'");
+ //CASTing the result of the NULLIF expression will solve the problem in
+ //the query above. Now both the operands around = operation will have
+ //collation type of territory based and hence the sql won't fail
+ checkLangBasedQuery(s, "SELECT TABLENAME FROM SYS.SYSTABLES WHERE " +
+ " NULLIF (CAST (TABLENAME AS CHAR(12)), 'c' ) = " +
+ " 'SYSCOLUMNS'",
+ new String[][] {{"SYSCOLUMNS"} });
s.close();
conn.commit();