Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <10204811.1189015534032.JavaMail.jira@brutus>
Date: Wed, 5 Sep 2007 11:05:34 -0700 (PDT)
From: "Kathey Marsden (JIRA)" <jira@apache.org>
To: derby-dev@db.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.
In-Reply-To: <857349.1185293311103.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-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.