Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <23800377.1178924715427.JavaMail.jira@brutus>
Date: Fri, 11 May 2007 16:05:15 -0700 (PDT)
From: "Daniel John Debrunner (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-2335) Compare character datatypes with
 different collation ordering.
In-Reply-To: <11316854.1171484585505.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


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