Обсуждение: BUG #11178: JSONB : The NOT operator applies to the <@ operator, even after casting to ::bool

The following bug has been logged on the website:

Bug reference:      11178
Logged by:          Christian Pronovost
Email address:      cpronovost@innvue.com
PostgreSQL version: 9.4beta2
Operating system:   Windows 7 Pro SP1
Description:

When using the <@ operator in conjunction with the NOT operator on a jsonb
column, the NOT reverse the <@ operator(becomes a @>).

However, when casting the result of the <@ operation to a ::bool, the NOT
operator stills applies to the <@ operator, rather than the ::bool.

Note: the NOT operator will apply to the ::bool if it is casted to ::text
prior to ::bool.(See complete example below)

Is the ::bool cast ignored since the operation already returns a boolean?
(causing the NOT operator to apply to the jsonb <@ Operator instead?)

Regards,
Chris




CREATE TABLE "TestJsonb"
(
  testcolumn jsonb
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "TestJsonb"
  OWNER TO postgres;

INSERT INTO "TestJsonb" VALUES ('{"ID":"1"}')

SELECT testcolumn->'ID' <@ '["1"]' FROM "TestJsonb" --Returns true (as
expected)
SELECT NOT(testcolumn->'ID' <@ '["1"]') FROM "TestJsonb" --Returns false (as
expected)

SELECT testcolumn->'ID' <@ '["2"]' FROM"TestJsonb" --Returns false (as
expected)
SELECT NOT((testcolumn->'ID' <@ '["2"]')::bool) FROM "TestJsonb" --Returns
false (not as expected, seems to change the '<@' operator to '@>')

SELECT NOT((testcolumn->'ID' <@ '["2"]')::text)::bool FROM "TestJsonb"
--Returns true (as expected)

cpronovost wrote
> The following bug has been logged on the website:
>
> Bug reference:      11178
> Logged by:          Christian Pronovost
> Email address:

> cpronovost@

> PostgreSQL version: 9.4beta2
> Operating system:   Windows 7 Pro SP1
> Description:
>
> When using the <@ operator in conjunction with the NOT operator on a jsonb
> column, the NOT reverse the <@ operator(becomes a @>).
>
> However, when casting the result of the <@ operation to a ::bool, the NOT
> operator stills applies to the <@ operator, rather than the ::bool.

The presence of "NOT" does not (aside from a possible bug) change the "<@"
operator into the "@>" operator.  "NOT" simply inverts the supplied boolean
value so that "not(true) := false" and vice-versa.

Mutually exclusive json values will result in false being returned no matter
which operator is used.


> Note: the NOT operator will apply to the ::bool if it is casted to ::text
> prior to ::bool.(See complete example below)
>
> Is the ::bool cast ignored since the operation already returns a boolean?
> (causing the NOT operator to apply to the jsonb <@ Operator instead?)

The cast is likely ignored if the input is already of the desired type - but
it shouldn't matter either way.

And as noted below casting the bool to a text and applying the NOT should
fail - not serve as a workaround...


> CREATE TABLE "TestJsonb"
> (
>   testcolumn jsonb
> )
> WITH (
>   OIDS=FALSE
> );
> ALTER TABLE "TestJsonb"
>   OWNER TO postgres;
>
> INSERT INTO "TestJsonb" VALUES ('{"ID":"1"}')
>
> SELECT testcolumn->'ID' <@ '["1"]' FROM "TestJsonb" --Returns true (as
> expected)
> SELECT NOT(testcolumn->'ID' <@ '["1"]') FROM "TestJsonb" --Returns false
> (as
> expected)
>
> SELECT testcolumn->'ID' <@ '["2"]' FROM"TestJsonb" --Returns false (as
> expected)
> SELECT NOT((testcolumn->'ID' <@ '["2"]')::bool) FROM "TestJsonb" --Returns
> false (not as expected, seems to change the '<@' operator to '@>')
>
> SELECT NOT((testcolumn->'ID' <@ '["2"]')::text)::bool FROM "TestJsonb"
> --Returns true (as expected)

I cannot test it myself but you are correct that the behavior of the
NOT((...<@...)::bool) is wrong; though confusingly so...

In the last scenario I am also confused why it actually evaluates in the
first place.

SELECT NOT('false'::text);  emits an error in 9.3.4 (argument of NOT must be
type boolean, not type text)

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-tp5815056p5815058.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

David G Johnston <david.g.johnston@gmail.com> writes:
> cpronovost wrote
>> When using the <@ operator in conjunction with the NOT operator on a jsonb
>> column, the NOT reverse the <@ operator(becomes a @>).

> The presence of "NOT" does not (aside from a possible bug) change the "<@"
> operator into the "@>" operator.  "NOT" simply inverts the supplied boolean
> value so that "not(true) := false" and vice-versa.

That's what it's *supposed* to do, but somebody got the pg_operator entry
wrong.  It looks to me like pg_operator OIDs 3246 and 3250 should be
listed as each others' commutators, not each others' negators.

Sigh.  So much for no initdb for 9.4beta3.  I wonder whether there are
other thinkos in the jsonb operator entries...

            regards, tom lane

On Fri, Aug 15, 2014 at 11:52:30PM -0400, Tom Lane wrote:
> David G Johnston <david.g.johnston@gmail.com> writes:
> > cpronovost wrote
> >> When using the <@ operator in conjunction with the NOT operator on a jsonb
> >> column, the NOT reverse the <@ operator(becomes a @>).
>
> > The presence of "NOT" does not (aside from a possible bug) change the "<@"
> > operator into the "@>" operator.  "NOT" simply inverts the supplied boolean
> > value so that "not(true) := false" and vice-versa.
>
> That's what it's *supposed* to do, but somebody got the pg_operator entry
> wrong.  It looks to me like pg_operator OIDs 3246 and 3250 should be
> listed as each others' commutators, not each others' negators.
>
> Sigh.  So much for no initdb for 9.4beta3.  I wonder whether there are
> other thinkos in the jsonb operator entries...

Just to be clear, pg_upgrade can handle system catalog changes, while it
can't handle data format changes.  However, I support changing the JSONB
data format to be optimal.  FYI, I have always been concerned that
pg_upgrade would inhibit data storage format improvements.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +

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.


> "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



--
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-tp5815056p5815229.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

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.

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