db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel John Debrunner (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:48:04 GMT

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

Daniel John Debrunner commented on DERBY-2909:
----------------------------------------------

Thanks, I now understand why you think the statement would fail, I thought you were saying
the collation type/derivation of TRIM would be derived from its first operand.

Looking at the comparable issue though I think the being comparable and collation derivation
are different items.

Section 3.1.6.5 defines comparable and says 'For the specification of comparability of individual
data types, see Subclause 4.2, "Character strings"'

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

Thus collation determination does not factor into the types being comparable. I think this
is correct because 8.2 SR 3) requires the types be comparable separate to any collation determination.

In this case the two values 'E' and TABLENAME have different character sets (USER & SYSTEM
IDENTIFIER) but I think that both the collations supported by Derby are applicable to both
character sets. Currently there is no way to have the territory based collation be used with
the system identifier character set, but once the COLLATION key word is allowed there will
be. Thus the territory collation is applicable to the SYSTEM IDENTIFIER character set, it
just can't be specified yet.




> 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