[
https://issues.apache.org/jira/browse/CALCITE-3101?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16852313#comment-16852313
]
Paul Jackson commented on CALCITE-3101:
---------------------------------------
FWIW, I like putting whatever expression that is to be used in the RelNode
rather than leaving until conversion to find an implementation that works for
the dialect. Without changing the RelNode, the expression will be wrong in that
its type will be Boolean but (if we use 1 and 0 to represent true and false)
the actual type will be int. That means the Rel2SQL conversion code will have
to fix both the expression and the {{"$f5"}} in the join condition. Such code
would either have to make assumptions about where to find the boolean variable
or would have to be smart enough to deal with it used in any arbitrary place
(other selects, where clause, join condition, etc.).
Is it possible there are dialects that for which the null check in the select
is not possible? I know Cassandra does not support case statements in the
Select clause.
It might be nice to give the dialect or the RelBuilder the option to disable
this pushdown.
> 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)
