[
https://issues.apache.org/jira/browse/DERBY-2972?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12526589
]
Kathey Marsden commented on DERBY-2972:
---------------------------------------
Per the wiki page and Mamta's suggestion the patch the collation will match the
collation of the schema where the function is defined. This means that for
TERRITORY_BASED collation database, users executing a query like:
"select * from SYSCS_DIAG.LOCK_TABLE where tablename = 'LOCKFUNCTESTTABLE'"
will need to either switch to the SYS schema or cast e.g:
select * from SYSCS_DIAG.LOCK_TABLE where CAST(tablename as VARCHAR(128))=
'LOCKFUNCTESTTABLE
The collation type is stored in the RoutineAliasInfo from an earlier patch, so
it gets the collation type from there.
To propogate the collation type from the nodes where it is available to
SQLToJavaValueNode, I added setCollationType()/getCollationType methods to
JavaValueNode.
SQLToJavaValueNode always sets the derivation to
StringDataValue.COLLATION_DERIVATION_IMPLICIT
> 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,
> derby_2972_diff.txt, derby_2972_stat.txt, tempFunction_diff.txt,
> tempFunction_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.
