[
https://issues.apache.org/jira/browse/CALCITE-2659?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16678837#comment-16678837
]
Haisheng Yuan commented on CALCITE-2659:
----------------------------------------
Here is a simpler test case with existing table schema in calcite mock catalog:
```
@Test public void testNatrualLeftJoin() {
final HepProgram hepProgram = new HepProgramBuilder()
.build();
HepProgramBuilder builder = new HepProgramBuilder();
HepPlanner hepPlanner = new HepPlanner(builder.build());
final String sql = "select * from EMPDEFAULTS natural left outer join DEPT";
checkPlanning(tester, hepProgram, hepPlanner, sql);
}
```
And we get plan:
```
LogicalProject(DEPTNO=[COALESCE($7, $9)], EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8], NAME=[$10])
LogicalJoin(condition=[=($7, $9)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMPDEFAULTS]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
```
> Wrong plan In natural left/right join
> --------------------------------------
>
> Key: CALCITE-2659
> URL: https://issues.apache.org/jira/browse/CALCITE-2659
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.17.0
> Reporter: yuqi
> Assignee: Julian Hyde
> Priority: Major
>
> The following is the code:
> {code:java}
> public static void main(String[] args) {
> try {
> SchemaPlus rootSchema =
> Frameworks.createRootSchema(true);
> rootSchema.add("TABLE_RESULT", new AbstractTable() {
> public RelDataType getRowType(final
> RelDataTypeFactory typeFactory) {
> RelDataTypeFactory.FieldInfoBuilder
> builder = typeFactory.builder();
> RelDataType t0 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.BIGINT),
> true);
> RelDataType t1 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.TINYINT),
> true);
> RelDataType t2 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.SMALLINT),
> true);
> RelDataType t3 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.INTEGER),
> true);
> RelDataType t4 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.FLOAT),
> true);
> RelDataType t5 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.DOUBLE),
> true);
> RelDataType t6 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.BIGINT),
> true);
> RelDataType t7 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.BOOLEAN),
> true);
> RelDataType t8 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.DATE),
> true);
> RelDataType t9 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.TIME),
> true);
> RelDataType t10 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.TIMESTAMP),
> true);
> RelDataType t11 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.VARCHAR),
> true);
> builder.add("ID", t0);
> builder.add("byte_test".toUpperCase(),
> t1);
> builder.add("short_test".toUpperCase(),
> t2);
> builder.add("int_test".toUpperCase(),
> t3);
> builder.add("float_test".toUpperCase(),
> t4);
>
> builder.add("double_test".toUpperCase(), t5);
> builder.add("long_test".toUpperCase(),
> t6);
>
> builder.add("boolean_test".toUpperCase(), t7);
> builder.add("date_test".toUpperCase(),
> t8);
> builder.add("time_test".toUpperCase(),
> t9);
>
> builder.add("timestamp_test".toUpperCase(), t10);
>
> builder.add("string_test".toUpperCase(), t11);
> return builder.build();
> }
> });
> rootSchema.add("TABLE_RESULT_COPY", new AbstractTable()
> {
> public RelDataType getRowType(final
> RelDataTypeFactory typeFactory) {
> RelDataTypeFactory.FieldInfoBuilder
> builder = typeFactory.builder();
> RelDataType t0 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.BIGINT),
> true);
> RelDataType t1 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.TINYINT),
> true);
> RelDataType t2 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.SMALLINT),
> true);
> RelDataType t3 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.INTEGER),
> true);
> RelDataType t4 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.FLOAT),
> true);
> RelDataType t5 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.DOUBLE),
> true);
> RelDataType t6 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.BIGINT),
> true);
> RelDataType t7 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.BOOLEAN),
> true);
> RelDataType t8 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.DATE),
> true);
> RelDataType t9 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.TIME),
> true);
> RelDataType t10 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.TIMESTAMP),
> true);
> RelDataType t11 =
> typeFactory.createTypeWithNullability(typeFactory.createSqlType(SqlTypeName.VARCHAR),
> true);
> builder.add("ID", t0);
> builder.add("byte_test1".toUpperCase(),
> t1);
>
> builder.add("short_test1".toUpperCase(), t2);
> builder.add("int_test1".toUpperCase(),
> t3);
>
> builder.add("float_test1".toUpperCase(), t4);
>
> builder.add("double_test1".toUpperCase(), t5);
> builder.add("long_test1".toUpperCase(),
> t6);
>
> builder.add("boolean_test1".toUpperCase(), t7);
> builder.add("date_test1".toUpperCase(),
> t8);
> builder.add("time_test1".toUpperCase(),
> t9);
>
> builder.add("timestamp_test1".toUpperCase(), t10);
>
> builder.add("string_test1".toUpperCase(), t11);
> return builder.build();
> }
> });
> final FrameworkConfig config =
> Frameworks.newConfigBuilder()
> .parserConfig(SqlParser.Config.DEFAULT)
> .defaultSchema(rootSchema)
> .build();
> Planner planner = Frameworks.getPlanner(config);
> String sql = "select * from table_result a natural left
> join table_result_copy b";
> SqlNode parse = planner.parse(sql);
> SqlNode validate = planner.validate(parse);
> RelRoot root = planner.rel(validate);
> System.out.println(RelOptUtil.toString(root.rel));
> } catch (Exception e) {
> e.printStackTrace();
> }
> }
> {code}
> We will get the plan
> {code:java}
> LogicalProject(ID=[COALESCE($0, $12)], BYTE_TEST=[$1], SHORT_TEST=[$2],
> INT_TEST=[$3], FLOAT_TEST=[$4], DOUBLE_TEST=[$5], LONG_TEST=[$6],
> BOOLEAN_TEST=[$7], DATE_TEST=[$8], TIME_TEST=[$9], TIMESTAMP_TEST=[$10],
> STRING_TEST=[$11], BYTE_TEST1=[$13], SHORT_TEST1=[$14], INT_TEST1=[$15],
> FLOAT_TEST1=[$16], DOUBLE_TEST1=[$17], LONG_TEST1=[$18], BOOLEAN_TEST1=[$19],
> DATE_TEST1=[$20], TIME_TEST1=[$21], TIMESTAMP_TEST1=[$22], STRING_TEST1=[$23])
> LogicalJoin(condition=[=($0, $12)], joinType=[left])
> EnumerableTableScan(table=[[TABLE_RESULT]])
> EnumerableTableScan(table=[[TABLE_RESULT_COPY]])
> {code}
> We should not use *coalesce* function in natural left/right join, as in left
> out join, we always return the left value
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
