Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2659

Wrong plan In natural left/right join

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.17.0
    • Fix Version/s: None
    • Component/s: core
    • Labels:
      None

      Description

      The following is the code:

      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();
      		}
      	}
      

      We will get the plan

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

      We should not use coalesce function in natural left/right join, as in left out join, we always return the left value

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              yuqi yuqi
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated: