[jira] Resolved: (DERBY-2909) TernaryOperatorNode does not check the collation type of it's operands when implementing TRIM, LOCATE functions.

["Mamta A. Satoor (JIRA)" <ji...@apache.org>]

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

Mamta A. Satoor resolved DERBY-2909.
------------------------------------

    Resolution: Won't Fix

Not a bug. SQL spec has no rule for collation determination for TRIM and hence what Derby is doing currently follows the SQL standard.

> 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
>            Assignee: 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.