Incorrect SQL generation from RelToSqlConverter

[Bali <ma...@gmail.com>]

Hi,

Thanks in advance for looking into this. I have been experimenting some
proof of concept for my firm with Apache Calcite. I got into this issue
while I was creating a simple correlated query. My understanding may be
wrong here. Following code constructs correlated query:

final FrameworkConfig config = RelBuilderTest.config().build();
final RelBuilder builder = RelBuilder.create(config);
final Holder<RexCorrelVariable> v = Holder.of(null);

 builder.scan("EMP")
    .variable(v)
    .scan("DEPT")
    .filter(
        builder.equals(builder.field("DEPTNO"),
            builder.field(v.get(), "DEPTNO")))
    .project(builder.field("DEPT", "DNAME"), builder.field("DEPT", "LOC"))
    .correlate(
        JoinRelType.LEFT, v.get().id,
        builder.field(2, 0, "DEPTNO")
    );
RelDataType dataType = builder.peek().getRowType();
RelNode relNode = builder.project(builder.field("ENAME"),
builder.field("DNAME"))
    .build();
System.out.println(RelOptUtil.toString(relNode));
final RelToSqlConverter converter = new
RelToSqlConverter(AnsiSqlDialect.DEFAULT);
final SqlNode sqlNode = converter.visitRoot(relNode).asStatement();
final String sql = sqlNode.toSqlString(AnsiSqlDialect.DEFAULT).getSql();
System.out.println(sql);

RowType after correlation step is:
RecordType(SMALLINT EMPNO, VARCHAR(10) ENAME, VARCHAR(9) JOB, SMALLINT MGR,
DATE HIREDATE, DECIMAL(7, 2) SAL, DECIMAL(7, 2) COMM, TINYINT DEPTNO,
VARCHAR(14) DNAME, VARCHAR(13) LOC)

Projecting "ENAME" and "DNAME" should work fine. Relational Tree for the
query looks like this:
LogicalProject(EMPNO=[$0], DNAME=[$8])
  LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{7}])
    LogicalTableScan(table=[[scott, EMP]])
    LogicalProject(DNAME=[$1], LOC=[$2])
      LogicalFilter(condition=[=($0, $cor0.DEPTNO)])
        LogicalTableScan(table=[[scott, DEPT]])

while generated SQL from the query looks like this:
SELECT `$cor0`.`EMPNO`, `$cor0`.`DNAME`
FROM `scott`.`EMP` AS `$cor0`,
LATERAL (SELECT `DNAME`, `LOC`
FROM `scott`.`DEPT`
WHERE `DEPTNO` = `$cor0`.`DEPTNO`) AS `t0`

If I try to run this SQL directly through JDBC, then it fails with the
error "Column 'DNAME' not found in table '$cor0'". Wondering if there is an
error in RelToSqlConverter or am I missing anything here?

I will highly appreciate any help here.

Thanks,
~Bali

Re: Incorrect SQL generation from RelToSqlConverter

[Julian Hyde <jh...@apache.org>]

It looks like a bug. Please log a JIRA case.

I don't think that Correlate has been extensively tested in RelToSql.
However, there is one test that is similar to yours (not identical)
[1] and it passes.

Julian

[1] https://github.com/apache/calcite/blob/174a707e1c199c97d7cc3531f0cd2e94745f4366/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java#L4842

On Mon, Jan 11, 2021 at 12:49 PM Bali <ma...@gmail.com> wrote:
>
> Hi,
>
> Thanks in advance for looking into this. I have been experimenting some
> proof of concept for my firm with Apache Calcite. I got into this issue
> while I was creating a simple correlated query. My understanding may be
> wrong here. Following code constructs correlated query:
>
> final FrameworkConfig config = RelBuilderTest.config().build();
> final RelBuilder builder = RelBuilder.create(config);
> final Holder<RexCorrelVariable> v = Holder.of(null);
>
>  builder.scan("EMP")
>     .variable(v)
>     .scan("DEPT")
>     .filter(
>         builder.equals(builder.field("DEPTNO"),
>             builder.field(v.get(), "DEPTNO")))
>     .project(builder.field("DEPT", "DNAME"), builder.field("DEPT", "LOC"))
>     .correlate(
>         JoinRelType.LEFT, v.get().id,
>         builder.field(2, 0, "DEPTNO")
>     );
> RelDataType dataType = builder.peek().getRowType();
> RelNode relNode = builder.project(builder.field("ENAME"),
> builder.field("DNAME"))
>     .build();
> System.out.println(RelOptUtil.toString(relNode));
> final RelToSqlConverter converter = new
> RelToSqlConverter(AnsiSqlDialect.DEFAULT);
> final SqlNode sqlNode = converter.visitRoot(relNode).asStatement();
> final String sql = sqlNode.toSqlString(AnsiSqlDialect.DEFAULT).getSql();
> System.out.println(sql);
>
> RowType after correlation step is:
> RecordType(SMALLINT EMPNO, VARCHAR(10) ENAME, VARCHAR(9) JOB, SMALLINT MGR,
> DATE HIREDATE, DECIMAL(7, 2) SAL, DECIMAL(7, 2) COMM, TINYINT DEPTNO,
> VARCHAR(14) DNAME, VARCHAR(13) LOC)
>
> Projecting "ENAME" and "DNAME" should work fine. Relational Tree for the
> query looks like this:
> LogicalProject(EMPNO=[$0], DNAME=[$8])
>   LogicalCorrelate(correlation=[$cor0], joinType=[left],
> requiredColumns=[{7}])
>     LogicalTableScan(table=[[scott, EMP]])
>     LogicalProject(DNAME=[$1], LOC=[$2])
>       LogicalFilter(condition=[=($0, $cor0.DEPTNO)])
>         LogicalTableScan(table=[[scott, DEPT]])
>
> while generated SQL from the query looks like this:
> SELECT `$cor0`.`EMPNO`, `$cor0`.`DNAME`
> FROM `scott`.`EMP` AS `$cor0`,
> LATERAL (SELECT `DNAME`, `LOC`
> FROM `scott`.`DEPT`
> WHERE `DEPTNO` = `$cor0`.`DEPTNO`) AS `t0`
>
> If I try to run this SQL directly through JDBC, then it fails with the
> error "Column 'DNAME' not found in table '$cor0'". Wondering if there is an
> error in RelToSqlConverter or am I missing anything here?
>
> I will highly appreciate any help here.
>
> Thanks,
> ~Bali