Обсуждение: BUG #11178: JSONB : The NOT operator applies to the <@ operator, even after casting to ::bool
BUG #11178: JSONB : The NOT operator applies to the <@ operator, even after casting to ::bool
От
cpronovost@innvue.com
Дата:
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)
Re: BUG #11178: JSONB : The NOT operator applies to the <@ operator, even after casting to ::bool
От
David G Johnston
Дата:
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. +
Re: BUG #11178: JSONB : The NOT operator applies to the <@ operator, even after casting to ::bool
От
Christian Pronovost
Дата:
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.
Re: BUG #11178: JSONB : The NOT operator applies to the <@ operator, even after casting to ::bool
От
David G Johnston
Дата:
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