[
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)
