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)