[
https://issues.apache.org/jira/browse/CALCITE-3192?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17034725#comment-17034725
]
Julian Hyde commented on CALCITE-3192:
--------------------------------------
I'd do a truth table of a = (null, 0, 1, 2, 3, 4) and b = (null, 1, 2). Doing
the algebra is too error-prone.
{noformat}
sqlline> !connect jdbc:calcite: sa sa
sqlline> select a, b,
1 < a or (a < 3 and b = 2) as "1 < a or (a < 3 and b = 2)",
1 < a or b = 2 as "1 < a or b = 2"
from (values (null), (0), (1), (2), (3), (4)) as x(a),
(values (null), (1), (2)) as y(b)
order by a nulls first, b nulls first;
+------+------+----------------------------+----------------+
| A | B | 1 < a or (a < 3 and b = 2) | 1 < a or b = 2 |
+------+------+----------------------------+----------------+
| null | null | | |
| null | 1 | | |
| null | 2 | | true |
| 0 | null | | |
| 0 | 1 | false | false |
| 0 | 2 | true | true |
| 1 | null | | |
| 1 | 1 | false | false |
| 1 | 2 | true | true |
| 2 | null | true | true |
| 2 | 1 | true | true |
| 2 | 2 | true | true |
| 3 | null | true | true |
| 3 | 1 | true | true |
| 3 | 2 | true | true |
| 4 | null | true | true |
| 4 | 1 | true | true |
| 4 | 2 | true | true |
+------+------+----------------------------+----------------+
18 rows selected (0.071 seconds)
sqlline> !connect jdbc:hsqldb:mem:scott scott tiger
sqlline> select a, b,
1 < a or (a < 3 and b = 2) as "1 < a or (a < 3 and b = 2)",
1 < a or b = 2 as "1 < a or b = 2"
from (values (null), (0), (1), (2), (3), (4)) as x(a),
(values (null), (1), (2)) as y(b)
order by a, b;
+------+------+----------------------------+----------------+
| A | B | 1 < a or (a < 3 and b = 2) | 1 < a or b = 2 |
+------+------+----------------------------+----------------+
| null | null | | |
| null | 1 | | |
| null | 2 | | TRUE |
| 0 | null | | |
| 0 | 1 | FALSE | FALSE |
| 0 | 2 | TRUE | TRUE |
| 1 | null | | |
| 1 | 1 | FALSE | FALSE |
| 1 | 2 | TRUE | TRUE |
| 2 | null | TRUE | TRUE |
| 2 | 1 | TRUE | TRUE |
| 2 | 2 | TRUE | TRUE |
| 3 | null | TRUE | TRUE |
| 3 | 1 | TRUE | TRUE |
| 3 | 2 | TRUE | TRUE |
| 4 | null | TRUE | TRUE |
| 4 | 1 | TRUE | TRUE |
| 4 | 2 | TRUE | TRUE |
+------+------+----------------------------+----------------+
18 rows selected (0.006 seconds)
{noformat}
I assume that hsqldb gives the right answer, and I am glad to say that Calcite
agrees.
It tells us that the proposed rewrite is not valid if a is null and b is 2.
> Simplification may weaken OR conditions containing inequalities
> ---------------------------------------------------------------
>
> Key: CALCITE-3192
> URL: https://issues.apache.org/jira/browse/CALCITE-3192
> Project: Calcite
> Issue Type: Bug
> Reporter: Jess Balint
> Assignee: Zoltan Haindrich
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.21.0
>
> Time Spent: 50m
> Remaining Estimate: 0h
>
> RexSimplify is transforming
> * {{OR(AND(>(999, $8), =($2, 'Franklin')), <(100, $8))}}
> * to {{OR(=($2, 'Franklin'), <(100, $8))}}
> the predicates are accumulated in {{simplifyOrTerms()}} but not discarded
> when iterating the second time
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
