[
https://issues.apache.org/jira/browse/DERBY-6185?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13633404#comment-13633404
]
Mamta A. Satoor commented on DERBY-6185:
----------------------------------------
If I recall it right, = and <> are not supported on CLOB/BLOB but LIKE is
allowed. For simplicity purposes, I removed the ESCAPE clause from the query
and the behavior is the same as seen by Rong.
-- does not work
select name from myview where name like 'Col1';
-- works
select name from mytbl1 where name like 'Col1';
Debugging the code a little bit, I found that we do the check for comparibility
in BinaryComparisonOperatorNode.bindComparisonOperator() and part of the code
is as follows
if (!cmp && !forQueryRewrite) {
throw
StandardException.newException(SQLState.LANG_NOT_COMPARABLE,
leftOperand.getTypeServices().getSQLTypeNameWithCollation() ,
rightOperand.getTypeServices().getSQLTypeNameWithCollation());
For the case with table, we come to this code in the bind phase and
forQueryRewrite has been set to true in LikeEscapeOperatorNode.bindExpression
// Set forQueryRewrite to bypass comparability checks
equals.setForQueryRewrite(true);
For the case with view, we come to the same code as above during the bind phase
and forQueryRewrite has been set to true and hence bind goes through fine. But
we come back to BinaryComparisonOperatorNode.bindComparisonOperator check in
the optimizer's pre-processing phase and at that time, forQueryRewrite is false
and that is why we throw the exception. May be we have a bug here. I want to
look at the code little more to see if I can see what the issue might be. But I
will appreciate any feedback from the list to see if I am on the right track.
Thanks
> Query against view with "where name LIKE 'Col1' ESCAPE '\' " failed
> --------------------------------------------------------------------
>
> Key: DERBY-6185
> URL: https://issues.apache.org/jira/browse/DERBY-6185
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.8.2.2, 10.8.3.0
> Environment: windows 7
> Reporter: Rong Qu
>
> The error can be reproduced,
> 1) create table mytbl1 (name clob(10K));
> 2) create table mytbl2 (name clob(10K));
> 3) create view myview (name) as select t1.name from mytbl1 t1 union all
> select t2.name from mytbl2 t2;
> 4) select name from myview where name LIKE 'Col1' ESCAPE '\';
> "4)" failed with error message "
> ERROR 42818: Comparisons between 'CLOB (UCS_BASIC)' and 'CHAR (UCS_BASIC)'
> are not supported. Types must be comparable. String types must also have
> matching collation. If collation does not match, a possible solution is to
> cast operands to force them to the default collation (e.g. SELECT tablename
> FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1')
> at org.apache.derby.iapi.error.StandardException.newException(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.BinaryComparisonOperatorNode.bindComparisonOperator(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.PredicateList.pushExpressionsIntoSelect(Unknown
> Source)
> at org.apache.derby.impl.sql.compile.UnionNode.pushExpressions(Unknown
> Source)
> at org.apache.derby.impl.sql.compile.UnionNode.pushExpressions(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.ProjectRestrictNode.pushExpressions(Unknown
> Source)
> at org.apache.derby.impl.sql.compile.FromList.pushPredicates(Unknown
> Source)
> at org.apache.derby.impl.sql.compile.SelectNode.preprocess(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.DMLStatementNode.optimizeStatement(Unknown
> Source)
> at
> org.apache.derby.impl.sql.compile.CursorNode.optimizeStatement(Unknown Source)
> at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
> at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
> at
> org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
> Source)
> ... 19 more
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
