You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2018/11/07 17:51:00 UTC
[jira] [Commented] (CALCITE-2659) Wrong plan In natural left/right
join
[ https://issues.apache.org/jira/browse/CALCITE-2659?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16678573#comment-16678573 ]
Julian Hyde commented on CALCITE-2659:
--------------------------------------
The plan produces the correct result. However, I guess that COALESCE could be simplified. Does RexSimplify not do what you want?
Also, can you make this into a minimal test case?
> 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)