Home > mailing lists

Thread: json ->> operator precedence

json ->> operator precedence

From

Geoff Winkless

Date:

05 August 2015, 13:03:05

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

Re: json ->> operator precedence

From

John McKown

Date:

05 August 2015, 16:35:46

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 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?

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

Re: json ->> operator precedence

From

Geoff Winkless

Date:

05 August 2015, 16:43:16

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

Re: json ->> operator precedence

From

John McKown

Date:

05 August 2015, 16:45:17

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

Re: json ->> operator precedence

From

John McKown

Date:

05 August 2015, 16:47:45

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

Re: json ->> operator precedence

From

Tom Lane

Date:

05 August 2015, 16:52:31

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

Re: json ->> operator precedence

From

Geoff Winkless

Date:

05 August 2015, 16:54:47

On 5 August 2015 at 14:52, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

Doh. Sorry, I'm an idiot.