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-2909) TernaryOperatorNode does not check the collation type of it's operands when implementing TRIM, LOCATE functions.
Date Mon, 09 Jul 2007 16:02:04 GMT

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

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

I did some research into the SQL spec on TRIM in SQL 2003 spec.

Section 6.29 <string value function>, Syntax Rule 11 talks about TRIM. Rule 11c) says
that "If a <trim character> is specified, then <trim character> and <trim source>
shall be comparable." I searched for comparable in the SQL spec and it gets discussed in Subclause
8.2, <comparison predicate>. General Rules 3) in Subclause 8.2 talks about rules for
character string comparison. 3a) says that Let CS be the collation as determined by Subclause
9.13 "Collation determination", for the declared types of the 2 character strings. And Section
9.13 "Collation Determination" has following rule Syntax Rules 3e) "Otherwise, every operand
whose collation derivation is implicit shall have the same declared type collation IDTC and
the collation to be used is IDTC." But this is not the case for the following TRIM example
and hence it should fail

-- we are in user schema and hence 'E' takes the territory based collation and TABLENAME has
UCS_BASIC collation
SELECT TABLENAME FROM SYS.SYSTABLES WHERE TRIM('E' from TABLENAME) = TABLENAME; 

I hope my explanation above is clear.

As for LOCATE, I couldn't find it at all in my copy of SQL 2003 spec. 


> TernaryOperatorNode does not check the collation type of it's operands when implementing
TRIM, LOCATE functions.
> ----------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-2909
>                 URL: https://issues.apache.org/jira/browse/DERBY-2909
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.3.1.1, 10.4.0.0
>            Reporter: Mamta A. Satoor
>
> Queries like following should fail in a territory based database if the current schema
is a user schema
> SELECT TABLENAME FROM SYS.SYSTABLES WHERE LOCATE('LOOKFORME', TABLENAME) != 0;
> SELECT TABLENAME FROM SYS.SYSTABLES WHERE TRIM('E' from TABLENAME) = TABLENAME;
> This is because the collation type of the first operand for both LOCATE and TRIM is territory
based but the second parameter has collation of UCS_BASIC and hence such a comparison should
not be allowed. In order to fix this, we need code like following in TernaryOperatorNode
> //Make sure that the string operands are comparable ie their collation
> //should be considered in deciding whether the string operands can be
> //compared with each other
> boolean cmp = leftOperand.getTypeServices().comparable(receiver.getTypeServices(),
> 				true,
> 				getClassFactory());
> if (!cmp) {
> 	throw StandardException.newException(SQLState.LANG_NOT_COMPARABLE, 
> 				receiverType.getSQLTypeName(),
> 				leftCTI.getSQLTypeName()
> 				);
>  }

-- 
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