[
https://issues.apache.org/jira/browse/DERBY-2972?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12524811
]
Mamta A. Satoor commented on DERBY-2972:
----------------------------------------
Kathey, the SQL spec does not have specific blurb about user defined functions
and collations. But applying Section 6.1, syntax rules 3b) and 16) for
character string types (which is where user defined functions with return type
of character string type fall into), the collation type of return of user
defined functions is implementation defined.
In the thread titled "some comments on collation wiki page", if you got Dan's
comment
http://www.nabble.com/Re%3A-some-comments-on-collation-wiki-page-p9813465.html
and then my comment
http://www.nabble.com/Re%3A-some-comments-on-collation-wiki-page-p9815841.html,
that is where we decided Derby's implementation behavior for user defined
functions to be to pick up the collation of the schema in which the function is
defined. Hope this helps provide a background info for you.
> 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.
