Re: Re: BUG #11178: JSONB : The NOT operator applies to the <@ operator, even after casting to ::bool

David G Johnston <david.g.johnston@gmail.com> writes:
> Apparently "NOT()" is both an operator itself (which acts on a boolean) and
> is also an optimization mechanic (which acts on an operator).  Since the
> non-table version does not require any optimization the function behavior is
> taken and the correct answer is returned.  In a table context - *even when
> the expression is not in a WHERE clause apparently* ... - the optimization
> code checks to see whether the associated operator has an associated negator
> function and then, if present, tries to compare a direct equality using the
> matched negator function.  Since jsonb incorrectly had just such an
> operator, which indeed caused PostgreSQL to attempt to match equality using
> the "@>" operator, the behavior you saw was manifested.

Right, the transformation that's applied is

    NOT (x op y)  ==>  x notop y

if operator "op" is declared to have a negator operator "notop".
Since <@ was incorrectly declared to have @> as its negator, the
expression simplification machinery was just doing what it was told.

The transformation that was *meant* to be applicable is the commutator
substitution,

    x <@ y  ==>  y @> x

While that wouldn't be of any great value (and would not be used) in this
particular instance, it's essential to have commutator pairs for indexable
operators, because PG's indexing machinery can only cope with clauses in
which the indexed column is on the left.

Also, IIRC, the negator and commutator substitutions don't get considered
until after eval_const_expressions() is run; so something like
    NOT('"1"'::jsonb <@ '["2"]')
will get folded to constant true before there's any opportunity for the
bug to manifest.

            regards, tom lane