Type resolution compatibility issue

[markddrake]

Jira Link: DB-2890

Description

The following works in Postgres 14

select array_agg(point(p[0]::double precision,p[1]::double precision)) points
  from jsonb_array_elements(jsonb_extract_path('{"type": "Polygon", "coordinates": [[[0, 0], [1, 3], [2, 0], [0, 0]]]}'::jsonb,'coordinates','0')) p

C:\Development\YADAMU>psql -Upostgres -hyadamu-db2 -dyadamu
Password for user postgres:
psql (13.3, server 14.4 (Debian 14.4-1.pgdg110+1))
WARNING: psql major version 13, server major version 14.
         Some psql features might not work.
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

yadamu=# select array_agg(point(p[0]::double precision,p[1]::double precision)) points
yadamu-#   from jsonb_array_elements(jsonb_extract_path('{"type": "Polygon", "coordinates": [[[0, 0], [1, 3], [2, 0], [0, 0]]]}'::jsonb,'coordinates','0')) p
yadamu-# ;
              points
-----------------------------------
 {"(0,0)","(1,3)","(2,0)","(0,0)"}
(1 row)

yadamu=# quit

But fails in Yugabyte

C:\Development\YADAMU>psql -Upostgres -hyadamu-db2 -dyadamu  -p5533
psql (13.3, server 11.2-YB-2.13.1.0-b0)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

yadamu=# select array_agg(point(p[0]::double precision,p[1]::double precision)) points
yadamu-#   from jsonb_array_elements(jsonb_extract_path('{"type": "Polygon", "coordinates": [[[0, 0], [1, 3], [2, 0], [0, 0]]]}'::jsonb,'coordinates','0')) p
yadamu-# ;
ERROR:  cannot subscript type jsonb because it is not an array
yadamu=#

[markddrake]

The following works in both

select array_agg(point((p ->> 0)::double precision,(p ->> 1)::double precision)) points
  from jsonb_array_elements(jsonb_extract_path('{"type": "Polygon", "coordinates": [[[0, 0], [1, 3], [2, 0], [0, 0]]]}'::jsonb,'coordinates','0')) p

[tedyu]

The query doesn't work in PG 13:

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=f4999bde8230dbbc737aebd8f898e6ff

This is because jsonb access using array notation came in PG 14.

[markddrake]

Thanks, I just set up a generic 11.2 Postgres container, so I'll catch these in future.