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

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Won't Fix
• Affects Version/s: 10.3.1.4, 10.4.1.3
• Component/s: SQL
• Assignee: Mamta A. Satoor
• Reporter: Mamta A. Satoor
• Created: 06/Jul/07 08:59
• Updated: 09/Jul/07 23:42
• Resolved: 09/Jul/07 23:42

Description

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() ); }

Issue Links

• relates to: DERBY-1478 Add built in language based ordering and like processing to Derby