[jira] [Updated] (CALCITE-2659) Wrong plan In natural left/right join

Wed, 07 Nov 2018 02:14:10 -0800 


     [ 
https://issues.apache.org/jira/browse/CALCITE-2659?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

yuqi updated CALCITE-2659:
--------------------------
    Description: 
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



  was:
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




> 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)

Reply via email to