[jira] [Commented] (CALCITE-3935) Materialization-Failed, when querying with LeftJoinWithFilter
[
https://issues.apache.org/jira/browse/CALCITE-3935?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17407258#comment-17407258
]
Xurenhe commented on CALCITE-3935:
--
Please review this pr: https://github.com/apache/calcite/pull/2013
Thanks a lot.
> Materialization-Failed, when querying with LeftJoinWithFilter
> -
>
> Key: CALCITE-3935
> URL: https://issues.apache.org/jira/browse/CALCITE-3935
> Project: Calcite
> Issue Type: Bug
>Reporter: Xurenhe
>Priority: Major
> Attachments: Jietu20200417-200532.png
>
> Time Spent: 50m
> Remaining Estimate: 0h
>
> Hi all.
> `SubstitutionVisitor` support more join_type in some rules of `Join`.
> Now `SubstitutionVisitor` only support join type of `INNER`, when pulling up
> bottom filters of join.
> It doesn't work as follows.
> {code:java}
> @Test public void testJoinOnLeftProjectWithFilterToJoin() {
> String mv = ""
> + "select \"emps\".\"empid\", \"depts\".\"name\",
> \"emps\".\"salary\"\n" +
> "from \"emps\"\n" +
> "left join \"depts\"\n" +
> "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
> + "where \"emps\".\"empid\" > 10";
> String query = ""
> + "select \"emps\".\"empid\", \"depts\".\"name\",
> \"emps\".\"salary\"\n" +
> "from \"emps\"\n" +
> "left join \"depts\"\n" +
> "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
> + "where \"emps\".\"empid\" > 40";
> sql(mv, query).withOnlyBySubstitution(true).ok();
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3935) Materialization-Failed, when querying with LeftJoinWithFilter
[
https://issues.apache.org/jira/browse/CALCITE-3935?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17134120#comment-17134120
]
Jin Xing commented on CALCITE-3935:
---
Refine the Jira title and give some basic analysis in the description.
> Materialization-Failed, when querying with LeftJoinWithFilter
> -
>
> Key: CALCITE-3935
> URL: https://issues.apache.org/jira/browse/CALCITE-3935
> Project: Calcite
> Issue Type: Bug
>Reporter: Xurenhe
>Priority: Major
> Attachments: Jietu20200417-200532.png
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> {code:java}
> @Test public void testJoinOnLeftProjectWithFilterToJoin() {
> String mv = ""
> + "select \"emps\".\"empid\", \"depts\".\"name\",
> \"emps\".\"salary\"\n" +
> "from \"emps\"\n" +
> "left join \"depts\"\n" +
> "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
> + "where \"emps\".\"empid\" > 10";
> String query = ""
> + "select \"emps\".\"empid\", \"depts\".\"name\",
> \"emps\".\"salary\"\n" +
> "from \"emps\"\n" +
> "left join \"depts\"\n" +
> "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
> + "where \"emps\".\"empid\" > 40";
> sql(mv, query).withOnlyBySubstitution(true).ok();
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3935) Materialization-Failed, when querying with LeftJoinWithFilter
[
https://issues.apache.org/jira/browse/CALCITE-3935?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17129254#comment-17129254
]
Xurenhe commented on CALCITE-3935:
--
[~jinxing6...@126.com][~xzh_dz]
Please review this pr: https://github.com/apache/calcite/pull/2013
> Materialization-Failed, when querying with LeftJoinWithFilter
> -
>
> Key: CALCITE-3935
> URL: https://issues.apache.org/jira/browse/CALCITE-3935
> Project: Calcite
> Issue Type: Bug
>Reporter: Xurenhe
>Priority: Major
> Attachments: Jietu20200417-200532.png
>
>
> {code:java}
> @Test public void testJoinOnLeftProjectWithFilterToJoin() {
> String mv = ""
> + "select \"emps\".\"empid\", \"depts\".\"name\",
> \"emps\".\"salary\"\n" +
> "from \"emps\"\n" +
> "left join \"depts\"\n" +
> "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
> + "where \"emps\".\"empid\" > 10";
> String query = ""
> + "select \"emps\".\"empid\", \"depts\".\"name\",
> \"emps\".\"salary\"\n" +
> "from \"emps\"\n" +
> "left join \"depts\"\n" +
> "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
> + "where \"emps\".\"empid\" > 40";
> sql(mv, query).withOnlyBySubstitution(true).ok();
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3935) Materialization-Failed, when querying with LeftJoinWithFilter
[
https://issues.apache.org/jira/browse/CALCITE-3935?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17085707#comment-17085707
]
Jin Xing commented on CALCITE-3935:
---
Thanks !
I really appreciate your fix ~ I can take a review when it's ready :D
- Jin
> Materialization-Failed, when querying with LeftJoinWithFilter
> -
>
> Key: CALCITE-3935
> URL: https://issues.apache.org/jira/browse/CALCITE-3935
> Project: Calcite
> Issue Type: Bug
>Reporter: Xurenhe
>Priority: Major
> Attachments: Jietu20200417-200532.png
>
>
> {code:java}
> @Test public void testJoinOnLeftProjectWithFilterToJoin() {
> String mv = ""
> + "select \"emps\".\"empid\", \"depts\".\"name\",
> \"emps\".\"salary\"\n" +
> "from \"emps\"\n" +
> "left join \"depts\"\n" +
> "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
> + "where \"emps\".\"empid\" > 10";
> String query = ""
> + "select \"emps\".\"empid\", \"depts\".\"name\",
> \"emps\".\"salary\"\n" +
> "from \"emps\"\n" +
> "left join \"depts\"\n" +
> "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
> + "where \"emps\".\"empid\" > 40";
> sql(mv, query).withOnlyBySubstitution(true).ok();
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3935) Materialization-Failed, when querying with LeftJoinWithFilter
[
https://issues.apache.org/jira/browse/CALCITE-3935?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17085706#comment-17085706
]
Xurenhe commented on CALCITE-3935:
--
Yes, I think it need to mitigate requirements.
I am making PR for fixing it.
THX [~jinxing6...@126.com]
> Materialization-Failed, when querying with LeftJoinWithFilter
> -
>
> Key: CALCITE-3935
> URL: https://issues.apache.org/jira/browse/CALCITE-3935
> Project: Calcite
> Issue Type: Bug
>Reporter: Xurenhe
>Priority: Major
> Attachments: Jietu20200417-200532.png
>
>
> {code:java}
> @Test public void testJoinOnLeftProjectWithFilterToJoin() {
> String mv = ""
> + "select \"emps\".\"empid\", \"depts\".\"name\",
> \"emps\".\"salary\"\n" +
> "from \"emps\"\n" +
> "left join \"depts\"\n" +
> "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
> + "where \"emps\".\"empid\" > 10";
> String query = ""
> + "select \"emps\".\"empid\", \"depts\".\"name\",
> \"emps\".\"salary\"\n" +
> "from \"emps\"\n" +
> "left join \"depts\"\n" +
> "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
> + "where \"emps\".\"empid\" > 40";
> sql(mv, query).withOnlyBySubstitution(true).ok();
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (CALCITE-3935) Materialization-Failed, when querying with LeftJoinWithFilter
[
https://issues.apache.org/jira/browse/CALCITE-3935?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17085701#comment-17085701
]
Jin Xing commented on CALCITE-3935:
---
Seems the check in [1] is too strict, we should allow compensating Calc from
the left child node when LEFT JOIN in JoinOnLeftCalcToJoinUnifyRule.
BTW I think the issue can also happen in JoinOnRightCalcToJoinUnifyRule
[[1]
https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java#L1204|https://github.com/apache/calcite/blob/master/core/src/main/java/org/apache/calcite/plan/SubstitutionVisitor.java#L1204]
> Materialization-Failed, when querying with LeftJoinWithFilter
> -
>
> Key: CALCITE-3935
> URL: https://issues.apache.org/jira/browse/CALCITE-3935
> Project: Calcite
> Issue Type: Bug
>Reporter: Xurenhe
>Priority: Major
> Attachments: Jietu20200417-200532.png
>
>
> {code:java}
> @Test public void testJoinOnLeftProjectWithFilterToJoin() {
> String mv = ""
> + "select \"emps\".\"empid\", \"depts\".\"name\",
> \"emps\".\"salary\"\n" +
> "from \"emps\"\n" +
> "left join \"depts\"\n" +
> "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
> + "where \"emps\".\"empid\" > 10";
> String query = ""
> + "select \"emps\".\"empid\", \"depts\".\"name\",
> \"emps\".\"salary\"\n" +
> "from \"emps\"\n" +
> "left join \"depts\"\n" +
> "on \"emps\".\"deptno\" = \"depts\".\"deptno\"\n"
> + "where \"emps\".\"empid\" > 40";
> sql(mv, query).withOnlyBySubstitution(true).ok();
> }
> {code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
