Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2634

Query with WITH and SOME parses but fails to convert to rel

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      The following query parses but fails to convert to logical rels:

      WITH BidCount (auction, num, hopstart) AS (
        SELECT
          Bid.auction,
          count(*),
          HOP_START(Bid.dateTime, INTERVAL '1' SECOND, INTERVAL '60' SECOND)
        FROM Bid
        GROUP BY
          Bid.auction,
          HOP(Bid.dateTime, INTERVAL '1' SECOND, INTERVAL '60' SECOND)
        )
      SELECT HotAuction.auction, HotAuction.num, HotAuction.hopstart
      FROM BidCount as HotAuction
      WHERE HotAuction.num >= ALL (
        SELECT num FROM BidCount
        WHERE HotAuction.hopstart = BidCount.hopstart)
      

      The validated SQL node pretty prints like so:

      WITH `BidCount` (`auction`, `num`, `hopstart`) AS (SELECT `Bid`.`auction`, COUNT(*), HOP_START(`Bid`.`dateTime`, INTERVAL '5' SECOND, INTERVAL '10' SECOND)
      FROM `beam`.`Bid` AS `Bid`
      GROUP BY `Bid`.`auction`, HOP(`Bid`.`dateTime`, INTERVAL '5' SECOND, INTERVAL '10' SECOND)) (SELECT `HotAuction`.`auction`, `HotAuction`.`num`, `HotAuction`.`hopstart`
      FROM `BidCount` AS `HotAuction`
      WHERE `HotAuction`.`num` >= ALL (SELECT `BidCount`.`num`
      FROM `BidCount` AS `BidCount`
      WHERE `HotAuction`.`hopstart` = `BidCount`.`hopstart`))
      

      The stack trace is here:

      java.lang.ClassCastException: org.apache.calcite.rel.logical.LogicalFilter cannot be cast to org.apache.calcite.rel.core.Join
      	at org.apache.calcite.sql2rel.SqlToRelConverter.translateIn(SqlToRelConverter.java:1242)
      	at org.apache.calcite.sql2rel.SqlToRelConverter.substituteSubQuery(SqlToRelConverter.java:1129)
      	at org.apache.calcite.sql2rel.SqlToRelConverter.replaceSubQueries(SqlToRelConverter.java:997)
      	at org.apache.calcite.sql2rel.SqlToRelConverter.convertWhere(SqlToRelConverter.java:964)
      	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:643)
      	at org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:621)
      	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3051)
      	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:557)
      	at org.apache.calcite.sql2rel.SqlToRelConverter.convertWith(SqlToRelConverter.java:3891)
      	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3065)
      	at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:557)
      	at oorg.apache.calcite.prepare.PlannerImpl.rel(PlannerImpl.java:235)
      

      For context, this is working with Beam SQL but the failure occurs before any Beam-related code is reached: https://github.com/apache/beam/pull/6757/files

       

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                kenn Kenneth Knowles
                Reporter:
                kenn Kenneth Knowles
              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: