Return-Path: X-Original-To: apmail-db-derby-dev-archive@www.apache.org Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id DECD1F1BD for ; Wed, 17 Apr 2013 13:23:21 +0000 (UTC) Received: (qmail 33765 invoked by uid 500); 17 Apr 2013 13:23:21 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 33477 invoked by uid 500); 17 Apr 2013 13:23:21 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 32346 invoked by uid 99); 17 Apr 2013 13:23:16 -0000 Received: from arcas.apache.org (HELO arcas.apache.org) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 17 Apr 2013 13:23:16 +0000 Date: Wed, 17 Apr 2013 13:23:16 +0000 (UTC) From: "Knut Anders Hatlen (JIRA)" To: derby-dev@db.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Updated] (DERBY-6185) Query against view with "where name LIKE 'Col1' ESCAPE '\' " failed MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/DERBY-6185?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Knut Anders Hatlen updated DERBY-6185: -------------------------------------- Attachment: set-for-rewrite.diff When LikeEscapeOperatorNode optimizes name LIKE 'Col1' to name = 'Col1', it sets a flag on the generated equals operator to bypass the comparability checks: // Set forQueryRewrite to bypass comparability checks equals.setForQueryRewrite(true); This makes the equals operator work even if the column is a CLOB. However, when pushing predicates into a sub-query (in PredicateList.pushExpressionsIntoSelect()) the generated operator is cloned, and the clone does not have the for query rewrite flag set. The attached patch makes PredicateList copy the flag when cloning the operator. This made the query succeed. I'll add a test case too. > 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.1.3.3, 10.5.3.2, 10.8.2.2, 10.8.3.0 > Environment: windows 7 > Reporter: Rong Qu > Attachments: set-for-rewrite.diff > > > 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