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 <mailb...@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
