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

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.