You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Haisheng Yuan (JIRA)" <ji...@apache.org> on 2018/11/07 21:59:05 UTC
[jira] [Comment Edited] (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=16678837#comment-16678837 ]
Haisheng Yuan edited comment on CALCITE-2659 at 11/7/18 9:58 PM:
-----------------------------------------------------------------
Here is a simpler test case with existing table schema in calcite mock catalog:
{code:java}
@Test public void testNatrualLeftJoin() {
final HepProgram hepProgram = new HepProgramBuilder()
.build();
HepProgramBuilder builder = new HepProgramBuilder();
HepPlanner hepPlanner = new HepPlanner(builder.build());
final String sql = "select * from EMPDEFAULTS natural left outer join DEPT";
checkPlanning(tester, hepProgram, hepPlanner, sql);
}
{code}
And we get plan:
{code:java}
LogicalProject(DEPTNO=[COALESCE($7, $9)], EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8], NAME=[$10])
LogicalJoin(condition=[=($7, $9)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMPDEFAULTS]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
{code}
was (Author: hyuan):
Here is a simpler test case with existing table schema in calcite mock catalog:
```
@Test public void testNatrualLeftJoin() {
final HepProgram hepProgram = new HepProgramBuilder()
.build();
HepProgramBuilder builder = new HepProgramBuilder();
HepPlanner hepPlanner = new HepPlanner(builder.build());
final String sql = "select * from EMPDEFAULTS natural left outer join DEPT";
checkPlanning(tester, hepProgram, hepPlanner, sql);
}
```
And we get plan:
```
LogicalProject(DEPTNO=[COALESCE($7, $9)], EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8], NAME=[$10])
LogicalJoin(condition=[=($7, $9)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMPDEFAULTS]])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
```
> 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)