[jira] [Comment Edited] (CALCITE-2074) Simplification of point ranges that are open above or below yields wrong results

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-2074?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16274984#comment-16274984 ] 

Julian Hyde edited comment on CALCITE-2074 at 12/7/17 9:27 AM:
---------------------------------------------------------------

An even simpler test case (without OR) fails:

{code}
    // condition "a > 5"
    // with pre-condition "a <= 5"
    // should yield "false" but yields "a = 5"
    checkSimplifyFilter(gt(aRef, literal5),
        RelOptPredicateList.of(rexBuilder,
            ImmutableList.of(le(aRef, literal5))),
        "false");
{code}

The problem is that Calcite creates a range for {{a}} that has the same upper and lower bound (5), but the lower bound is closed (does not include 5) while the upper bound is open (does include 5). It is correct to simplify an point range to the bound, i.e. {{a = 5}}, only if both bounds are open; but if either bound is closed, as in this case, no values satisfy, therefore it should simplify to {{false}}.

Yes, I think this is a show-stopper for 1.15 RC0.


was (Author: julianhyde):
An even simpler test case (without OR) fails:

{code}
    // condition "a > 5"
    // with pre-condition "a <= 5"
    // should yield "false" but yields "a = 5"
    checkSimplifyFilter(gt(aRef, literal5),
        RelOptPredicateList.of(rexBuilder,
            ImmutableList.of(le(aRef, literal5))),
        "false");
{code}

Yes, I think this is a show-stopper for 1.15 RC0.

> Simplification of point ranges that are open above or below yields wrong results
> --------------------------------------------------------------------------------
>
>                 Key: CALCITE-2074
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2074
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.15.0
>            Reporter: Jesus Camacho Rodriguez
>            Assignee: Julian Hyde
>             Fix For: 1.15.0
>
>
> Discovered while testing 1.15.0 RC0 with Hive. It seems this regression was introduced by CALCITE-1995.
> Consider the following query:
> {code}
> select * from (
> select a.*,b.d d1,c.d d2 from
>   t1 a join t2 b on (a.id1 = b.id)
>        join t2 c on (a.id2 = b.id) where b.d <= 1 and c.d <= 1
> ) z where d1 > 1 or d2 > 1
> {code}
> We end up generating the following plan:
> {code}
> HiveProject(id1=[$0], id2=[$1], d1=[$3], d2=[$4])
>   HiveJoin(condition=[OR(=($3, 1), =($4, 1))], joinType=[inner], algorithm=[none], cost=[not available])
>     HiveJoin(condition=[AND(=($0, $2), =($1, $2))], joinType=[inner], algorithm=[none], cost=[not available])
>       HiveFilter(condition=[AND(IS NOT NULL($0), IS NOT NULL($1))])
>         HiveProject(id1=[$0], id2=[$1])
>           HiveTableScan(table=[[default.t1]], table:alias=[a])
>       HiveFilter(condition=[AND(<=($1, 1), IS NOT NULL($0))])
>         HiveProject(id=[$0], d=[$1])
>           HiveTableScan(table=[[default.t2]], table:alias=[b])
>     HiveFilter(condition=[<=($0, 1)])
>       HiveProject(d=[$1])
>         HiveTableScan(table=[[default.t2]], table:alias=[c])
> {code}
> Observe that the condition in the top join is not correct.
> I can reproduce this in {{RexProgramTest.simplifyFilter}} with the following example:
> {code}
>     // condition "a > 5 or b > 5"
>     // with pre-condition "a <= 5 and b <= 5"
>     // should yield "false" but yields "a = 5 or b = 5"
>     checkSimplifyFilter(or(gt(aRef, literal5),gt(bRef, literal5)),
>         RelOptPredicateList.of(rexBuilder,
>             ImmutableList.of(le(aRef, literal5), le(bRef, literal5))),
>         "false");
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)