[WIP][CALCITE-3385] fields in diffrent tables has the same order will occur a bug.

[flaming-archer]

origin is use table'field no. But if fields in diffrent tables has the same order.That say leftFactorColMapping.put( colOrigin.getOriginColumnOrdinal(), i); leftFactorColMapping the after will override the before.So i want to add table's qualifiedNames to differentiate.

[yanlin-Lynn]

You seem to use a wrong title for this PR.

[flaming-archer]

You seem to use a wrong title for this PR.

just copy titile from u ...

[yanlin-Lynn]

I think you may need this https://github.com/apache/calcite/pull/1439 to update your commit log.

[flaming-archer]

I think you may need this #1439 to update your commit log.

done

[julianhyde]

I don't understand the commit message, it doesn't seem to relate to CALCITE-3379, and there's no test case.

[flaming-archer]

I don't understand the commit message, it doesn't seem to relate to CALCITE-3379, and there's no test case.

test case just complex， I have write 3 hours already CALCITE-3379 is flowed by github,not apache issue. I also known there is an issue just now.

my test code like that ` /*

• Licensed to the Apache Software Foundation (ASF) under one or more
• contributor license agreements. See the NOTICE file distributed with
• this work for additional information regarding copyright ownership.
• The ASF licenses this file to you under the Apache License, Version 2.0
• (the "License"); you may not use this file except in compliance with
• the License. You may obtain a copy of the License at
• http://www.apache.org/licenses/LICENSE-2.0
• Unless required by applicable law or agreed to in writing, software
• distributed under the License is distributed on an "AS IS" BASIS,
• WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
• See the License for the specific language governing permissions and
• limitations under the License. */ package org.apache.calcite.rel.rules;

import static org.hamcrest.CoreMatchers.allOf; import static org.hamcrest.CoreMatchers.containsString; import static org.hamcrest.CoreMatchers.not; import static org.junit.Assert.assertThat;

import org.apache.calcite.adapter.enumerable.EnumerableConvention; import org.apache.calcite.plan.ConventionTraitDef; import org.apache.calcite.plan.RelOptUtil; import org.apache.calcite.plan.RelTraitSet; import org.apache.calcite.plan.hep.HepPlanner; import org.apache.calcite.rel.RelCollationTraitDef; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.RelRoot; import org.apache.calcite.schema.SchemaPlus; import org.apache.calcite.schemas.HrClusteredSchema; import org.apache.calcite.sql.SqlExplainFormat; import org.apache.calcite.sql.SqlExplainLevel; import org.apache.calcite.sql.SqlNode; import org.apache.calcite.sql.parser.SqlParseException; import org.apache.calcite.sql.parser.SqlParser; import org.apache.calcite.tools.FrameworkConfig; import org.apache.calcite.tools.Frameworks; import org.apache.calcite.tools.Planner; import org.apache.calcite.tools.Programs; import org.apache.calcite.tools.RelConversionException; import org.apache.calcite.tools.RuleSet; import org.apache.calcite.tools.RuleSets; import org.apache.calcite.tools.ValidationException; import org.apache.calcite.util.Util; import org.junit.Test;

/**

• Tests the application of the {@link LoptMultiJoin}. */ public class LoptMultiJoinTest {

@Test public void addRemovableSelfJoinPair() throws SqlParseException, ValidationException, RelConversionException { RuleSet prepareRules = RuleSets.ofList( LoptOptimizeJoinRule.INSTANCE); SchemaPlus rootSchema = Frameworks.createRootSchema(true); SchemaPlus defSchema = rootSchema.add("hr", new HrClusteredSchema()); FrameworkConfig config = Frameworks.newConfigBuilder() .parserConfig(SqlParser.Config.DEFAULT) .defaultSchema(defSchema) .traitDefs(ConventionTraitDef.INSTANCE, RelCollationTraitDef.INSTANCE) .programs(Programs.of(prepareRules)) .build();

String sql
    = "SELECT a.ename,a.dname,a.count_ename ,b.count_dname from "
    + " ( SELECT EMPS.\"name\" ename,DEPTS.\"name\" dname,"
    + "COUNT(DISTINCT EMPS.\"name\") count_ename "
    + "FROM \"hr\".\"emps\" EMPS,\"hr\".\"depts\" DEPTS "
    + "WHERE EMPS.\"deptno\" = DEPTS.\"deptno\" "
    + "GROUP  BY EMPS.\"name\",DEPTS.\"name\" ) a "
    + " inner join "
    + " ( SELECT EMPS1.\"name\" ename ,DEPTS1.\"name\" dname ,"
    + " COUNT(DISTINCT DEPTS1.\"name\") count_dname "
    + "FROM \"hr\".\"emps\" EMPS1,\"hr\".\"depts\" DEPTS1 "
    + "WHERE EMPS1.\"deptno\" = DEPTS1.\"deptno\" "
    + "GROUP  BY EMPS1.\"name\",DEPTS1.\"name\" ) b"
    + " on  a.ename = b.ename and a.dname=b.dname ";

// System.out.print(sql);

Planner planner = Frameworks.getPlanner(config);
SqlNode parse = planner.parse(sql);
SqlNode validate = planner.validate(parse);
RelRoot planRoot = planner.rel(validate);
RelNode planBefore = planRoot.rel;

// String beforeStr = toString(planBefore);

RelTraitSet desiredTraits = planBefore.getTraitSet()
    .replace(EnumerableConvention.INSTANCE);
RelNode planAfter = LoptOptimizeJoinRule.INSTANCE.onMatch(planAfter);
System.out.print(toString(planAfter));

assertThat(
    toString(planAfter),
    allOf(
        containsString("ENAME=[$0]"),
        not(containsString("ENAME=[$1]"))));

} private String toString(RelNode rel) { return Util.toLinux( RelOptUtil.dumpPlan("", rel, SqlExplainFormat.TEXT, SqlExplainLevel.DIGEST_ATTRIBUTES)); } }

// End LoptMultiJoinTest.java

bug is like that LogicalProject(subset=[rel#59:Subset#10.ENUMERABLE.[]], _ENAME=[$1],_ DNAME=[$1], COUNT_ENAME=[$2], COUNT_DNAME=[$7]) But it should be this LogicalProject(subset=[rel#59:Subset#10.ENUMERABLE.[]], _ENAME=[$0]_, DNAME=[$1], COUNT_ENAME=[$2], COUNT_DNAME=[$7])

Notice ENAME=[$1] and ENAME=[$0]

The reason is Both emps and depts have same field name ,but the ordinal is diffrent.

leftFactorColMapping.put( colOrigin.getOriginColumnOrdinal(), i); The later will replace before.

I found this bug in my product code with a big sql. But have some diffcult to make a test case to reappear.

[hsyuan]

@flaming-archer Can you log a JIRA issue for the bug you described?

[flaming-archer]

@flaming-archer Can you log a JIRA issue for the bug you described?

Done,https://issues.apache.org/jira/plugins/servlet/mobile#issue/CALCITE-3385 Report this issue on my mobile.And i have a trip now. git commit message also changed

[yanlin-Lynn]

RelNode planAfter = LoptOptimizeJoinRule.INSTANCE.onMatch(planAfter); This line of code in test doesn't work, no definition of planAfter

[flaming-archer]

RelNode planAfter = LoptOptimizeJoinRule.INSTANCE.onMatch(planAfter); This line of code in test doesn't work, no definition of planAfter

Not complete yet. write again and again（3 hours） ,the bug place is so deeper,I cannot directtly invoke the bug function （I am not too familiar with calcite）· Maybe u can complete.

[jinxing64]

hi @flaming-archer Could you please put a [WIP] in your PR title if this is still under working and not ready for review?

[flaming-archer]

hi @flaming-archer Could you please put a [WIP] in your PR title if this is still under working and not ready for review?

done