-- 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;