Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6185

Query against view with "where name LIKE 'Col1' ESCAPE '\' " failed

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.1.3.3, 10.5.3.2, 10.8.2.2, 10.8.3.0
    • 10.8.3.3, 10.9.2.2, 10.10.2.0, 10.11.1.1
    • SQL
    • None
    • windows 7
    • Normal

    Description

      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

      Attachments

        1. ViewsTest.java
          34 kB
          Mamta A. Satoor
        2. set-for-rewrite.diff
          1.0 kB
          Knut Anders Hatlen
        3. DERBY_6185_diff_patch4.txt
          19 kB
          Mamta A. Satoor
        4. DERBY_6185_diff_patch3.txt
          18 kB
          Mamta A. Satoor
        5. DERBY_6185_diff_patch2.txt
          14 kB
          Mamta A. Satoor

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            mamtas Mamta A. Satoor
            rqu Rong Qu
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment