[
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.
