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

From: cpronovost(at)innvue(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #11178: JSONB : The NOT operator applies to the <@ operator, even after casting to ::bool
Date: 2014-08-15 21:11:38
Message-ID: 20140815211138.2543.16364@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 11178
Logged by: Christian Pronovost
Email address: cpronovost(at)innvue(dot)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)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David G Johnston 2014-08-15 23:59:03 Re: BUG #11178: JSONB : The NOT operator applies to the <@ operator, even after casting to ::bool
Previous Message John R Pierce 2014-08-15 20:14:25 Re: Postgresql on windows SSL.