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

Christian Pronovost wrote
>
> David G Johnston wrote
>> The presence of "NOT" does not (aside from a possible bug) change the
>> "<@" operator into the "@>" operator.
> I was wrong assuming the inversion of the "<@" operator.

As Tom noted you were correct in your observation but you were observing a
bug...see below, and Tom's comments regarding "operator negator functions",
for the knowledge I was missing when I replied the first time.

>> "NOT" simply inverts the supplied boolean value.
> This does not seem to be the case. In the following example, the same
> query returns false whether there is a NOT operator or not.
>
> SELECT (testcolumn->'ID' <@ '["2"]') FROM "TestJsonb" --Returns false (as
> expected)
> SELECT NOT(testcolumn->'ID' <@ '["2"]') FROM "TestJsonb" --Returns false (
*
> not as expected
*
> )
>
> To be noted that outside of the context of a table, this works fine:
>
> SELECT ('"1"'::jsonb <@ '["2"]') --Returns false (as expected)
> SELECT NOT('"1"'::jsonb <@ '["2"]') --Returns true (as expected)
>
> I am confused

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.

I feel I am missing something in the above conclusion but it is what comes
to mind why I try to explain what you are showing here.

Furthermore, I am still confused why your example:

NOT((... @< ...)::text)::bool

failed to fail...the cast to text should blow things up since the NOT
shouldn't be able to cross the casting boundary to see the operator for
optimization...

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-11178-JSONB-The-NOT-operator-applies-to-the-operator-even-after-casting-to-bool-tp5815056p5815230.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.