db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-2335) Compare character datatypes with different collation ordering.
Date Wed, 09 May 2007 21:30:15 GMT

     [ 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

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:
>            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,
> 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.

View raw message