[jira] [Commented] (CALCITE-2634) Query parses but fails to convert to rel

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-2634?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16657561#comment-16657561 ] 

Julian Hyde commented on CALCITE-2634:
--------------------------------------

FWIW, I tried to create simpler test cases but they passed:

{noformat}
diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 92f98a8f2..00c77a8d6 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1303,6 +1303,12 @@ public void testJoinUsingDynamicTable() {
     sql(sql).expand(false).ok();
   }
 
+  @Test public void testSomeWhere() {
+    final String sql = "select empno from emp where deptno > some (\n"
+        + "  select deptno from dept where name like 'S%')";
+    sql(sql).expand(false).ok();
+  }
+
   @Test public void testNotInUncorrelatedSubQueryRex() {
     final String sql = "select empno from emp where deptno not in"
         + " (select deptno from dept)";
diff --git a/core/src/test/resources/sql/some.iq b/core/src/test/resources/sql/some.iq
index 065476cc7..4ce131488 100644
--- a/core/src/test/resources/sql/some.iq
+++ b/core/src/test/resources/sql/some.iq
@@ -573,5 +573,17 @@ where sal > all (select comm from "scott".emp where comm is not null);
 
 !ok
 
+# SOME plus WHERE
+select * from "scott".emp
+where sal > some (select comm from "scott".emp where deptno = 20);
+!ok
+
+# SOME plus WHERE and WITH
+# [CALCITE-2634] Query parses but fails to convert to rel
+with e as (select deptno, comm, sal from "scott".emp)
+select * from e
+where sal > some (select comm from e where deptno = 20);
+!ok
+
 # End some.iq
{noformat}

> Query parses but fails to convert to rel
> ----------------------------------------
>
>                 Key: CALCITE-2634
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2634
>             Project: Calcite
>          Issue Type: Improvement
>            Reporter: Kenneth Knowles
>            Assignee: Julian Hyde
>            Priority: Major
>
> The following query parses but fails to convert to logical rels:
> {code:sql}
> 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)
> {code}
> The validated SQL node pretty prints like so:
> {code:sql}
> 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`))
> {code}
> The stack trace is here:
> {code:java}
> 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)
> {code}
> 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]
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)