[jira] [Commented] (CALCITE-3508) Strengthen outer Join to inner if it is under a Filter that discards null values
[ https://issues.apache.org/jira/browse/CALCITE-3508?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16976345#comment-16976345 ] Julian Hyde commented on CALCITE-3508: -- Regarding what you call "negation filters". I think that truth tables on 3-valued boolean logic will turn out to be sufficient, and we don't need to add a new notion of negation filters. "WHERE condition" accepts rows if condition is TRUE, and rejects rows if condition is FALSE or UNKNOWN (the boolean equivalent of NULL). A rewrite of "condition" to "condition2" is acceptable if it "condition2" yields UNKNOWN where "condition" would yield FALSE, or if "condition2" yields FALSE where "condition" would yield UNKNOWN, but not if, say, "condition2" yields TRUE where "condition" would yield UNKNOWN. The NOT operator seems to have weird behavior only if you assume that UNKNOWN and FALSE are equivalent. It's better to think of WHERE having weird behavior (it collapses 3 values to 2 outcomes), in which case NOT has normal behavior. See {{enum RexUnknownAs}}, which is used widely in {{RexSimplify}}. I feel that {{Strong}} and {{RexUnknownAs}} have sufficient expressive power for this planner rule. (There is a slight chance we need an analog of Strong such that {{f(x)}} is null ONLY IF {{x}} is null; the current Strong has that {{f(x)}} is null IF {{x}} is null.) We have invested a lot in {{Strong}} and it would be a mistake to start building something similar. > Strengthen outer Join to inner if it is under a Filter that discards null > values > > > Key: CALCITE-3508 > URL: https://issues.apache.org/jira/browse/CALCITE-3508 > Project: Calcite > Issue Type: Improvement >Reporter: Scott Reynolds >Priority: Major > Labels: pull-request-available > Time Spent: 2h 20m > Remaining Estimate: 0h > > Today, FilterJoinRule given an Outer Join the rule strengthens it to Inner > Join when the nullable side contains a filter IS_NOT_NULL. Below is the code. > {code:java} > for (RexNode filter : aboveFilters) { > if (joinType.generatesNullsOnLeft() > && Strong.isNotTrue(filter, leftBitmap)) { > joinType = joinType.cancelNullsOnLeft(); > } > if (joinType.generatesNullsOnRight() > && Strong.isNotTrue(filter, rightBitmap)) { > joinType = joinType.cancelNullsOnRight(); > } > if (!joinType.isOuterJoin()) { > break; > } > } > {code} > This code looks at the filter to determine if it is always true, then it can > alter the join type by removing the null on that side. > We can see this in the following test RelOptRules#testStrengthenJoinType, > which executes the following SQL that transforms from a LEFT OUTER JOIN to an > INNER JOIN > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.deptno is not null and emp.sal > 100 > {code} > This ticket is about broadening the application of this rule to a sql like > the following: > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.sal > 100 > {code} > This originally came up on the mailing list: > [https://mail-archives.apache.org/mod_mbox/calcite-dev/201909.mbox/%3CCAGzrZ38hh%2B8B8TG8gVHUfVKunJZ4L%2BK7rmrinQxpwOHcdJbzGQ%40mail.gmail.com%3E] > and in that thread it was pointed out that there are filters that prevent > this from being applied: > {code:sql} > SELECT b.title > FROM Book b > LEFT JOIN Author a ON b.author = a.id > WHERE a.name <> 'Victor' > {code} > This means we need to ensure we that the OUTER JOIN doesn't contain – for > lack of a different term – negation filters. If there is a negation – like > NOT_EQUAL – the JOIN cannot be strengthened. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3508) Strengthen outer Join to inner if it is under a Filter that discards null values
[ https://issues.apache.org/jira/browse/CALCITE-3508?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16976332#comment-16976332 ] Julian Hyde commented on CALCITE-3508: -- Strengthening the join type seems to be a natural extension of the rule's behavior. I can't imagine anyone wanting to apply this rule and not wanting to strengthen join type. So, option 1. > Strengthen outer Join to inner if it is under a Filter that discards null > values > > > Key: CALCITE-3508 > URL: https://issues.apache.org/jira/browse/CALCITE-3508 > Project: Calcite > Issue Type: Improvement >Reporter: Scott Reynolds >Priority: Major > Labels: pull-request-available > Time Spent: 2h 20m > Remaining Estimate: 0h > > Today, FilterJoinRule given an Outer Join the rule strengthens it to Inner > Join when the nullable side contains a filter IS_NOT_NULL. Below is the code. > {code:java} > for (RexNode filter : aboveFilters) { > if (joinType.generatesNullsOnLeft() > && Strong.isNotTrue(filter, leftBitmap)) { > joinType = joinType.cancelNullsOnLeft(); > } > if (joinType.generatesNullsOnRight() > && Strong.isNotTrue(filter, rightBitmap)) { > joinType = joinType.cancelNullsOnRight(); > } > if (!joinType.isOuterJoin()) { > break; > } > } > {code} > This code looks at the filter to determine if it is always true, then it can > alter the join type by removing the null on that side. > We can see this in the following test RelOptRules#testStrengthenJoinType, > which executes the following SQL that transforms from a LEFT OUTER JOIN to an > INNER JOIN > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.deptno is not null and emp.sal > 100 > {code} > This ticket is about broadening the application of this rule to a sql like > the following: > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.sal > 100 > {code} > This originally came up on the mailing list: > [https://mail-archives.apache.org/mod_mbox/calcite-dev/201909.mbox/%3CCAGzrZ38hh%2B8B8TG8gVHUfVKunJZ4L%2BK7rmrinQxpwOHcdJbzGQ%40mail.gmail.com%3E] > and in that thread it was pointed out that there are filters that prevent > this from being applied: > {code:sql} > SELECT b.title > FROM Book b > LEFT JOIN Author a ON b.author = a.id > WHERE a.name <> 'Victor' > {code} > This means we need to ensure we that the OUTER JOIN doesn't contain – for > lack of a different term – negation filters. If there is a negation – like > NOT_EQUAL – the JOIN cannot be strengthened. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3508) Strengthen outer Join to inner if it is under a Filter that discards null values
[ https://issues.apache.org/jira/browse/CALCITE-3508?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16976218#comment-16976218 ] Danny Chen commented on CALCITE-3508: - Thanks, [~ScottReynolds], either is okey for me, choose the one that you think is more easier to implement. > Strengthen outer Join to inner if it is under a Filter that discards null > values > > > Key: CALCITE-3508 > URL: https://issues.apache.org/jira/browse/CALCITE-3508 > Project: Calcite > Issue Type: Improvement >Reporter: Scott Reynolds >Priority: Major > Labels: pull-request-available > Time Spent: 1h 10m > Remaining Estimate: 0h > > Today, FilterJoinRule given an Outer Join the rule strengthens it to Inner > Join when the nullable side contains a filter IS_NOT_NULL. Below is the code. > {code:java} > for (RexNode filter : aboveFilters) { > if (joinType.generatesNullsOnLeft() > && Strong.isNotTrue(filter, leftBitmap)) { > joinType = joinType.cancelNullsOnLeft(); > } > if (joinType.generatesNullsOnRight() > && Strong.isNotTrue(filter, rightBitmap)) { > joinType = joinType.cancelNullsOnRight(); > } > if (!joinType.isOuterJoin()) { > break; > } > } > {code} > This code looks at the filter to determine if it is always true, then it can > alter the join type by removing the null on that side. > We can see this in the following test RelOptRules#testStrengthenJoinType, > which executes the following SQL that transforms from a LEFT OUTER JOIN to an > INNER JOIN > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.deptno is not null and emp.sal > 100 > {code} > This ticket is about broadening the application of this rule to a sql like > the following: > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.sal > 100 > {code} > This originally came up on the mailing list: > [https://mail-archives.apache.org/mod_mbox/calcite-dev/201909.mbox/%3CCAGzrZ38hh%2B8B8TG8gVHUfVKunJZ4L%2BK7rmrinQxpwOHcdJbzGQ%40mail.gmail.com%3E] > and in that thread it was pointed out that there are filters that prevent > this from being applied: > {code:sql} > SELECT b.title > FROM Book b > LEFT JOIN Author a ON b.author = a.id > WHERE a.name <> 'Victor' > {code} > This means we need to ensure we that the OUTER JOIN doesn't contain – for > lack of a different term – negation filters. If there is a negation – like > NOT_EQUAL – the JOIN cannot be strengthened. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3508) Strengthen outer Join to inner if it is under a Filter that discards null values
[ https://issues.apache.org/jira/browse/CALCITE-3508?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16976210#comment-16976210 ] Scott Reynolds commented on CALCITE-3508: - I am left with one more remaining design question. Today the {{FilterJoinRule}} has the following Javadoc {code:java} /** * Planner rule that pushes filters above and * within a join node into the join node and/or its children nodes. */ {code} We should consider doing one of two things: 1. Updating the javadoc to indicate it can Strengthen the join type 2. Break the Strengthen join type into a separate rule Should we move this logic into a separate rule like we do with {{JoinProjectTransposeRule}} > Strengthen outer Join to inner if it is under a Filter that discards null > values > > > Key: CALCITE-3508 > URL: https://issues.apache.org/jira/browse/CALCITE-3508 > Project: Calcite > Issue Type: Improvement >Reporter: Scott Reynolds >Priority: Major > Labels: pull-request-available > Time Spent: 1h 10m > Remaining Estimate: 0h > > Today, FilterJoinRule given an Outer Join the rule strengthens it to Inner > Join when the nullable side contains a filter IS_NOT_NULL. Below is the code. > {code:java} > for (RexNode filter : aboveFilters) { > if (joinType.generatesNullsOnLeft() > && Strong.isNotTrue(filter, leftBitmap)) { > joinType = joinType.cancelNullsOnLeft(); > } > if (joinType.generatesNullsOnRight() > && Strong.isNotTrue(filter, rightBitmap)) { > joinType = joinType.cancelNullsOnRight(); > } > if (!joinType.isOuterJoin()) { > break; > } > } > {code} > This code looks at the filter to determine if it is always true, then it can > alter the join type by removing the null on that side. > We can see this in the following test RelOptRules#testStrengthenJoinType, > which executes the following SQL that transforms from a LEFT OUTER JOIN to an > INNER JOIN > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.deptno is not null and emp.sal > 100 > {code} > This ticket is about broadening the application of this rule to a sql like > the following: > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.sal > 100 > {code} > This originally came up on the mailing list: > [https://mail-archives.apache.org/mod_mbox/calcite-dev/201909.mbox/%3CCAGzrZ38hh%2B8B8TG8gVHUfVKunJZ4L%2BK7rmrinQxpwOHcdJbzGQ%40mail.gmail.com%3E] > and in that thread it was pointed out that there are filters that prevent > this from being applied: > {code:sql} > SELECT b.title > FROM Book b > LEFT JOIN Author a ON b.author = a.id > WHERE a.name <> 'Victor' > {code} > This means we need to ensure we that the OUTER JOIN doesn't contain – for > lack of a different term – negation filters. If there is a negation – like > NOT_EQUAL – the JOIN cannot be strengthened. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3508) Strengthen outer Join to inner if it is under a Filter that discards null values
[ https://issues.apache.org/jira/browse/CALCITE-3508?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16975860#comment-16975860 ] Danny Chen commented on CALCITE-3508: - Thanks [~ScottReynolds], i think the Strong policy is a little different with what you needs, the Strong defines some policies that decide "how does the RexCalls returns nulls if some of their operands are null values", but what you need is "The operands are already null, then if the expression returns null or false". I think they have a lot in common, and we can define a new method named `Strong.isNotFalse()`. > Strengthen outer Join to inner if it is under a Filter that discards null > values > > > Key: CALCITE-3508 > URL: https://issues.apache.org/jira/browse/CALCITE-3508 > Project: Calcite > Issue Type: Improvement >Reporter: Scott Reynolds >Priority: Major > Labels: pull-request-available > Time Spent: 1h 10m > Remaining Estimate: 0h > > Today, FilterJoinRule given an Outer Join the rule strengthens it to Inner > Join when the nullable side contains a filter IS_NOT_NULL. Below is the code. > {code:java} > for (RexNode filter : aboveFilters) { > if (joinType.generatesNullsOnLeft() > && Strong.isNotTrue(filter, leftBitmap)) { > joinType = joinType.cancelNullsOnLeft(); > } > if (joinType.generatesNullsOnRight() > && Strong.isNotTrue(filter, rightBitmap)) { > joinType = joinType.cancelNullsOnRight(); > } > if (!joinType.isOuterJoin()) { > break; > } > } > {code} > This code looks at the filter to determine if it is always true, then it can > alter the join type by removing the null on that side. > We can see this in the following test RelOptRules#testStrengthenJoinType, > which executes the following SQL that transforms from a LEFT OUTER JOIN to an > INNER JOIN > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.deptno is not null and emp.sal > 100 > {code} > This ticket is about broadening the application of this rule to a sql like > the following: > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.sal > 100 > {code} > This originally came up on the mailing list: > [https://mail-archives.apache.org/mod_mbox/calcite-dev/201909.mbox/%3CCAGzrZ38hh%2B8B8TG8gVHUfVKunJZ4L%2BK7rmrinQxpwOHcdJbzGQ%40mail.gmail.com%3E] > and in that thread it was pointed out that there are filters that prevent > this from being applied: > {code:sql} > SELECT b.title > FROM Book b > LEFT JOIN Author a ON b.author = a.id > WHERE a.name <> 'Victor' > {code} > This means we need to ensure we that the OUTER JOIN doesn't contain – for > lack of a different term – negation filters. If there is a negation – like > NOT_EQUAL – the JOIN cannot be strengthened. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3508) Strengthen outer Join to inner if it is under a Filter that discards null values
[ https://issues.apache.org/jira/browse/CALCITE-3508?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16975780#comment-16975780 ] Scott Reynolds commented on CALCITE-3508: - Thank you for the subject change it is considerably better! Updated pull request title to reflect that as well. Looking at {code:java} JoinProjectTransposeRule{code} I see that it is using {code:java} /** Returns whether all expressions in a list are strong. */ public static boolean allStrong(List operands) {code} Today, this will return true for {code:java} a.name <> 'Victor'{code} . Is that a what we should expect from {noformat}public static boolean isStrong(RexNode){noformat} ? Perhaps we should extend that to cover more cases? {code:java} /** * Returns whether a given expression is strong. * * Examples: * * Returns true for {@code c = 1} since it returns null if and only if * c is null * Returns false for {@code c IS NULL} since it always returns TRUE * or FALSE * * * @param e Expression * @return true if the expression is strong, false otherwise */ public static boolean isStrong(RexNode e) {code} To handle `RexCall`s with negations etc? > Strengthen outer Join to inner if it is under a Filter that discards null > values > > > Key: CALCITE-3508 > URL: https://issues.apache.org/jira/browse/CALCITE-3508 > Project: Calcite > Issue Type: Improvement >Reporter: Scott Reynolds >Priority: Major > Labels: pull-request-available > Time Spent: 0.5h > Remaining Estimate: 0h > > Today, FilterJoinRule given an Outer Join the rule strengthens it to Inner > Join when the nullable side contains a filter IS_NOT_NULL. Below is the code. > {code:java} > for (RexNode filter : aboveFilters) { > if (joinType.generatesNullsOnLeft() > && Strong.isNotTrue(filter, leftBitmap)) { > joinType = joinType.cancelNullsOnLeft(); > } > if (joinType.generatesNullsOnRight() > && Strong.isNotTrue(filter, rightBitmap)) { > joinType = joinType.cancelNullsOnRight(); > } > if (!joinType.isOuterJoin()) { > break; > } > } > {code} > This code looks at the filter to determine if it is always true, then it can > alter the join type by removing the null on that side. > We can see this in the following test RelOptRules#testStrengthenJoinType, > which executes the following SQL that transforms from a LEFT OUTER JOIN to an > INNER JOIN > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.deptno is not null and emp.sal > 100 > {code} > This ticket is about broadening the application of this rule to a sql like > the following: > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.sal > 100 > {code} > This originally came up on the mailing list: > [https://mail-archives.apache.org/mod_mbox/calcite-dev/201909.mbox/browser] > and in that thread it was pointed out that there are filters that prevent > this from being applied: > {code:sql} > SELECT b.title > FROM Book b > LEFT JOIN Author a ON b.author = a.id > WHERE a.name <> 'Victor' > {code} > This means we need to ensure we that the OUTER JOIN doesn't contain – for > lack of a different term – negation filters. If there is a negation – like > NOT_EQUAL – the JOIN cannot be strengthened. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3508) Strengthen outer Join to inner if it is under a Filter that discards null values
[ https://issues.apache.org/jira/browse/CALCITE-3508?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16975599#comment-16975599 ] Danny Chen commented on CALCITE-3508: - I agree with Julian, we may need a method like `Strong.isNotFalse()` to decide that a filter condition would never returns null or false value. > Strengthen outer Join to inner if it is under a Filter that discards null > values > > > Key: CALCITE-3508 > URL: https://issues.apache.org/jira/browse/CALCITE-3508 > Project: Calcite > Issue Type: Improvement >Reporter: Scott Reynolds >Priority: Major > Labels: pull-request-available > Time Spent: 20m > Remaining Estimate: 0h > > Today, FilterJoinRule given an Outer Join the rule strengthens it to Inner > Join when the nullable side contains a filter IS_NOT_NULL. Below is the code. > {code:java} > for (RexNode filter : aboveFilters) { > if (joinType.generatesNullsOnLeft() > && Strong.isNotTrue(filter, leftBitmap)) { > joinType = joinType.cancelNullsOnLeft(); > } > if (joinType.generatesNullsOnRight() > && Strong.isNotTrue(filter, rightBitmap)) { > joinType = joinType.cancelNullsOnRight(); > } > if (!joinType.isOuterJoin()) { > break; > } > } > {code} > This code looks at the filter to determine if it is always true, then it can > alter the join type by removing the null on that side. > We can see this in the following test RelOptRules#testStrengthenJoinType, > which executes the following SQL that transforms from a LEFT OUTER JOIN to an > INNER JOIN > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.deptno is not null and emp.sal > 100 > {code} > This ticket is about broadening the application of this rule to a sql like > the following: > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.sal > 100 > {code} > This originally came up on the mailing list: > [https://mail-archives.apache.org/mod_mbox/calcite-dev/201909.mbox/browser] > and in that thread it was pointed out that there are filters that prevent > this from being applied: > {code:sql} > SELECT b.title > FROM Book b > LEFT JOIN Author a ON b.author = a.id > WHERE a.name <> 'Victor' > {code} > This means we need to ensure we that the OUTER JOIN doesn't contain – for > lack of a different term – negation filters. If there is a negation – like > NOT_EQUAL – the JOIN cannot be strengthened. -- This message was sent by Atlassian Jira (v8.3.4#803005)
[jira] [Commented] (CALCITE-3508) Strengthen outer Join to inner if it is under a Filter that discards null values
[ https://issues.apache.org/jira/browse/CALCITE-3508?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16975520#comment-16975520 ] Julian Hyde commented on CALCITE-3508: -- I changed the subject, because "FILTER clause" is an ambiguous - some people might think it referred to, say, "SUM(x) FILTER (WHERE y > 0)". > Strengthen outer Join to inner if it is under a Filter that discards null > values > > > Key: CALCITE-3508 > URL: https://issues.apache.org/jira/browse/CALCITE-3508 > Project: Calcite > Issue Type: Improvement >Reporter: Scott Reynolds >Priority: Major > Labels: pull-request-available > Time Spent: 10m > Remaining Estimate: 0h > > Today, FilterJoinRule given an Outer Join the rule strengthens it to Inner > Join when the nullable side contains a filter IS_NOT_NULL. Below is the code. > {code:java} > for (RexNode filter : aboveFilters) { > if (joinType.generatesNullsOnLeft() > && Strong.isNotTrue(filter, leftBitmap)) { > joinType = joinType.cancelNullsOnLeft(); > } > if (joinType.generatesNullsOnRight() > && Strong.isNotTrue(filter, rightBitmap)) { > joinType = joinType.cancelNullsOnRight(); > } > if (!joinType.isOuterJoin()) { > break; > } > } > {code} > This code looks at the filter to determine if it is always true, then it can > alter the join type by removing the null on that side. > We can see this in the following test RelOptRules#testStrengthenJoinType, > which executes the following SQL that transforms from a LEFT OUTER JOIN to an > INNER JOIN > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.deptno is not null and emp.sal > 100 > {code} > This ticket is about broadening the application of this rule to a sql like > the following: > {code:sql} > select * > from dept left join emp on dept.deptno = emp.deptno > where emp.sal > 100 > {code} > This originally came up on the mailing list: > [https://mail-archives.apache.org/mod_mbox/calcite-dev/201909.mbox/browser] > and in that thread it was pointed out that there are filters that prevent > this from being applied: > {code:sql} > SELECT b.title > FROM Book b > LEFT JOIN Author a ON b.author = a.id > WHERE a.name <> 'Victor' > {code} > This means we need to ensure we that the OUTER JOIN doesn't contain – for > lack of a different term – negation filters. If there is a negation – like > NOT_EQUAL – the JOIN cannot be strengthened. -- This message was sent by Atlassian Jira (v8.3.4#803005)