pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedFrom: Manu <manuelreyesbravo@gmail.com>
To: jian he <jian.universality@gmail.com>
Cc: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Cc: zengman <zengman@halodbtech.com>
Cc: syzhong16 <syzhong16@gmail.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Cc: Amit Langote <amitlangote09@gmail.com>
Subject: Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY
Date: Sat, 26 Sep 2026 15:14:04 -0300
Message-ID: <179044644489.113031.11832692369228086606@gmail.com> (raw)
In-Reply-To: <CACJufxFL-xzeD_GVVqbd1Jeab8unkbibM_=1cLBUASyi2bDx=g@mail.gmail.com>
References: <CACJufxFL-xzeD_GVVqbd1Jeab8unkbibM_=1cLBUASyi2bDx=g@mail.gmail.com>
Hi jian,
> This seems unnecessary.
> In EEOP_JSONEXPR_PATH, we can
> if document or jsonpath is NULL, we can just go to jump_end (return
> NULL) or jump_eval_coercion (NULL need coerce to constrainted domain),
> no need to worry about ON ERROR, ON EMPTY.
That also takes care of the back-branch concern I raised on v2: with
no new opcode, v3 does not change any header, so the ExprEvalOp values
stay as they are.
v3 applies cleanly to master, REL_18_STABLE and REL_17_STABLE, builds
without warnings, and the main regression suite passes on all three.
Since the bug is state leaking from one row to the next, I also
checked it with a differential test (attached): every row of a
two-row query must give the same result as that row evaluated alone.
It covers json_value (RETURNING int, a NOT NULL domain and a CHECK
domain), json_query and json_exists, with every ON EMPTY / ON ERROR
combination, over every ordered pair of rows built from five
documents ('{}', '{"a":1}', '{"a":"x"}', '{"a":[1,2]}', NULL) and
three paths ('$.a', 'strict $.a', NULL). That is 9675 cases:
- unpatched master (09a579abaca): 567 mismatches (497 json_value,
56 json_query, 14 json_exists)
- v3 on master, REL_18_STABLE and REL_17_STABLE: 0
The single-row answers do not change: all 645 of them are the same
with and without v3, including NULL input into the NOT NULL domain.
With Srinath's fix for #19695 on top, its case is right too
(1, NULL, 2). As before, I could not exercise JIT here.
Regards,
Manu
-- Differential check for JsonExpr state leaking from one row to the next
-- (#19621, #19654). Oracle: in a multi-row query, the result for each row
-- must be the one that same row gives when evaluated alone. If any row
-- errors when alone, the multi-row query must fail with the first such
-- error (VALUES is scanned in order).
--
-- Matrix: json_value (RETURNING int, a NOT NULL domain, a CHECK domain) x
-- ON EMPTY x ON ERROR, json_query x ON EMPTY x ON ERROR, json_exists x
-- ON ERROR; over every ordered pair of rows, where a row is a document
-- ('{}', '{"a":1}', '{"a":"x"}', '{"a":[1,2]}', NULL) and a path
-- ('$.a', 'strict $.a', NULL).
\set QUIET on
SET client_min_messages = warning;
DROP DOMAIN IF EXISTS o_nn, o_pos CASCADE;
CREATE DOMAIN o_nn AS int NOT NULL;
CREATE DOMAIN o_pos AS int CHECK (VALUE > 0);
CREATE TEMP TABLE o_item (i int, d text, p text);
INSERT INTO o_item
SELECT row_number() OVER (), d, p
FROM unnest(ARRAY['''{}''', '''{"a":1}''', '''{"a":"x"}''', '''{"a":[1,2]}''', 'NULL']) d,
unnest(ARRAY['''$.a''', '''strict $.a''', 'NULL']) p;
CREATE TEMP TABLE o_expr (e text);
INSERT INTO o_expr
SELECT format('json_value(x, p RETURNING %s %s ON EMPTY %s ON ERROR)', t, oe, oer)
FROM unnest(ARRAY['int', 'o_nn', 'o_pos']) t,
unnest(ARRAY['NULL', 'DEFAULT 42', 'ERROR']) oe,
unnest(ARRAY['NULL', 'DEFAULT 7', 'ERROR']) oer
UNION ALL
SELECT format('json_query(x, p %s ON EMPTY %s ON ERROR)', oe, oer)
FROM unnest(ARRAY['NULL', 'EMPTY ARRAY', 'DEFAULT ''"d"''', 'ERROR']) oe,
unnest(ARRAY['NULL', 'EMPTY OBJECT', 'ERROR']) oer
UNION ALL
SELECT format('json_exists(x, p %s ON ERROR)', oer)
FROM unnest(ARRAY['FALSE', 'TRUE', 'UNKNOWN', 'ERROR']) oer;
CREATE TEMP TABLE o_result (e text, rows text, alone text, together text);
DO $$
DECLARE
ex record; a record; b record;
r1 text; r2 text; together text; alone text;
q text;
BEGIN
FOR ex IN SELECT e FROM o_expr LOOP
FOR a IN SELECT * FROM o_item LOOP
FOR b IN SELECT * FROM o_item LOOP
-- each row alone
BEGIN
EXECUTE format('SELECT coalesce((%s)::text, ''NULL'') FROM (VALUES (%s::jsonb, %s::jsonpath)) v(x, p)',
ex.e, a.d, a.p) INTO r1;
EXCEPTION WHEN OTHERS THEN r1 := 'ERROR: ' || SQLERRM;
END;
BEGIN
EXECUTE format('SELECT coalesce((%s)::text, ''NULL'') FROM (VALUES (%s::jsonb, %s::jsonpath)) v(x, p)',
ex.e, b.d, b.p) INTO r2;
EXCEPTION WHEN OTHERS THEN r2 := 'ERROR: ' || SQLERRM;
END;
alone := CASE WHEN r1 LIKE 'ERROR:%' THEN r1
WHEN r2 LIKE 'ERROR:%' THEN r2
ELSE r1 || ' | ' || r2 END;
-- both rows in one query, in order
q := format('SELECT string_agg(coalesce((%s)::text, ''NULL''), '' | '' ORDER BY o)
FROM (VALUES (1, %s::jsonb, %s::jsonpath), (2, %s::jsonb, %s::jsonpath)) v(o, x, p)',
ex.e, a.d, a.p, b.d, b.p);
BEGIN
EXECUTE q INTO together;
EXCEPTION WHEN OTHERS THEN together := 'ERROR: ' || SQLERRM;
END;
INSERT INTO o_result VALUES (ex.e, a.d || ',' || a.p || ' then ' || b.d || ',' || b.p, alone, together);
END LOOP;
END LOOP;
END LOOP;
END $$;
SELECT 'cases=' || count(*) || ' mismatches=' || count(*) FILTER (WHERE alone IS DISTINCT FROM together)
FROM o_result;
SELECT split_part(e, '(', 1) AS func, count(*) FILTER (WHERE alone IS DISTINCT FROM together) AS mismatches, count(*) AS cases
FROM o_result GROUP BY 1 ORDER BY 1;
SELECT e, rows, alone, together
FROM o_result WHERE alone IS DISTINCT FROM together
ORDER BY e, rows LIMIT 8;
Attachments:
[text/plain] rows_oracle.sql.txt (3.6K, ../179044644489.113031.11832692369228086606@gmail.com/2-rows_oracle.sql.txt)
download | inline:
-- Differential check for JsonExpr state leaking from one row to the next
-- (#19621, #19654). Oracle: in a multi-row query, the result for each row
-- must be the one that same row gives when evaluated alone. If any row
-- errors when alone, the multi-row query must fail with the first such
-- error (VALUES is scanned in order).
--
-- Matrix: json_value (RETURNING int, a NOT NULL domain, a CHECK domain) x
-- ON EMPTY x ON ERROR, json_query x ON EMPTY x ON ERROR, json_exists x
-- ON ERROR; over every ordered pair of rows, where a row is a document
-- ('{}', '{"a":1}', '{"a":"x"}', '{"a":[1,2]}', NULL) and a path
-- ('$.a', 'strict $.a', NULL).
\set QUIET on
SET client_min_messages = warning;
DROP DOMAIN IF EXISTS o_nn, o_pos CASCADE;
CREATE DOMAIN o_nn AS int NOT NULL;
CREATE DOMAIN o_pos AS int CHECK (VALUE > 0);
CREATE TEMP TABLE o_item (i int, d text, p text);
INSERT INTO o_item
SELECT row_number() OVER (), d, p
FROM unnest(ARRAY['''{}''', '''{"a":1}''', '''{"a":"x"}''', '''{"a":[1,2]}''', 'NULL']) d,
unnest(ARRAY['''$.a''', '''strict $.a''', 'NULL']) p;
CREATE TEMP TABLE o_expr (e text);
INSERT INTO o_expr
SELECT format('json_value(x, p RETURNING %s %s ON EMPTY %s ON ERROR)', t, oe, oer)
FROM unnest(ARRAY['int', 'o_nn', 'o_pos']) t,
unnest(ARRAY['NULL', 'DEFAULT 42', 'ERROR']) oe,
unnest(ARRAY['NULL', 'DEFAULT 7', 'ERROR']) oer
UNION ALL
SELECT format('json_query(x, p %s ON EMPTY %s ON ERROR)', oe, oer)
FROM unnest(ARRAY['NULL', 'EMPTY ARRAY', 'DEFAULT ''"d"''', 'ERROR']) oe,
unnest(ARRAY['NULL', 'EMPTY OBJECT', 'ERROR']) oer
UNION ALL
SELECT format('json_exists(x, p %s ON ERROR)', oer)
FROM unnest(ARRAY['FALSE', 'TRUE', 'UNKNOWN', 'ERROR']) oer;
CREATE TEMP TABLE o_result (e text, rows text, alone text, together text);
DO $$
DECLARE
ex record; a record; b record;
r1 text; r2 text; together text; alone text;
q text;
BEGIN
FOR ex IN SELECT e FROM o_expr LOOP
FOR a IN SELECT * FROM o_item LOOP
FOR b IN SELECT * FROM o_item LOOP
-- each row alone
BEGIN
EXECUTE format('SELECT coalesce((%s)::text, ''NULL'') FROM (VALUES (%s::jsonb, %s::jsonpath)) v(x, p)',
ex.e, a.d, a.p) INTO r1;
EXCEPTION WHEN OTHERS THEN r1 := 'ERROR: ' || SQLERRM;
END;
BEGIN
EXECUTE format('SELECT coalesce((%s)::text, ''NULL'') FROM (VALUES (%s::jsonb, %s::jsonpath)) v(x, p)',
ex.e, b.d, b.p) INTO r2;
EXCEPTION WHEN OTHERS THEN r2 := 'ERROR: ' || SQLERRM;
END;
alone := CASE WHEN r1 LIKE 'ERROR:%' THEN r1
WHEN r2 LIKE 'ERROR:%' THEN r2
ELSE r1 || ' | ' || r2 END;
-- both rows in one query, in order
q := format('SELECT string_agg(coalesce((%s)::text, ''NULL''), '' | '' ORDER BY o)
FROM (VALUES (1, %s::jsonb, %s::jsonpath), (2, %s::jsonb, %s::jsonpath)) v(o, x, p)',
ex.e, a.d, a.p, b.d, b.p);
BEGIN
EXECUTE q INTO together;
EXCEPTION WHEN OTHERS THEN together := 'ERROR: ' || SQLERRM;
END;
INSERT INTO o_result VALUES (ex.e, a.d || ',' || a.p || ' then ' || b.d || ',' || b.p, alone, together);
END LOOP;
END LOOP;
END LOOP;
END $$;
SELECT 'cases=' || count(*) || ' mismatches=' || count(*) FILTER (WHERE alone IS DISTINCT FROM together)
FROM o_result;
SELECT split_part(e, '(', 1) AS func, count(*) FILTER (WHERE alone IS DISTINCT FROM together) AS mismatches, count(*) AS cases
FROM o_result GROUP BY 1 ORDER BY 1;
SELECT e, rows, alone, together
FROM o_result WHERE alone IS DISTINCT FROM together
ORDER BY e, rows LIMIT 8;
view thread (11+ messages) latest in thread
Message-ID: <179044644489.113031.11832692369228086606@gmail.com>
Permalink: ../179044644489.113031.11832692369228086606@gmail.com/
Also on: postgresql.org/message-id/179044644489.113031.11832692369228086606@gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: manuelreyesbravo@gmail.com, jian.universality@gmail.com, pl0h0yp1@gmail.com, zengman@halodbtech.com, syzhong16@gmail.com, pgsql-bugs@lists.postgresql.org, amitlangote09@gmail.com
Subject: Re: BUG #19621: Unexpected results of JSON_VALUE with DEFAULT ON EMPTY
In-Reply-To: <179044644489.113031.11832692369228086606@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox