agora inbox for pgsql-committers@postgresql.org
help / color / mirror / Atom feedpgsql: Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expression
4+ messages / 1 participants
[nested] [flat]
* pgsql: Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expression
@ 2026-07-06 23:45 Amit Langote <amitlan@postgresql.org>
0 siblings, 0 replies; 4+ messages in thread
From: Amit Langote @ 2026-07-06 23:45 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expressions
transformJsonBehavior() coerced an ON EMPTY / ON ERROR DEFAULT
expression only when its type differed from the RETURNING type's OID.
When the base type matched but the RETURNING type carried a type
modifier (e.g. numeric(4,1) or varchar(3)), the coercion that enforces
the typmod was skipped, so the DEFAULT value could violate the
declared type:
SELECT JSON_VALUE(jsonb '{}', '$.a'
RETURNING numeric(4,1) DEFAULT 99999.999 ON EMPTY);
returned 99999.999, which 99999.999::numeric(4,1) would reject; the
value could even be stored into a numeric(4,1) column, as later
coercions trust its already-correct type label.
Fix by also coercing when the RETURNING type has a typmod, except for
a NULL constant. coerce_to_target_type() is a no-op when the typmod
already matches. The matching-OID short-circuit dates to 74c96699be3.
Reported-by: Ewan Young <kdbase.hack@gmail.com>
Author: Ewan Young <kdbase.hack@gmail.com>
Discussion: https://postgr.es/m/CAON2xHPO9f4cAmyGn1mQ=VqoS7wN5rz4yOiqudxX78zninZpCw@mail.gmail.com
Backpatch-through: 17
Branch
------
REL_17_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/71cd10cd249baae25bb420ea4a6d5eae9fe1dd5f
Modified Files
--------------
src/backend/parser/parse_expr.c | 11 ++++++++++-
src/test/regress/expected/sqljson_jsontable.out | 16 ++++++++++++++++
src/test/regress/expected/sqljson_queryfuncs.out | 21 +++++++++++++++++++++
src/test/regress/sql/sqljson_jsontable.sql | 9 +++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 8 ++++++++
5 files changed, 64 insertions(+), 1 deletion(-)
^ permalink raw reply [nested|flat] 4+ messages in thread
* pgsql: Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expression
@ 2026-07-06 23:46 Amit Langote <amitlan@postgresql.org>
0 siblings, 0 replies; 4+ messages in thread
From: Amit Langote @ 2026-07-06 23:46 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expressions
transformJsonBehavior() coerced an ON EMPTY / ON ERROR DEFAULT
expression only when its type differed from the RETURNING type's OID.
When the base type matched but the RETURNING type carried a type
modifier (e.g. numeric(4,1) or varchar(3)), the coercion that enforces
the typmod was skipped, so the DEFAULT value could violate the
declared type:
SELECT JSON_VALUE(jsonb '{}', '$.a'
RETURNING numeric(4,1) DEFAULT 99999.999 ON EMPTY);
returned 99999.999, which 99999.999::numeric(4,1) would reject; the
value could even be stored into a numeric(4,1) column, as later
coercions trust its already-correct type label.
Fix by also coercing when the RETURNING type has a typmod, except for
a NULL constant. coerce_to_target_type() is a no-op when the typmod
already matches. The matching-OID short-circuit dates to 74c96699be3.
Reported-by: Ewan Young <kdbase.hack@gmail.com>
Author: Ewan Young <kdbase.hack@gmail.com>
Discussion: https://postgr.es/m/CAON2xHPO9f4cAmyGn1mQ=VqoS7wN5rz4yOiqudxX78zninZpCw@mail.gmail.com
Backpatch-through: 17
Branch
------
REL_18_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/441e4c8d699910ea21f7f31ae7e76dbc1e572e05
Modified Files
--------------
src/backend/parser/parse_expr.c | 11 ++++++++++-
src/test/regress/expected/sqljson_jsontable.out | 16 ++++++++++++++++
src/test/regress/expected/sqljson_queryfuncs.out | 21 +++++++++++++++++++++
src/test/regress/sql/sqljson_jsontable.sql | 9 +++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 8 ++++++++
5 files changed, 64 insertions(+), 1 deletion(-)
^ permalink raw reply [nested|flat] 4+ messages in thread
* pgsql: Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expression
@ 2026-07-06 23:46 Amit Langote <amitlan@postgresql.org>
0 siblings, 0 replies; 4+ messages in thread
From: Amit Langote @ 2026-07-06 23:46 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expressions
transformJsonBehavior() coerced an ON EMPTY / ON ERROR DEFAULT
expression only when its type differed from the RETURNING type's OID.
When the base type matched but the RETURNING type carried a type
modifier (e.g. numeric(4,1) or varchar(3)), the coercion that enforces
the typmod was skipped, so the DEFAULT value could violate the
declared type:
SELECT JSON_VALUE(jsonb '{}', '$.a'
RETURNING numeric(4,1) DEFAULT 99999.999 ON EMPTY);
returned 99999.999, which 99999.999::numeric(4,1) would reject; the
value could even be stored into a numeric(4,1) column, as later
coercions trust its already-correct type label.
Fix by also coercing when the RETURNING type has a typmod, except for
a NULL constant. coerce_to_target_type() is a no-op when the typmod
already matches. The matching-OID short-circuit dates to 74c96699be3.
Reported-by: Ewan Young <kdbase.hack@gmail.com>
Author: Ewan Young <kdbase.hack@gmail.com>
Discussion: https://postgr.es/m/CAON2xHPO9f4cAmyGn1mQ=VqoS7wN5rz4yOiqudxX78zninZpCw@mail.gmail.com
Backpatch-through: 17
Branch
------
REL_19_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/d30bfcbddca3b08f66d1207265d9ab7d8a7b95c1
Modified Files
--------------
src/backend/parser/parse_expr.c | 11 ++++++++++-
src/test/regress/expected/sqljson_jsontable.out | 16 ++++++++++++++++
src/test/regress/expected/sqljson_queryfuncs.out | 21 +++++++++++++++++++++
src/test/regress/sql/sqljson_jsontable.sql | 9 +++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 8 ++++++++
5 files changed, 64 insertions(+), 1 deletion(-)
^ permalink raw reply [nested|flat] 4+ messages in thread
* pgsql: Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expression
@ 2026-07-06 23:46 Amit Langote <amitlan@postgresql.org>
0 siblings, 0 replies; 4+ messages in thread
From: Amit Langote @ 2026-07-06 23:46 UTC (permalink / raw)
To: pgsql-committers@lists.postgresql.org
Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expressions
transformJsonBehavior() coerced an ON EMPTY / ON ERROR DEFAULT
expression only when its type differed from the RETURNING type's OID.
When the base type matched but the RETURNING type carried a type
modifier (e.g. numeric(4,1) or varchar(3)), the coercion that enforces
the typmod was skipped, so the DEFAULT value could violate the
declared type:
SELECT JSON_VALUE(jsonb '{}', '$.a'
RETURNING numeric(4,1) DEFAULT 99999.999 ON EMPTY);
returned 99999.999, which 99999.999::numeric(4,1) would reject; the
value could even be stored into a numeric(4,1) column, as later
coercions trust its already-correct type label.
Fix by also coercing when the RETURNING type has a typmod, except for
a NULL constant. coerce_to_target_type() is a no-op when the typmod
already matches. The matching-OID short-circuit dates to 74c96699be3.
Reported-by: Ewan Young <kdbase.hack@gmail.com>
Author: Ewan Young <kdbase.hack@gmail.com>
Discussion: https://postgr.es/m/CAON2xHPO9f4cAmyGn1mQ=VqoS7wN5rz4yOiqudxX78zninZpCw@mail.gmail.com
Backpatch-through: 17
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/4c75cc786301886145bc1a450977cbd024814ef5
Modified Files
--------------
src/backend/parser/parse_expr.c | 11 ++++++++++-
src/test/regress/expected/sqljson_jsontable.out | 16 ++++++++++++++++
src/test/regress/expected/sqljson_queryfuncs.out | 21 +++++++++++++++++++++
src/test/regress/sql/sqljson_jsontable.sql | 9 +++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 8 ++++++++
5 files changed, 64 insertions(+), 1 deletion(-)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-07-06 23:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-07-06 23:45 pgsql: Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expression Amit Langote <amitlan@postgresql.org>
2026-07-06 23:46 pgsql: Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expression Amit Langote <amitlan@postgresql.org>
2026-07-06 23:46 pgsql: Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expression Amit Langote <amitlan@postgresql.org>
2026-07-06 23:46 pgsql: Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expression Amit Langote <amitlan@postgresql.org>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox