[
https://issues.apache.org/jira/browse/CALCITE-2659?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681581#comment-16681581
]
Haisheng Yuan edited comment on CALCITE-2659 at 11/9/18 3:30 PM:
-----------------------------------------------------------------
As I mentioned, for left outer join, if the left side is NULL, the right MUST
also be NULL. So the result is correct with coalesce, no matter it is inner
join, left join, right join or full join. In fact, we only need the coalesce
function for full outer join. So there is no bug here, the plan is correct, but
just needs improvement to remove redundant COALESCE for left / right / inner
join.
try this to see what you will get:
{code:java}
create table foo(a int, b int);
create table bar(a int, c int);
insert into foo values(1,2), (NULL, 10);
insert into bar values(1,3), (NULL, 20), (2, 0);
select * from foo natural left outer join bar;
{code}
[~julianhyde] I doubt the RexSimplify can make the deduction correctly. It
doesn't know where does the COALESCE come from. It may come from left join,
right join, or even full outer join. I think we should stop generating coalesce
during parsing, specifically in
SqlValidatorImpl.Permute, only generate COALESCE for full outer join.
was (Author: hyuan):
As I mentioned, for left outer join, if the left side is NULL, the right MUST
also be NULL. So the result is correct with coalesce, no matter it is left
join, right join or full join. In fact, we only need the coalesce function for
full outer join. So there is no bug here, the plan is correct, but just needs
improvement to remove redundant COALESCE for left / right join.
try this to see what you will get:
{code:java}
create table foo(a int, b int);
create table bar(a int, c int);
insert into foo values(1,2), (NULL, 10);
insert into bar values(1,3), (NULL, 20), (2, 0);
select * from foo natural left outer join bar;
{code}
[~julianhyde] I doubt the RexSimplify can make the deduction correctly. It
doesn't know where does the COALESCE come from. It may come from left join,
right join, or even full outer join. I think we should stop generating coalesce
during parsing, specifically in
SqlValidatorImpl.Permute, only generate COALESCE for full outer join.
> 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)
