db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (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 18:05:34 GMT

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

Kathey Marsden commented on DERBY-2972:
---------------------------------------

I think that matching the collation of the compilation schema of the executing statement would
be easiest for users to understand, because it would match the behavour of string literals
and system functions would ve much more likely to work. For example the statement below would
execute fine regardless of current schema if we use the compilation schema of the executing
statement (as in the attached patch).  If we use the compilation schema of the system schema
the user would have to almost always cast the result of system functions.

VALUES case WHEN SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.stream.error.logSeverityLevel')
= '50000'  THEN 'LOG SHUT
DOWN  ERRORS'
WHEN SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.stream.error.logSeverityLevel') = '40000'
THEN 'SHOW CONN CLOSE ERROR
S'
WHEN SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.stream.error.logSeverityLevel') = '30000'
THEN 'SHOW XACT ROLLBACK ER
RORS'
WHEN SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.stream.error.logSeverityLevel') = '20000'
THEN 'SHOW STMT  ROLLBACK E
RRORS'
WHEN SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.stream.error.logSeverityLevel') = '0' THEN
'SHOW ALL  ERRORS' ELSE 'D
ONT KNOW' END;
1

> 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