Op 9/18/23 om 12:20 schreef Amit Langote:
Hi Erik,
I am sorry to be bothering you with these somewhat idiotic SQL
statements but I suppose somehow it needs to be made more solid.
For 60 datatypes, I ran this statement:
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning ${datatype} with wrapper
);
against a 17devel server (a0a5) with json v15 patches and caught the
output, incl. 30+ crashes, in the attached .txt. I hope that's useful.
Erik
--------------------------------------------------------------- [int4 START ] --
-- datatype [int4]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning int4 with wrapper
);
ERROR: cannot cast jsonb array to type integer
--------------------------------------------------------------- [int4 END ] --
--------------------------------------------------------------- [bit START ] --
-- datatype [bit]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning bit with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [bit END ] --
--------------------------------------------------------------- [bigint START ]
--
-- datatype [bigint]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning bigint with wrapper
);
ERROR: cannot cast jsonb array to type bigint
--------------------------------------------------------------- [bigint END ] --
--------------------------------------------------------------- [bit varying
START ] --
-- datatype [bit varying]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning bit varying with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [bit varying
END ] --
--------------------------------------------------------------- [boolean START
] --
-- datatype [boolean]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning boolean with wrapper
);
ERROR: cannot cast jsonb array to type boolean
--------------------------------------------------------------- [boolean END ]
--
--------------------------------------------------------------- [box START ] --
-- datatype [box]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning box with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [box END ] --
--------------------------------------------------------------- [bytea START ]
--
-- datatype [bytea]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning bytea with wrapper
);
json_query
----------------
\x5b322c20335d
(1 row)
--------------------------------------------------------------- [bytea END ] --
--------------------------------------------------------------- [character
varying(5) START ] --
-- datatype [character varying(5)]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning character varying(5) with wrapper
);
json_query
------------
[2, 3
(1 row)
--------------------------------------------------------------- [character
varying(5) END ] --
--------------------------------------------------------------- [cidr START ] --
-- datatype [cidr]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning cidr with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [cidr END ] --
--------------------------------------------------------------- [circle START ]
--
-- datatype [circle]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning circle with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [circle END ] --
--------------------------------------------------------------- [date START ] --
-- datatype [date]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning date with wrapper
);
json_query
------------
2000-01-01
(1 row)
--------------------------------------------------------------- [date END ] --
--------------------------------------------------------------- [double
precision START ] --
-- datatype [double precision]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning double precision with wrapper
);
ERROR: cannot cast jsonb array to type double precision
--------------------------------------------------------------- [double
precision END ] --
--------------------------------------------------------------- [float START ]
--
-- datatype [float]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning float with wrapper
);
ERROR: cannot cast jsonb array to type double precision
--------------------------------------------------------------- [float END ] --
--------------------------------------------------------------- [inet START ] --
-- datatype [inet]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning inet with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [inet END ] --
--------------------------------------------------------------- [interval START
] --
-- datatype [interval]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning interval with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [interval END ]
--
--------------------------------------------------------------- [json START ] --
-- datatype [json]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning json with wrapper
);
json_query
------------
[2, 3]
(1 row)
--------------------------------------------------------------- [json END ] --
--------------------------------------------------------------- [jsonb START ]
--
-- datatype [jsonb]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning jsonb with wrapper
);
json_query
------------
[2, 3]
(1 row)
--------------------------------------------------------------- [jsonb END ] --
--------------------------------------------------------------- [line START ] --
-- datatype [line]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning line with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [line END ] --
--------------------------------------------------------------- [lseg START ] --
-- datatype [lseg]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning lseg with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [lseg END ] --
--------------------------------------------------------------- [macaddr START
] --
-- datatype [macaddr]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning macaddr with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [macaddr END ]
--
--------------------------------------------------------------- [macaddr8 START
] --
-- datatype [macaddr8]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning macaddr8 with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [macaddr8 END ]
--
--------------------------------------------------------------- [money START ]
--
-- datatype [money]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning money with wrapper
);
json_query
------------
$0.00
(1 row)
--------------------------------------------------------------- [money END ] --
--------------------------------------------------------------- [numeric START
] --
-- datatype [numeric]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning numeric with wrapper
);
ERROR: cannot cast jsonb array to type numeric
--------------------------------------------------------------- [numeric END ]
--
--------------------------------------------------------------- [path START ] --
-- datatype [path]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning path with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [path END ] --
--------------------------------------------------------------- [pg_lsn START ]
--
-- datatype [pg_lsn]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning pg_lsn with wrapper
);
json_query
------------
0/0
(1 row)
--------------------------------------------------------------- [pg_lsn END ] --
--------------------------------------------------------------- [point START ]
--
-- datatype [point]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning point with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [point END ] --
--------------------------------------------------------------- [polygon START
] --
-- datatype [polygon]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning polygon with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [polygon END ]
--
--------------------------------------------------------------- [real START ] --
-- datatype [real]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning real with wrapper
);
ERROR: cannot cast jsonb array to type real
--------------------------------------------------------------- [real END ] --
--------------------------------------------------------------- [smallint START
] --
-- datatype [smallint]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning smallint with wrapper
);
ERROR: cannot cast jsonb array to type smallint
--------------------------------------------------------------- [smallint END ]
--
--------------------------------------------------------------- [serial START ]
--
-- datatype [serial]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning serial with wrapper
);
ERROR: type "serial" does not exist
LINE 2: '$.a[*].a?(@<=3)'returning serial with wrapper
^
--------------------------------------------------------------- [serial END ] --
--------------------------------------------------------------- [text START ] --
-- datatype [text]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning text with wrapper
);
json_query
------------
[2, 3]
(1 row)
--------------------------------------------------------------- [text END ] --
--------------------------------------------------------------- [time START ] --
-- datatype [time]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning time with wrapper
);
json_query
------------
00:00:00
(1 row)
--------------------------------------------------------------- [time END ] --
--------------------------------------------------------------- [time without
time zone START ] --
-- datatype [time without time zone]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning time without time zone with wrapper
);
json_query
------------
00:00:00
(1 row)
--------------------------------------------------------------- [time without
time zone END ] --
--------------------------------------------------------------- [time with time
zone START ] --
-- datatype [time with time zone]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning time with time zone with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [time with time
zone END ] --
--------------------------------------------------------------- [timestamp
START ] --
-- datatype [timestamp]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning timestamp with wrapper
);
json_query
---------------------
2000-01-01 00:00:00
(1 row)
--------------------------------------------------------------- [timestamp END
] --
--------------------------------------------------------------- [timestamp
without time zone START ] --
-- datatype [timestamp without time zone]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning timestamp without time zone with wrapper
);
json_query
---------------------
2000-01-01 00:00:00
(1 row)
--------------------------------------------------------------- [timestamp
without time zone END ] --
--------------------------------------------------------------- [timestamp with
time zone START ] --
-- datatype [timestamp with time zone]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning timestamp with time zone with wrapper
);
json_query
------------------------
2000-01-01 01:00:00+01
(1 row)
--------------------------------------------------------------- [timestamp with
time zone END ] --
--------------------------------------------------------------- [tsquery START
] --
-- datatype [tsquery]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning tsquery with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [tsquery END ]
--
--------------------------------------------------------------- [tsvector START
] --
-- datatype [tsvector]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning tsvector with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [tsvector END ]
--
--------------------------------------------------------------- [uuid START ] --
-- datatype [uuid]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning uuid with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [uuid END ] --
--------------------------------------------------------------- [txid_snapshot
START ] --
-- datatype [txid_snapshot]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning txid_snapshot with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [txid_snapshot
END ] --
--------------------------------------------------------------- [xml START ] --
-- datatype [xml]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning xml with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [xml END ] --
--------------------------------------------------------------- [decimal START
] --
-- datatype [decimal]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning decimal with wrapper
);
ERROR: cannot cast jsonb array to type numeric
--------------------------------------------------------------- [decimal END ]
--
--------------------------------------------------------------- [char(4) START
] --
-- datatype [char(4)]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning char(4) with wrapper
);
json_query
------------
[2,
(1 row)
--------------------------------------------------------------- [char(4) END ]
--
--------------------------------------------------------------- [character(4)
START ] --
-- datatype [character(4)]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning character(4) with wrapper
);
json_query
------------
[2,
(1 row)
--------------------------------------------------------------- [character(4)
END ] --
--------------------------------------------------------------- [bit(3) START ]
--
-- datatype [bit(3)]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning bit(3) with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [bit(3) END ] --
--------------------------------------------------------------- [int4range
START ] --
-- datatype [int4range]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning int4range with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [int4range END
] --
--------------------------------------------------------------- [int4multirange
START ] --
-- datatype [int4multirange]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning int4multirange with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [int4multirange
END ] --
--------------------------------------------------------------- [int8range
START ] --
-- datatype [int8range]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning int8range with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [int8range END
] --
--------------------------------------------------------------- [int8multirange
START ] --
-- datatype [int8multirange]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning int8multirange with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [int8multirange
END ] --
--------------------------------------------------------------- [numrange START
] --
-- datatype [numrange]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning numrange with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [numrange END ]
--
--------------------------------------------------------------- [nummultirange
START ] --
-- datatype [nummultirange]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning nummultirange with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [nummultirange
END ] --
--------------------------------------------------------------- [tsrange START
] --
-- datatype [tsrange]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning tsrange with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [tsrange END ]
--
--------------------------------------------------------------- [tsmultirange
START ] --
-- datatype [tsmultirange]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning tsmultirange with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [tsmultirange
END ] --
--------------------------------------------------------------- [tstzrange
START ] --
-- datatype [tstzrange]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning tstzrange with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [tstzrange END
] --
--------------------------------------------------------------- [tstzmultirange
START ] --
-- datatype [tstzmultirange]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning tstzmultirange with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [tstzmultirange
END ] --
--------------------------------------------------------------- [daterange
START ] --
-- datatype [daterange]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning daterange with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [daterange END
] --
--------------------------------------------------------------- [datemultirange
START ] --
-- datatype [datemultirange]
select json_query(jsonb'{"a":[{"a":[2,3]},{"a":[4,5]}]}',
'$.a[*].a?(@<=3)'returning datemultirange with wrapper
);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
--------------------------------------------------------------- [datemultirange
END ] --
