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)
