You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Mamta A. Satoor (JIRA)" <ji...@apache.org> on 2007/02/14 21:23:05 UTC
[jira] Created: (DERBY-2335) Compare character datatypes with
different collation ordering.
Compare character datatypes with different collation ordering.
--------------------------------------------------------------
Key: DERBY-2335
URL: https://issues.apache.org/jira/browse/DERBY-2335
Project: Derby
Issue Type: Sub-task
Components: SQL
Affects Versions: 10.3.0.0
Reporter: Mamta A. Satoor
The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
For instance
Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
Thanks to Rick for taking the time out on this issue. He had following suggestion
</Rick comment start>
"As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
</Rick comment end>
When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-2335:
-----------------------------------
Attachment: DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt
DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt
Attaching patch(DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt) which does couple of things
1)It puts the correct collation type for character constants and for persistent character columns. For character constants, the collation type will be the collation type of the schema in which the query is getting compiled. This happens in
CharConstantNode.bindExpression. The setting of collation type for the persistent columns happens in a constructor for the DataTypeDescriptor. This could be found in the patch which has changes for DataTypeDescriptor and TypeDescriptorImpl.
2)Collation related methods (like and stringCompare) on collation sensitive DVDs has been copied into WorkHorseForCollatorDatatypes. These methods get called by CollatorSQLxxx classes.
3)The final change went into CollationTest. The change now allows this test to test collations for Norway, English and Polish.
I will add more tests for these changes in a subsequent patch.
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12494573 ]
Mamta A. Satoor commented on DERBY-2335:
----------------------------------------
Myrna noticed following javadoc warning
I believe revision 535738 caused a number of javadoc warnings:
[javadoc] C:\nightlies\main\src\opensource\java\engine\org\apache\derby\iapi\types\CollatorSQLChar.java:145: warning - Tag @see: missing '#': "SQLChar.stringCompare(SQLChar, SQLChar)"
[javadoc] C:\nightlies\main\src\opensource\java\engine\org\apache\derby\iapi\types\CollatorSQLChar.java:145: warning - Tag @see: can't find SQLChar.stringCompare(SQLChar, SQLChar) in org.apache.derby.iapi.types.CollatorSQLChar
[javadoc] C:\nightlies\main\src\opensource\java\engine\org\apache\derby\iapi\types\CollatorSQLClob.java:145: warning - Tag @see: missing '#': "SQLChar.stringCompare(SQLChar, SQLChar)"
[javadoc] C:\nightlies\main\src\opensource\java\engine\org\apache\derby\iapi\types\CollatorSQLClob.java:145: warning - Tag @see: can't find SQLChar.stringCompare(SQLChar, SQLChar) in org.apache.derby.iapi.types.CollatorSQLClob
[javadoc] C:\nightlies\main\src\opensource\java\engine\org\apache\derby\iapi\types\CollatorSQLLongvarchar.java:145: warning - Tag @see: missing '#': "SQLChar.stringCompare(SQLChar, SQLChar)"
[javadoc] C:\nightlies\main\src\opensource\java\engine\org\apache\derby\iapi\types\CollatorSQLLongvarchar.java:145: warning - Tag @see: can't find SQLChar.stringCompare(SQLChar, SQLChar) in org.apache.derby.iapi.types.CollatorSQLLongvarchar
[javadoc] C:\nightlies\main\src\opensource\java\engine\org\apache\derby\iapi\types\CollatorSQLVarchar.java:145: warning - Tag @see: missing '#': "SQLChar.stringCompare(SQLChar, SQLChar)"
[javadoc] C:\nightlies\main\src\opensource\java\engine\org\apache\derby\iapi\types\CollatorSQLVarchar.java:145: warning - Tag @see: can't find SQLChar.stringCompare(SQLChar, SQLChar) in org.apache.derby.iapi.types.CollatorSQLVarchar
[javadoc] C:\nightlies\main\src\opensource\java\engine\org\apache\derby\iapi\types\WorkHorseForCollatorDatatypes.java:86: warning - Tag @see: missing '#': "SQLChar.stringCompare(SQLChar, SQLChar)"
[javadoc] C:\nightlies\main\src\opensource\java\engine\org\apache\derby\iapi\types\WorkHorseForCollatorDatatypes.java:86: warning - Tag @see: can't find SQLChar.stringCompare(SQLChar, SQLChar) in org.apache.derby.iapi.types.WorkHorseForCollatorDatatypes
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12496341 ]
Mamta A. Satoor commented on DERBY-2335:
----------------------------------------
Commited additional tests with revision 538640 which checks the collation type of the database. It will be either UCS_BASIC or TERRITORY_BASED.
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt, DERBY2335_Use_LCC_in_ValueNode_v1_diff.txt, DERBY2335_Use_LCC_in_ValueNode_v1_stat.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_diff.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_stat.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v2_diff.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v2_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-2335:
----------------------------------
I'll try out the latest patch.
A couple of quick comments:
looks like you included the import of ExpressionClassBuilder twice in TypeCompiler.
Also doesn't look like you updated javadoc to reflect the new parameter.
What exactly is the rule about including impl reference in interfaces defined in :
opensource/java/engine/org/apache/derby/iapi/sql/compile. I see you added an include of
TypeCompiler.java:import org.apache.derby.impl.sql.compile.ExpressionClassBuilder;
I see in this directory there are a couple of other examples, each with a comment about we really should not do it, but went
ahead and did it:
NodeFactory.java:/* Strictly speaking we shouldn't import classes under the impl
hierarchy here
NodeFactory.java:import org.apache.derby.impl.sql.compile.QueryTreeNode;
NodeFactory.java:import org.apache.derby.impl.sql.compile.ResultColumnList;
NodeFactory.java:import org.apache.derby.impl.sql.compile.ResultSetNode;
Parser.java:/* Strictly speaking we shouldn't import classes under the impl hier
archy here
Parser.java:import org.apache.derby.impl.sql.compile.StatementNode;
When I looked at Dan's suggestion I also ran into this issue. Your changes seemed the obvious way to pass in the needed
info, but ran into this interface change that needed to reference an internal implementation. Longer term does this mean that
maybe the CLass builder should have an interface also?
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt, DERBY2335_Use_LCC_in_ValueNode_v1_diff.txt, DERBY2335_Use_LCC_in_ValueNode_v1_stat.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_diff.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-2335:
-----------------------------------
Attachment: DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt
DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt
Commited (revision 537296) the attached patch DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt which addresses 3 issues and adds some tests
1)Easiest first, fixed the javadoc error in WorkHorseForCollatorDatatypes.java
2)CharConstantNode in it's bind method does the collation setting based on the compilation schema. But it didn't do the switching of it's value from SQLChar/SQLVarchar/SQLLongvarchar/SQLClob to CollatorSQLChar/CollatoSQLVarchar/CollatoSQLLongvarchar/CollatoSQLClob
if the collation type for it ends up being territory based. By default, the value associated with CharConstantNode is always UCS_BASIC collation. It should get switched to territory based and my fix in this class does that job.
3)DataTypeDesciptor.getNull value currently gets the DVD using typeId.getNull(). But we should check if we are dealing with territory based collation and if yes, then we should change the DVD type returned by typeId.getNull from
SQLChar/SQLVarchar/SQLLongvarchar/SQLClob to CollatorSQLChar/CollatoSQLVarchar/CollatoSQLLongvarchar/CollatoSQLClob. My change in DataTypeDescriptor.getNull does that job.
4)In addition, I have added tests in CollationTest class to do some persistent character columns testing. Some tests are commented out and will be added later.
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12495197 ]
Daniel John Debrunner commented on DERBY-2335:
----------------------------------------------
Why would DataTypeDescriptor.getNull() ever throw an exception?
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-2335:
-----------------------------------
Attachment: DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v2_stat.txt
DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v2_diff.txt
Committed attached patch (DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v2_diff.txt) with revision 538325 and following comments
DERBY-2335
Made changes such that rather than having a new method in BaseTypeCompiler to push the DVD on the stack at code generation time, we use the existing method that accomplishes the same task in ExpressionClassBuilder. The junit tests have run fine with these changes and the stack trace experienced by Army in DERBY-2335 has been fixed by this fix. The reason for stack trace was that the lifetime of a BaseTypeCompiler is longer than a single class generation and I was trying to hold a reference to a declared method from MethodBuilder.describeMethod across the generated classes. This discussion can be found at
http://www.nabble.com/DERBY-1478-subtask-DERBY-2583---need-help-in-debugging-stack-trace-thrown-during-code-generation-p10611184.html
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt, DERBY2335_Use_LCC_in_ValueNode_v1_diff.txt, DERBY2335_Use_LCC_in_ValueNode_v1_stat.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_diff.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_stat.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v2_diff.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v2_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12495206 ]
Mamta A. Satoor commented on DERBY-2335:
----------------------------------------
I missed that CharConstantNode (and every QueryTreeNode) has a getLanguageConnection() method. I will go ahead and fix that in next patch.
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-2335:
-----------------------------------
Attachment: DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_stat.txt
DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_diff.txt
I am attaching a temporary patch DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_diff.txt (not ready for commit yet since the code needs to be cleaned up a little.) I want to go ahead and post it so Mike can try it out for his testing without running into stack trace that Army ran into earlier this week. This patch uses the existing method PushDataValueFactory in ExpressionClassBuilder rather than the new method in BaseTypeCompiler. Mike, please let me know how this patch works with your testing. Once I have cleanedup the code and run few tests of my own, I will go ahead and commit it.
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt, DERBY2335_Use_LCC_in_ValueNode_v1_diff.txt, DERBY2335_Use_LCC_in_ValueNode_v1_stat.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_diff.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12495192 ]
Mamta A. Satoor commented on DERBY-2335:
----------------------------------------
The change in DataTypeDescriptor.getNull method now has it throw a StandardException. This exception should be handled by the callers of this method and that's what the change in EmbedResultSet.java is doing. Please let me know if you or anyone else have any further questions.
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12496130 ]
Mamta A. Satoor commented on DERBY-2335:
----------------------------------------
Mike, I was planning of doing the javadoc fix and removing the redundant method in BaseTypeCompiler as part of my cleanup. Didn't realize I accidentally included 2 imports. I will take care of that too and see if anything else needs cleanup.
As for including impl reference in iapi, I am not too sure about the rules here. The advantage of using ExpressionClassBuilder is that the reference to the method descriptor can be hold on to because it is for per generated class level.
The junit tests have passed fine with this patch and I am running derbyall right now. I will go ahead and cleanup the code and check it in. If there is further concern about impl and iapi boundaries, we can take it up as a subsequent discussion/patch.
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt, DERBY2335_Use_LCC_in_ValueNode_v1_diff.txt, DERBY2335_Use_LCC_in_ValueNode_v1_stat.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_diff.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12494598 ]
A B commented on DERBY-2335:
----------------------------
Hi Mamta,
Thank you for your continued diligence with this work!
I was inspired by the test cases that you just checked in so I played a little with collation sorting out of curiosity. Things are looking good :)
At one point it occurred to me try to create an XML column in a Polish database. The table creation was fine but when I tried to insert into the table, I got an error:
ERROR XJ001: <polish-snipped> Java: 'org.apache.derby.iapi.services.classfile.CONSTANT_Utf8_info: java.lang.ClassCastException'.
I then deleted the database and started over, and I was able to insert without problem. However, when I then created a table with a varchar column and tried to select from it with WHERE clause, I got the same error as above.
So both statements worked fine in one scenario, but failed in another. I'm not entirely clear on what's going on here, but the order of the statements does matter. For example:
Scenario 1: Insert into the XML table succeeds, then select with a WHERE clause fails.
ij> connect 'poldb;create=true;territory=pl;collation=TERRITORY_BASED';
ij> create table xt (x xml, id int);
0 rows inserted/updated/deleted
ij> insert into xt values (xmlparse(document '<my>Acorn</my>' preserve whitespace), 1);
1 row inserted/updated/deleted
ij> create table t1 (id int, vc varchar(20));
0 rows inserted/updated/deleted
ij> select * from t1 where vc < 'aacorn';
ERROR XJ001: <polish-snipped> Java: 'org.apache.derby.iapi.services.classfile.CONSTANT_Utf8_info: java.lang.ClassCastException'.
Scenario 2: Select with a WHERE clause succeeds, then insert into XML table fails.
ij> connect 'poldb;create=true;territory=pl;collation=TERRITORY_BASED';
ij> create table t1 (id int, vc varchar(20));
0 rows inserted/updated/deleted
ij> select * from t1 where vc < 'aacorn';
ID |VC
--------------------------------
0 rows selected
ij> create table xt (x xml, id int);
0 rows inserted/updated/deleted
ij> insert into xt values (xmlparse(document '<my>Acorn</my>' preserve whitespace), 1);
ERROR XJ001: <polish-snipped> Java: 'org.apache.derby.iapi.services.classfile.CONSTANT_Utf8_info: java.lang.ClassCastException'.
Note that shutting down the database and re-connecting does not help for either scenario:
ij> connect 'poldb;shutdown=true';
ij> disconnect all;
ij> connect 'poldb';
ij> insert into xt values (xmlparse(document '<my>Acorn</my>' preserve whitespace), 1);
ERROR XJ001: <polish-snipped> Java: 'org.apache.derby.iapi.services.classfile.CONSTANT_Utf8_info: java.lang.ClassCastException'.
But exiting ij and then re-connecting from a new ij session does:
ij> exit;
java -Dij.protocol=jdbc:derby: org.apache.derby.tools.ij
ij version 10.3
ij> connect 'poldb';
ij> insert into xt values (xmlparse(document '<my>Acorn</my>' preserve whitespace), 1);
1 row inserted/updated/deleted
I'm not sure if this is a problem in language, store, or XML. I'm also not sure if this is just because collation work is still in progress--maybe this is something that will be addressed as part of upcoming changes?
In any event, just thought I'd bring it up since I noticed it.
The full stack trace for the error is:
2007-05-09 23:01:46.301 GMT Thread[main,5,main] (XID = 144), (SESSIONID = 0), (DATABASE = poldb), (DRDAID = null), Failed Statement is: select * from t1 where vc < 'aacorn'
java.lang.ClassCastException: org.apache.derby.iapi.services.classfile.CONSTANT_Utf8_info
at org.apache.derby.impl.services.bytecode.CodeChunk.getTypeDescriptor(CodeChunk.java:1007)
at org.apache.derby.impl.services.bytecode.CodeChunk.getVariableStackDelta(CodeChunk.java:1059)
at org.apache.derby.impl.services.bytecode.CodeChunk.stackWordDelta(CodeChunk.java:992)
at org.apache.derby.impl.services.bytecode.CodeChunk.findMaxStack(CodeChunk.java:954)
at org.apache.derby.impl.services.bytecode.CodeChunk.complete(CodeChunk.java:810)
at org.apache.derby.impl.services.bytecode.BCMethod.complete(BCMethod.java:241)
at org.apache.derby.impl.sql.compile.BinaryRelationalOperatorNode.generateQualMethod(BinaryRelationalOperatorNode.java:747)
at org.apache.derby.impl.sql.compile.PredicateList.generateSingleQualifierCode(PredicateList.java:2817)
at org.apache.derby.impl.sql.compile.PredicateList.generateQualifiers(PredicateList.java:3066)
at org.apache.derby.impl.sql.compile.BaseJoinStrategy.fillInScanArgs1(BaseJoinStrategy.java:121)
at org.apache.derby.impl.sql.compile.NestedLoopJoinStrategy.getScanArgs(NestedLoopJoinStrategy.java:248)
at org.apache.derby.impl.sql.compile.FromBaseTable.getScanArguments(FromBaseTable.java:3445)
at org.apache.derby.impl.sql.compile.FromBaseTable.generateResultSet(FromBaseTable.java:3135)
at org.apache.derby.impl.sql.compile.FromBaseTable.generate(FromBaseTable.java:3062)
at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1351)
at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1302)
at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generateMinion(ProjectRestrictNode.java:1351)
at org.apache.derby.impl.sql.compile.ProjectRestrictNode.generate(ProjectRestrictNode.java:1302)
at org.apache.derby.impl.sql.compile.ScrollInsensitiveResultSetNode.generate(ScrollInsensitiveResultSetNode.java:109)
at org.apache.derby.impl.sql.compile.CursorNode.generate(CursorNode.java:564)
at org.apache.derby.impl.sql.compile.StatementNode.generate(StatementNode.java:347)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:447)
at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:88)
at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:742)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:567)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:516)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:330)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:531)
at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:376)
at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:271)
at org.apache.derby.impl.tools.ij.Main.go(Main.java:215)
at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:181)
at org.apache.derby.impl.tools.ij.Main14.main(Main14.java:56)
at org.apache.derby.tools.ij.main(ij.java:71)
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12494635 ]
Mamta A. Satoor commented on DERBY-2335:
----------------------------------------
Army, thanks for spending some time testing with XML. I spent couple hrs and unfortunately can't pinpoint right away what is causing this. I tried using -Dderby.debug.true=DumpClassFile but looks like if there is an error during the code generation and we don't generate any class for the problem query. It will be nice if there was an even partial dump of what ever code has been generated for the query even if it runs into problem because that would help with debugging. Oh well..
I will put this on my todo list but in the meantime, if you/anyone find any more problems, do let me know.
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor closed DERBY-2335.
----------------------------------
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assignee: Mamta A. Satoor
> Fix For: 10.3.0.0
>
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt, DERBY2335_Use_LCC_in_ValueNode_v1_diff.txt, DERBY2335_Use_LCC_in_ValueNode_v1_stat.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_diff.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_stat.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v2_diff.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v2_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493966 ]
Mamta A. Satoor commented on DERBY-2335:
----------------------------------------
Committed patch DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt with revision 535738.
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-2335:
----------------------------------
the DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_diff.txt works for all cases in my new test. I have just submitted the test - CollationTest2.java. I have more cases to add but I was seeing the previous issue in multiple of the current cases.
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt, DERBY2335_Use_LCC_in_ValueNode_v1_diff.txt, DERBY2335_Use_LCC_in_ValueNode_v1_stat.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_diff.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-2335:
-----------------------------------
Attachment: DERBY2335_Use_LCC_in_ValueNode_v1_stat.txt
DERBY2335_Use_LCC_in_ValueNode_v1_diff.txt
Committed (revision 537940) patch DERBY2335_Use_LCC_in_ValueNode_v1_diff.txt using following comments
CharConstantNode (and every QueryTreeNode) has a getLanguageConnection() method that provides access to the current language connection. That call should get used in bindExpression rather than ConnectionUtil to get current language connection.
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt, DERBY2335_Use_LCC_in_ValueNode_v1_diff.txt, DERBY2335_Use_LCC_in_ValueNode_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor updated DERBY-2335:
-----------------------------------
Attachment: DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt
DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt
Committed patch DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt with revision 536661 and it has following commit comments
"DERBY-2335 This commit adds some basics tests and fixes a bug that I came across while writing the tests. The problem is that SQLChar has a method called stringCompare(SQLChar, SQLChar). This method compares the 2 passed parameters. When I implemented this method for CollatorSQLxxx classes, I was making the assumption that first parameter SQLChar is same as the SQLChar object on which the method is getting called and hence I was ignoring the 1st parameter and comparing the object on which stringCompare is called with the 2nd paramter to stringCompare method. But that assumption is not correct. I discovered that when I wrote a test like following
select id, name from customer where 'a'='b'
The where clause for territory based collated db always returned true because of the bug/wrong assumption. This is because stringCompare was getting called with first parameter as SQLChar 'b' and 2nd parameter as SQLChar 'a' and
stringCompare was getting called off of the object for 'a' and hence, at runtime the comparison was happening between 2 'a's rather than 'a' and 'b'. In order to fix this, I am having stringCompare on ColatorSQLxxx methods use both the passed SQLChars to do the comparison rather than ignorning the first SQLChar(just like what we do for existing SQLChar classes). This fixes the problem.
In addition, I have enabled the testing for territory based collated db in CollationTest added by Kathey (Thanks for starting this test off, Kathey). There are 3 databases with territory based collation, for Polish, Norway and English.
And lastly, I have added 2 simple character constant tests for each of the 4 databases (3 territory based collated dbs and one default collation db). One of these 2 simple tests will give different results based on the collation used.
"
If anyone has any comments, please let me know.
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12494843 ]
Mamta A. Satoor commented on DERBY-2335:
----------------------------------------
Just wanted to provide an update that I am able to reproduce the stack trace that Army ran into with following test case
create table localize1(nc10 char(10));
insert into localize1 values ('a');
select nc10 from localize1;
It looks like the combination of insert and select is causing this problem. I have a vague feeling that it has to do with code generation for collation sensitive characters but I don't know for sure. Will investigate more.
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor resolved DERBY-2335.
------------------------------------
Resolution: Fixed
Fix Version/s: 10.3.0.0
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assignee: Mamta A. Satoor
> Fix For: 10.3.0.0
>
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt, DERBY2335_Use_LCC_in_ValueNode_v1_diff.txt, DERBY2335_Use_LCC_in_ValueNode_v1_stat.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_diff.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v1_stat.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v2_diff.txt, DERBY2335_Use_PushDataValueFactory_in_ExpressionClassBuilder_v2_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-2335:
----------------------------------
Could you explain a little the EmbedResultSet.java change in change number 537296.
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12495200 ]
Mamta A. Satoor commented on DERBY-2335:
----------------------------------------
That's because DataTypeDescriptor.getNull() has following call which throws an exception and it needs to be handled by DataTypeDescriptor.getNull()
ConnectionUtil.getCurrentLCC()
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Mamta A. Satoor (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mamta A. Satoor reassigned DERBY-2335:
--------------------------------------
Assignee: Mamta A. Satoor
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2335) Compare character datatypes with
different collation ordering.
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2335?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12495203 ]
Daniel John Debrunner commented on DERBY-2335:
----------------------------------------------
That's a major change for that method, previously there was no requirement a current connection was on the stack and now there is.
The type system is meant to be somewhat standalone and not depend on other modules such as the language.
I also see CharConstantNode calls ConnectionUtil.getCurrentLCC(), but there's no need to do that.
CharConstantNode (and every QueryTreeNode) has a getLanguageConnection() method that provides access to the current language connection.
> Compare character datatypes with different collation ordering.
> --------------------------------------------------------------
>
> Key: DERBY-2335
> URL: https://issues.apache.org/jira/browse/DERBY-2335
> Project: Derby
> Issue Type: Sub-task
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Mamta A. Satoor
> Assigned To: Mamta A. Satoor
> Attachments: DERBY2335_correct_collation_for_constants_persistent_column_v1_diff.txt, DERBY2335_correct_collation_for_constants_persistent_column_v1_stat.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_diff.txt, DERBY2335_fix_stringCompare_Method_CollatorSQLxxx_classes_v1_stat.txt, DERBY2335_More_tests_And_Fix_getNull_v1_diff.txt, DERBY2335_More_tests_And_Fix_getNull_v1_stat.txt
>
>
> The parent task DERBY-1478 will enable users to have a different collation order for user-defined character datatypes compared to UNICODE based collation, UCS_BASIC, used by system tables. This sub-task is added to handle the case where a comparison is made between character datatypes with different collation order.
> For instance
> Let's say, a database is created to use a territory based collation for character types. And say there is a userSchema schema in that database which has a table tableInfo with column tablename defined as VARCHAR. This tableInfo.tablename will have territory based collation assoicated with it. And say this column is then compared with a VARCHAR column in SYS schema, then how will the comparison happen, since the 2 columns being compared have different collation associated with them?
> select * from sys.systables and userSchema.tableInfo where systables.tablename = tableInfo,tablename
> Thanks to Rick for taking the time out on this issue. He had following suggestion
> </Rick comment start>
> "As I read part 2 of the SQL Standard, it looks like you need a CAST in order to compare 2 strings which have different collations bound to them. Both string operands must have the same collation--that is my reading of Syntax rule 3b in section 9.13. Sections 6.12 and 6.1 explain how to cast the operands so that you can compare them. I think you need to write an expression like this:
> WHERE userStringCol = CAST ( systemStringCol AS VARCHAR COLLATE userStringColumnsCollation )
> Here's an example I googled up: http://docs.openlinksw.com/virtuoso/sqlrefDATATYPES.html. Hope this helps.
> </Rick comment end>
> When this task is taken up, it would be good to explore Rick's suggestion.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.