Re: Implement predicate propagation for non-equivalence clauses

Hi,

Thanks everyone for reviewing. We updated the patch and added more strict
checks for the non-equivalence clauses before deducing new quals, including:

1) The non-equivalence clause for deduction can only be type of OpExpr or
ScalarArrayOpExpr, with two arguments.

2) The operator of the non-equivalence clause must be in the eclass's
operator
family, if it intends to deduce new quals based on the eclass.

3) The expression in the non-equivalence clause must be equal to the
EquivalenceMember, if we want to replace that expression to get a new qual.

Thanks
Richard

On Thu, Sep 6, 2018 at 12:01 PM Richard Guo <riguo(at)pivotal(dot)io> wrote:

> Hi,
>
> On Wed, Sep 5, 2018 at 2:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Richard Guo <riguo(at)pivotal(dot)io> writes:
>> > In this patch, we are trying to do the similar deduction, from
>> > non-equivalence
>> > clauses, that is, A=B AND f(A) implies A=B AND f(A) and f(B), under some
>> > restrictions on f.
>>
>> Uh, *what* restrictions on f()? In general the above equivalence
>> does not hold, at least not for any data type more complicated than
>> integers; and we do not have any semantic model for deciding
>> which functions it would be correct for.
>>
>
> Exactly! The operator in f() should be at least in the same opfamily as
> the equivalence class containing A,B.
> Besides, as far as I can consider, the clause in f() should not contain volatile
> functions or subplans. Not sure
> if these restrictions are enough to make it safe.
>
>
>> One simple example to show what I'm talking about is that float8 zero
>> and minus zero are equal according to float8eq (assuming IEEE float
>> arithmetic); but they aren't equivalent for any function f() that is
>> sensitive to the sign or the text representation of the value.
>> The numeric data type likewise has values that are "equal" without
>> being identical for all purposes, eg 0.0 vs 0.000. Or consider
>> citext.
>>
>
> Thanks for the example. Heikki materialized this example as:
>
> create table a (f float8);
> create table b (f float8);
>
> insert into a values ('0'), ('-0');
> insert into b values ('0'), ('-0');
>
> select * from a, b where a.f = b.f and a.f::text <> '-0';
>
> And run that query, this patch would give wrong result. Will address this
> in v2.
>
>
>> The existing planner deduction rules for equivalence classes are
>> carefully designed to ensure that we only generate derived clauses
>> using operators from the same operator class or family, so that
>> it's on the opclass author to ensure that the operators have consistent
>> semantics. I don't think we can extrapolate from that to any random
>> function that accepts the datatype.
>
>
>
>
>
>
>> regards, tom lane
>>
>
>