json ->> operator precedence
An interesting quirk:
# select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes' ELSE 'no'
END;
case
------
yes
According to the precedence table
http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html I would
expect ->> to come under "all other native and user-defined operators",
which would imply that this command should be testing whether 'a' IS NULL
and applying the result (false) to the json operator - at which point we
have
# SELECT CASE WHEN '{"a":null}'::jsonb->>false THEN 'yes' ELSE 'no' END;
and since
# SELECT '{"a":null}'::jsonb->>false;
returns NULL, the query is effectively:
# SELECT CASE WHEN NULL THEN 'yes' ELSE 'no' END;
which returns 'no'.
So the only way that we should get 'yes' is if the ->> has higher
precedence than 'IS NULL'.
OK, so be it; except if we assume that the reason is because the lex
analyzer sees '-' and assumes higher precedence than 'IS NULL' then you
would expect
SELECT '{"a":10}'::jsonb->>'a' - 5;
to return '5' - since left-to-right precedence would make ->> run before
the subtraction; however I get:
ERROR: invalid input syntax for integer: "a"
LINE 1: select '{"a":10}'::jsonb->>'a' - 5;
So what precedence level is ->> actually running at?
Or am I missing something?
Cheers
Geoff
On Wed, Aug 5, 2015 at 5:02 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
An interesting quirk:
# select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes' ELSE 'no'
END;
case
------
yes
According to the precedence table
http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html I would
expect ->> to come under "all other native and user-defined operators",
which would imply that this command should be testing whether 'a' IS NULL
and applying the result (false) to the json operator - at which point we
have# SELECT CASE WHEN '{"a":null}'::jsonb->>false THEN 'yes' ELSE 'no' END;
and since
# SELECT '{"a":null}'::jsonb->>false;
returns NULL, the query is effectively:
# SELECT CASE WHEN NULL THEN 'yes' ELSE 'no' END;
which returns 'no'.
So the only way that we should get 'yes' is if the ->> has higher
precedence than 'IS NULL'.OK, so be it; except if we assume that the reason is because the lex
analyzer sees '-' and assumes higher precedence than 'IS NULL' then you
would expectSELECT '{"a":10}'::jsonb->>'a' - 5;
to return '5' - since left-to-right precedence would make ->> run before
the subtraction; however I get:ERROR: invalid input syntax for integer: "a"
LINE 1:
select '{"a":10}'::jsonb->>'a' - 5;
So what precedence level is ->> actually running at?
Or am I missing something?
Looks correct to me. As I understand it the ::jsonb is NOT an operator! It
is a syntactic construct for a CAST(). An equivalent which might make more
sense is:
select CASE WHEN CAST('{"a":null}' AS JSONB)->>'a' IS NULL THEN 'yes' ELSE
'no' END;
Oh, an CAST() may look like a function call, but it is also a syntactic
element. I.e. there is not a function called "CAST".
Cheers
Geoff
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
On 5 August 2015 at 14:35, John McKown <john.archie.mckown@gmail.com> wrote:
Looks correct to me. As I understand it the ::jsonb is NOT an operator!
It is a syntactic construct for a CAST(). An equivalent which might make
more sense is:
My issue is nothing to do with the ::jsonb cast, it's the precedence of
the ->> operator.
As far as I can see the ->> operator has predence below '- +' but above
'IS', but there's no entry for it in that place in the precedence table.
Geoff
On Wed, Aug 5, 2015 at 8:35 AM, John McKown <john.archie.mckown@gmail.com>
wrote:
Looks correct to me. As I understand it the ::jsonb is NOT an operator!
It is a syntactic construct for a CAST(). An equivalent which might make
more sense is:select CASE WHEN CAST('{"a":null}' AS JSONB)->>'a' IS NULL THEN 'yes' ELSE
'no' END;Oh, an CAST() may look like a function call, but it is also a syntactic
element. I.e. there is not a function called "CAST".
Well, I messed that up a bit. CAST is not a "syntactic element" as I was
thinking. But it's not really a function call either but is closer to a
function call than an operator in nature (I my ignorant opinion) . I was
thinking it was a "compile time" operation, but it, like a function call,
is a run-time operation. I think that using the CASE() makes it plainer
that it is _not_ an operation like ->> or - and makes the precedence issue
plainer.
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
On Wed, Aug 5, 2015 at 8:42 AM, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
On 5 August 2015 at 14:35, John McKown <john.archie.mckown@gmail.com>
wrote:Looks correct to me. As I understand it the ::jsonb is NOT an operator!
It is a syntactic construct for a CAST(). An equivalent which might make
more sense is:My issue is nothing to do with the ::jsonb cast, it's the precedence of
the ->> operator.As far as I can see the ->> operator has predence below '- +' but above
'IS', but there's no entry for it in that place in the precedence table.
Ah. I see your point now. Sorry about that.
Geoff
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
Geoff Winkless <pgsqladmin@geoff.dj> writes:
An interesting quirk:
# select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes' ELSE 'no'
END;
case
------
yes
Apparently you're running that on 9.5 or HEAD.
According to the precedence table
http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html I would
expect ->> to come under "all other native and user-defined operators",
It does ...
which would imply that this command should be testing whether 'a' IS NULL
and applying the result (false) to the json operator - at which point we
have
... and in 9.4 that's what happens:
regression=# select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes' ELSE 'no'
END;
ERROR: operator does not exist: jsonb ->> boolean
LINE 1: select CASE WHEN '{"a":null}'::jsonb->>'a' IS NULL THEN 'yes...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Or am I missing something?
The first compatibility item in the 9.5 release notes: we changed
the precedence of IS and some other things. You need to be reading
the 9.5 version of the precedence table.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general