[
https://issues.apache.org/jira/browse/CALCITE-3101?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16853295#comment-16853295
]
Julian Hyde commented on CALCITE-3101:
--------------------------------------
You're kind of asking to have it both ways. Do the transformations in the
algebra (RelNode), but don't do the transformations if some dialect might not
be able to implement the algebra directly.
It is possible to give you what you want, but the cost is that we make the
algebra more aware of dialect than I think it should be.
Boolean expressions and Case expressions in the SELECT clause are perfectly
reasonable, and I don't think we should compromise our algebra because lame
implementations exist. (There are historical reasons why Oracle doesn't support
boolean expressions and Elasticsearch doesn't support CASE, but that's their
problem, not ours.)
> PushDownJoinConditions is not always a valid transformation
> -----------------------------------------------------------
>
> Key: CALCITE-3101
> URL: https://issues.apache.org/jira/browse/CALCITE-3101
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.16.0
> Environment: Java app pointing to Oracle database.
> Reporter: Paul Jackson
> Priority: Major
>
> SqlToRelConverter can create a plan that is invalid when converted back to
> SQL in cases where the expression that is pushed to the projection returns a
> Boolean. The following example pushes IS NOT NULL to a select. Several SQL
> dialects do not support this. Oracle, for example, sees IS NOT NULL as a
> condition rather than an expression. It returns a Boolean data type, which is
> not supported. Likewise, Microsoft SQL Server does not support IS NOT NULL in
> a projection expression.
> Steps to reproduce (Oracle):
> DDL:
> {code:java}
> CREATE TABLE "EMP" (
> "empno" INTEGER PRIMARY KEY NOT NULL,
> "ename" VARCHAR(100),
> "deptno" INTEGER);{code}
> Start with this query:
> {code:java}
> SELECT "EMP"."empno", "t"."ename" "ename0"
> FROM "EMP"
> INNER JOIN "EMP" "t"
> ON "EMP"."deptno" = "t"."deptno" AND "t"."ename" IS NOT NULL{code}
> Parse using {{SqlToRelConverter.convertQuery()}}. At this point in the stack
> trace:
> {noformat}
> org.apache.calcite.plan.RelOptUtil.pushDownJoinConditions(RelOptUtil.java:3222)
> org.apache.calcite.sql2rel.SqlToRelConverter.createJoin(SqlToRelConverter.java:2414)
> org.apache.calcite.sql2rel.SqlToRelConverter.convertFrom(SqlToRelConverter.java:2056)
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:641)
> org.apache.calcite.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:622)
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQueryRecursive(SqlToRelConverter.java:3057)
> org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:558)
> {noformat}
> the {{RelNode}} is:
> {noformat}
> LogicalJoin(condition=[AND(=($3, $8), IS NOT NULL($6))], joinType=[inner])
> JdbcTableScan(table=[[XYZ, EMP]])
> JdbcTableScan(table=[[XYZ, EMP]])
> {noformat}
> After {{pushDownJoinConditions}} the {{RelNode}} is:
> {noformat}
> LogicalJoin(condition=[AND(=($3, $8), $10)], joinType=[inner])
> JdbcTableScan(table=[[XYZ, EMP]])
> LogicalProject(empno=[$0], ename=[$1], job=[$2], deptno=[$3], etype=[$4],
> $f5=[IS NOT NULL($1)])
> JdbcTableScan(table=[[XYZ, EMP]])
> {noformat}
> Which leads to invalid SQL ("ORA-00923: FROM keyword not found where
> expected"):
> {code:java}
> SELECT "EMP"."empno", "t"."ename" "ename0"
> FROM "XYZ"."EMP"
> INNER JOIN (
> SELECT "empno", "ename", "job", "deptno", "etype", "ename" IS NOT NULL
> "$f5"
> FROM "XYZ"."EMP") "t"
> ON "EMP"."deptno" = "t"."deptno" AND "t"."$f5"
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
