[
https://issues.apache.org/jira/browse/CALCITE-2659?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681553#comment-16681553
]
yuqi commented on CALCITE-2659:
-------------------------------
[~julianhyde], i could not understand your answer 'The plan produces the
correct result' clearly. The function COALESCE do omit the left attribute. if
left value is null and right is not null, COALESCE will return a value that is
not null. I will appreciate it if you can explain it in detail, thanks
> 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)
