You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2019/05/30 08:16:00 UTC
[jira] [Updated] (CALCITE-3101) PushDownJoinConditions is not
always a valid transformation
[ https://issues.apache.org/jira/browse/CALCITE-3101?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Julian Hyde updated CALCITE-3101:
---------------------------------
Description:
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}CREATE TABLE "EMP" (
"empno" INTEGER PRIMARY KEY NOT NULL,
"ename" VARCHAR(100),
"deptno" INTEGER);{code}
Start with this query:
{code}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}
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}
was:
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:
{{CREATE TABLE "EMP" (}}
{{ "empno" INTEGER PRIMARY KEY NOT NULL,}}
{{ "ename" VARCHAR(100),}}
{{"deptno" INTEGER);}}
Start with this query:
{{SELECT "EMP"."empno", "t"."ename" "ename0"}}
{{FROM "EMP"}}
{{INNER JOIN "EMP" "t"}}
{{ON "EMP"."deptno" = "t"."deptno" AND "t"."ename" IS NOT NULL}}
Parse using SqlToRelConverter.convertQuery()
At his point in the stack trace:
{{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)}}
the RelNode is:
{{LogicalJoin(condition=[AND(=($3, $8), IS NOT NULL($6))], joinType=[inner])}}
{{ JdbcTableScan(table=[[XYZ, EMP]])}}
{{ JdbcTableScan(table=[[XYZ, EMP]])}}
After pushDownJoinConditions the RelNode is:
{{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]])}}
Which leads to invalid SQL (ORA-00923: FROM keyword not found where expected) :
{{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"}}
> 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}CREATE TABLE "EMP" (
> "empno" INTEGER PRIMARY KEY NOT NULL,
> "ename" VARCHAR(100),
> "deptno" INTEGER);{code}
> Start with this query:
> {code}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}
> 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)