[
https://issues.apache.org/jira/browse/DERBY-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12511215
]
Mamta A. Satoor commented on DERBY-2909:
----------------------------------------
Thinking a little more about this, I think following just satisfies Section
6.29 SR 11c) for TRIM('E' from TABLENAME)
********************
Section 4.2.2 then defines 'comparable' for character string types clearly:
"Two character strings are comparable if and only if either they have the same
character set or there exists at least one collation that is applicable to both
their respective character sets."
********************
ie although 'E' and TABLENAME have different character sets, they have
collation that apply to both those character sets and hence the two are
comparable.
Further on, there is no rule about determining collation to be used for TRIM in
SQL section 6.29 and hence what Derby is doing currently is the correct
behavior and we have no bug here.
> 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.
