git.net

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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


yuqi created CALCITE-2659:
-----------------------------

             Summary: 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
         Environment: 
{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 id, if(id > 0, time_test, date_test) from table_result";
			String sql1 = "select id, case when id > 0 then time_test else date_test end from table_result";
			String sql2 = "select * from table_result a natural left join table_result_copy b";



			SqlNode parse = planner.parse(sql2);
			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 funtion in natural left/rigiht join, as in left out join, we always return the left value


            Reporter: yuqi
            Assignee: Julian Hyde






--
This message was sent by Atlassian JIRA
(v7.6.3#76005)