hi.
in transformJsonFuncExpr:
path_spec = transformExprRecurse(pstate, func->pathspec);
path_spec = coerce_to_target_type(pstate, path_spec, exprType(path_spec),
JSONPATHOID, -1,
COERCION_EXPLICIT, COERCE_IMPLICIT_CAST,
exprLocation(path_spec));
if (path_spec == NULL)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("JSON path expression must be of type %s, not
of type %s",
"jsonpath", format_type_be(exprType(path_spec))),
parser_errposition(pstate, exprLocation(path_spec))));
There is no test for this, if you try it, you can easily reach "cache
lookup failed".
SELECT JSON_VALUE(jsonb 'null', NULL::date);
ERROR: cache lookup failed for type 0
because we first call ``format_type_be(exprType(path_spec))),`` then ereport.
format_type_be can not code with InvalidOid.
A patch is attached.
-----------
Also, note that we allow:
SELECT * FROM JSON_TABLE(jsonb'"1.23"', '$' COLUMNS (js2 int PATH '$'));
but don't allow
SELECT * FROM JSON_TABLE(jsonb'"1.23"', '$'::jsonpath COLUMNS (js2 int
PATH '$'));
Maybe we should support this.
since every A_Const should have a type for it. Allowing something like:
JSON_TABLE(jsonb '"1.23"', '$'::some_jsonpath_type ... )
seems consistent.
I guess that's a separate issue, so I didn't touch it.
--
jian
https://www.enterprisedb.com/
From c3aa2acd0cbbb0af05530d5076d4dd55b3e2e31f Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Fri, 7 Nov 2025 10:48:27 +0800
Subject: [PATCH v1 1/1] fix transformJsonFuncExpr pathspec cache lookup
failure
discussion: https://postgr.es/m/
---
src/backend/parser/parse_expr.c | 13 +++++++++----
src/test/regress/expected/sqljson_queryfuncs.out | 8 ++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 2 ++
3 files changed, 19 insertions(+), 4 deletions(-)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 12119f147fc..bdee31eb22f 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4287,6 +4287,8 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
Node *path_spec;
const char *func_name = NULL;
JsonFormatType default_format;
+ Oid pathspec_type;
+ ParseLoc pathspec_loc;
switch (func->op)
{
@@ -4500,16 +4502,19 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
jsexpr->format = func->context_item->format;
path_spec = transformExprRecurse(pstate, func->pathspec);
- path_spec = coerce_to_target_type(pstate, path_spec, exprType(path_spec),
+ pathspec_type = exprType(path_spec);
+ pathspec_loc = exprLocation(path_spec);
+
+ path_spec = coerce_to_target_type(pstate, path_spec, pathspec_type,
JSONPATHOID, -1,
COERCION_EXPLICIT, COERCE_IMPLICIT_CAST,
- exprLocation(path_spec));
+ pathspec_loc);
if (path_spec == NULL)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("JSON path expression must be of type %s, not of type %s",
- "jsonpath", format_type_be(exprType(path_spec))),
- parser_errposition(pstate, exprLocation(path_spec))));
+ "jsonpath", format_type_be(pathspec_type)),
+ parser_errposition(pstate, pathspec_loc)));
jsexpr->path_spec = path_spec;
/* Transform and coerce the PASSING arguments to jsonb. */
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index 5a35aeb7bba..53145f50f18 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -1331,6 +1331,10 @@ SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER);
[123]
(1 row)
+SELECT JSON_QUERY(jsonb '{"a": 123}', ('$' || '.' || 'a' || NULL)::date WITH WRAPPER);
+ERROR: JSON path expression must be of type jsonpath, not of type date
+LINE 1: SELECT JSON_QUERY(jsonb '{"a": 123}', ('$' || '.' || 'a' || ...
+ ^
-- Should fail (invalid path)
SELECT JSON_QUERY(jsonb '{"a": 123}', 'error' || ' ' || 'error');
ERROR: syntax error at or near " " of jsonpath input
@@ -1355,6 +1359,10 @@ SELECT json_value('"aaa"', path RETURNING json) FROM jsonpaths;
"aaa"
(1 row)
+SELECT json_value('"aaa"', jsonpaths RETURNING json) FROM jsonpaths;
+ERROR: JSON path expression must be of type jsonpath, not of type jsonpaths
+LINE 1: SELECT json_value('"aaa"', jsonpaths RETURNING json) FROM js...
+ ^
-- Test PASSING argument parsing
SELECT JSON_QUERY(jsonb 'null', '$xyz' PASSING 1 AS xy);
ERROR: could not find jsonpath variable "xyz"
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index 8d7b225b612..a5d5e256d7f 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -450,6 +450,7 @@ SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'a');
SELECT JSON_VALUE(jsonb '{"a": 123}', '$' || '.' || 'b' DEFAULT 'foo' ON EMPTY);
SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a');
SELECT JSON_QUERY(jsonb '{"a": 123}', '$' || '.' || 'a' WITH WRAPPER);
+SELECT JSON_QUERY(jsonb '{"a": 123}', ('$' || '.' || 'a' || NULL)::date WITH WRAPPER);
-- Should fail (invalid path)
SELECT JSON_QUERY(jsonb '{"a": 123}', 'error' || ' ' || 'error');
@@ -460,6 +461,7 @@ SELECT JSON_QUERY(NULL FORMAT JSON, '$');
-- Test non-const jsonpath
CREATE TEMP TABLE jsonpaths (path) AS SELECT '$';
SELECT json_value('"aaa"', path RETURNING json) FROM jsonpaths;
+SELECT json_value('"aaa"', jsonpaths RETURNING json) FROM jsonpaths;
-- Test PASSING argument parsing
SELECT JSON_QUERY(jsonb 'null', '$xyz' PASSING 1 AS xy);
--
2.34.1
