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] Commented: (DERBY-2972) Update or select with function in the where clause causes with TERRITORY_BASED collation fails with ERROR 42818: Comparisons between 'VARCHAR' and 'VARCHAR' are not supported.
Date Wed, 05 Sep 2007 05:26:32 GMT

    [ https://issues.apache.org/jira/browse/DERBY-2972?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12524983
] 

Mamta A. Satoor commented on DERBY-2972:
----------------------------------------

Kathey asked "Why did we choose to not have the functions follow rule three and use the current
compilation schema? " 

I have copied following from the thread at http://www.nabble.com/Re%3A-some-comments-on-collation-wiki-page-p9815841.html
where the decision for function's collation was made. I guess the question at that time was
that if we chose current compilation schema, what compilation schema should be picked up ie
the current compilation schema when the function is getting defined or the current compilation
when the function is getting executed. Based on that, to avoid confusion, decision was made
to use the collation of the schema in which the function is defined. 

*******************************************************************************************************************
The character set of the schema the function is defined in. That keeps it least confusing
and easier for the users to understand. 
Mamta

 On 4/3/07, Daniel John Debrunner <djd@...> wrote: 

http://wiki.apache.org/db-derby/BuiltInLanguageBasedOrderingDERBY-1478 

> 7)For user defined functions' that return character string type, the return type's collation
> will have the same collation as current schema's character set.

The "current schema's" character set, or the character set of the schema 
the function is defined in? And if it's the current schema, is it
current at function definition time, or current when used?

Dan.

*******************************************************************************************************************


> Update or select with function in the where clause causes with TERRITORY_BASED collation
fails with ERROR 42818: Comparisons between 'VARCHAR' and 'VARCHAR' are not supported.
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-2972
>                 URL: https://issues.apache.org/jira/browse/DERBY-2972
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.4.0.0
>            Reporter: Kathey Marsden
>            Assignee: Kathey Marsden
>         Attachments: derby-2972_diff.txt, derby-2972_stat.txt
>
>
> The following update fails with ERROR 42818
> ij> update testing set a = PADSTRING('aa',2024) where a = PADSTRING('a',2024);
> ERROR 42818: Comparisons between 'VARCHAR' and 'VARCHAR' are not supported.
> See full script below 
> onnect 'jdbc:derby:nordb;territory=no_NO;collation=TERRITORY_BASED';
> CREATE FUNCTION  PADSTRING (DATA VARCHAR(32000), LENGTH INTEGER) RETURNS VARCHAR(32000)
EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Formatters.padString' LANGUAGE JAVA
PARAMETER STYLE JAVA;
> create table testing 
> 	(a varchar(2024), b varchar(1024), c varchar(1024), d varchar(2048), e varchar(300))
;
> -- insert 9 rows into the table
> insert into testing values (PADSTRING('1',2024),  PADSTRING('2',1024), 
>        PADSTRING('3',1024), PADSTRING('4',2048),  PADSTRING('5',300));
> insert into testing values (PADSTRING('10',2024),  
>        PADSTRING('20',1024), PADSTRING('30',1024), 
>        PADSTRING('40',2048), PADSTRING('50',300));
> insert into testing values (PADSTRING('100',2024),  
>        PADSTRING('200',1024), PADSTRING('300',1024), 
>        PADSTRING('400',2048), PADSTRING('500',300));
> insert into testing values (PADSTRING('1000',2024),  
>        PADSTRING('2000',1024), PADSTRING('3000',1024), 
>        PADSTRING('4000',2048), PADSTRING('5000',300));
> insert into testing values (PADSTRING('10000',2024),  
>        PADSTRING('20000',1024),	PADSTRING('30000',1024), 
>        PADSTRING('40000',2048), PADSTRING('50000',300));
> insert into testing values (PADSTRING('100000',2024), 
>        PADSTRING('200000',1024), PADSTRING('300000',1024), 
>        PADSTRING('400000',2048), PADSTRING('500000',300));
> insert into testing values (PADSTRING('1000000',2024), 
>        PADSTRING('2000000',1024), PADSTRING('3000000',1024), 
>        PADSTRING('4000000',2048), PADSTRING('5000000',300));
> insert into testing values (PADSTRING('10000000',2024), 
>        PADSTRING('20000000',1024), PADSTRING('30000000',1024), 
>        PADSTRING('40000000',2048), PADSTRING('50000000',300));
> insert into testing values (PADSTRING('100000000',2024), 
>        PADSTRING('200000000',1024), PADSTRING('300000000',1024), 
>        PADSTRING('400000000',2048), PADSTRING('500000000',300));
> update testing set a = PADSTRING('aa',2024) where a = PADSTRING('a',2024);

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message