-- A typed leaf of a path (`doc.v float8`) cannot store a value of another JSON type (a numeric string, a boolean, a container). A scan that reads
-- the path as `(doc ->> 'v')::T` or as the cast of `doc -> 'v'` reads the rows that hold one again from the heap, so that it answers, errors
-- included, as the normal plan: the row raises the error of the cast where PostgreSQL evaluates it (an aggregate argument or a grouping
-- expression of a row that reaches the aggregates) and never when a conjunct or a lookup drops the row, or when the cast runs for some rows only.
-- Every query runs with and without the pushdown; the rows, or the error, must be the same.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_helpers;
SET max_parallel_workers_per_gather = 0;
SET seq_page_cost = 1000;
SET random_page_cost = 1000;
\ir test/agg_helpers.sql
\set ECHO none
-- what the scan does, and whether the normal plan does the same
CREATE FUNCTION outcome(q text) RETURNS text LANGUAGE plpgsql AS $$
DECLARE a text := runq(q, false); b text := runq(q, true);
BEGIN
	RETURN CASE WHEN a <> b THEN format('DIFF off=%s on=%s', left(a, 200), left(b, 200))
	            WHEN a LIKE 'ERR %' THEN 'same ' || left(a, 70) ELSE 'same rows' END;
END $$;
-- the rows the scan read again from the heap
CREATE FUNCTION reread(q text) RETURNS bigint LANGUAGE sql AS $$ SELECT sift_helpers.agg_heap_rereads(q) $$;
CREATE TABLE s_t (id int PRIMARY KEY, grp int, doc jsonb) WITH (autovacuum_enabled = false);
INSERT INTO s_t
SELECT i, i % 5, jsonb_build_object('v', (i % 17) + 0.5, 'n', i % 9, 'ok', i % 2 = 0, 't', 'k' || (i % 4), 'w', i::bigint * 1000000000)
FROM generate_series(1, 3000) i;
-- documents that are no object (a key of them is NULL) and JSON nulls
UPDATE s_t SET doc = '[1, 2]' WHERE id % 100 = 1;
UPDATE s_t SET doc = '"text"' WHERE id % 100 = 2;
UPDATE s_t SET doc = '7' WHERE id % 100 = 3;
UPDATE s_t SET doc = jsonb_set(doc, '{v}', 'null') WHERE id % 100 = 4;
-- no dead version is left for the index build (a session of another test with an older snapshot makes the planner leave the index out: indcheckxmin)
CREATE TEMP TABLE s_t_rows AS SELECT * FROM s_t ORDER BY ctid;
TRUNCATE s_t;
INSERT INTO s_t SELECT * FROM s_t_rows;
DROP TABLE s_t_rows;
CREATE INDEX s_t_col ON s_t USING sift_col (id, grp, doc) WITH (paths = 'doc.v float8, doc.n int4, doc.ok bool, doc.t text, doc.w int8');
NOTICE:  sift_col index "s_t_col": 1 automatic choice (track_changes)
DETAIL:  track_changes = id
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('s_t_col');
VACUUM ANALYZE s_t;
-- the leaves hold no value they cannot store but the documents that are no object: those rows are read again
CREATE TABLE s_q (n text PRIMARY KEY, q text);
INSERT INTO s_q VALUES
	('io float8',        'SELECT grp, sum((doc ->> ''v'')::float8), count((doc ->> ''v'')::float8) FROM s_t GROUP BY grp'),
	('cast float8',      'SELECT grp, sum((doc -> ''v'')::float8) FROM s_t GROUP BY grp'),
	('io int4',          'SELECT grp, sum((doc ->> ''n'')::int4), max((doc ->> ''n'')::int4) FROM s_t GROUP BY grp'),
	('io int8',          'SELECT grp, sum((doc ->> ''w'')::int8), min((doc ->> ''w'')::int8) FROM s_t GROUP BY grp'),
	('io bool',          'SELECT grp, count(*) FILTER (WHERE grp > 1), bool_and((doc ->> ''ok'')::bool) FROM s_t GROUP BY grp'),
	('key float8',       'SELECT (doc -> ''v'')::float8 k, count(*) FROM s_t GROUP BY 1'),
	('key io',           'SELECT (doc ->> ''n'')::int4 k, avg((doc ->> ''v'')::float8) FROM s_t GROUP BY 1'),
	('expression',       'SELECT grp, sum((doc ->> ''v'')::float8 * 2 + 1) FROM s_t GROUP BY grp'),
	('having',           'SELECT grp, count(*) FROM s_t GROUP BY grp HAVING max((doc ->> ''v'')::float8) > 10'),
	('text key beside',  'SELECT doc ->> ''t'' k, sum((doc ->> ''v'')::float8) FROM s_t GROUP BY 1'),
	('another column',   'SELECT grp, sum((doc ->> ''v'')::float8) FROM s_t WHERE grp <> 0 GROUP BY grp'),
	('no groups',        'SELECT sum((doc ->> ''v'')::float8), min((doc ->> ''n'')::int4) FROM s_t');
SELECT n, pushed(q) AS pushed, reread(q) AS reread, outcome(q) FROM s_q ORDER BY n;
        n        | pushed | reread |                            outcome                             
-----------------+--------+--------+----------------------------------------------------------------
 another column  | t      |     60 | same rows
 cast float8     | f      |     -1 | same ERR 22023 cannot cast jsonb null to type double precision
 expression      | t      |     60 | same rows
 having          | t      |     60 | same rows
 io bool         | t      |     60 | same rows
 io float8       | t      |     60 | same rows
 io int4         | t      |     60 | same rows
 io int8         | t      |     60 | same rows
 key float8      | f      |     -1 | same ERR 22023 cannot cast jsonb null to type double precision
 key io          | t      |     60 | same rows
 no groups       | t      |     60 | same rows
 text key beside | t      |     60 | same rows
(12 rows)

-- numeric strings, which `->>` and a cast of the text convert (the cast of the jsonb value raises for them, below)
UPDATE s_t SET doc = jsonb_set(jsonb_set(doc, '{v}', '"8.5"'), '{n}', '"3"') WHERE jsonb_typeof(doc) = 'object' AND id % 33 = 5;
UPDATE s_t SET doc = jsonb_set(doc, '{ok}', '"yes"') WHERE jsonb_typeof(doc) = 'object' AND id % 29 = 5;
UPDATE s_t SET doc = jsonb_set(doc, '{w}', to_jsonb((id::bigint * 1000000000)::text)) WHERE jsonb_typeof(doc) = 'object' AND id % 31 = 5;
VACUUM ANALYZE s_t;
SELECT n, pushed(q) AS pushed, reread(q) AS reread, outcome(q) FROM s_q WHERE n NOT IN ('cast float8', 'key float8') ORDER BY n;
        n        | pushed | reread |  outcome  
-----------------+--------+--------+-----------
 another column  | t      |    129 | same rows
 expression      | t      |    148 | same rows
 having          | t      |    148 | same rows
 io bool         | t      |    161 | same rows
 io float8       | t      |    148 | same rows
 io int4         | t      |    148 | same rows
 io int8         | t      |    154 | same rows
 key io          | t      |    148 | same rows
 no groups       | t      |    148 | same rows
 text key beside | t      |    148 | same rows
(10 rows)

-- a value SQL cannot convert: the scan raises the error of the cast for the row it reaches, as the normal plan does
UPDATE s_t SET doc = jsonb_set(doc, '{v}', '"abc"') WHERE id = 1234;
UPDATE s_t SET doc = jsonb_set(doc, '{n}', '3.5') WHERE id = 1236;
UPDATE s_t SET doc = jsonb_set(doc, '{ok}', '"maybe"') WHERE id = 1238;
UPDATE s_t SET doc = jsonb_set(doc, '{w}', '1.5') WHERE id = 1240;
VACUUM ANALYZE s_t;
TRUNCATE s_q;
INSERT INTO s_q VALUES
	('io float8 raises',   'SELECT grp, sum((doc ->> ''v'')::float8) FROM s_t GROUP BY grp'),
	('cast float8 raises', 'SELECT grp, sum((doc -> ''v'')::float8) FROM s_t GROUP BY grp'),
	('io int4 raises',     'SELECT grp, sum((doc ->> ''n'')::int4) FROM s_t GROUP BY grp'),
	('io int8 raises',     'SELECT grp, sum((doc ->> ''w'')::int8) FROM s_t GROUP BY grp'),
	('io bool raises',     'SELECT grp, bool_and((doc ->> ''ok'')::bool) FROM s_t GROUP BY grp'),
	('key raises',         'SELECT (doc ->> ''v'')::float8 k, count(*) FROM s_t GROUP BY 1'),
	('having raises',      'SELECT grp, count(*) FROM s_t GROUP BY grp HAVING max((doc ->> ''v'')::float8) > 10'),
	-- the row is dropped by a condition on another column before the cast: nothing is raised
	('dropped by a conjunct', 'SELECT grp, sum((doc ->> ''v'')::float8) FROM s_t WHERE id <> 1234 GROUP BY grp'),
	('dropped by grp',     'SELECT grp, sum((doc ->> ''v'')::float8) FROM s_t WHERE grp <> 4 GROUP BY grp'),
	('dropped, cast',      'SELECT grp, sum((doc -> ''v'')::float8) FROM s_t WHERE grp <> 4 GROUP BY grp'),
	-- the leaf of another row is dirty, the one read is not
	('other leaf',         'SELECT grp, sum((doc ->> ''n'')::int4) FROM s_t WHERE id <> 1236 GROUP BY grp'),
	('clean leaf',         'SELECT grp, count(*) FILTER (WHERE doc ->> ''t'' = ''k1'') FROM s_t GROUP BY grp');
SELECT n, pushed(q) AS pushed, outcome(q) FROM s_q ORDER BY n;
           n           | pushed |                               outcome                                
-----------------------+--------+----------------------------------------------------------------------
 cast float8 raises    | f      | same ERR 22023 cannot cast jsonb string to type double precision
 clean leaf            | t      | same rows
 dropped by a conjunct | t      | same rows
 dropped by grp        | t      | same rows
 dropped, cast         | f      | same ERR 22023 cannot cast jsonb string to type double precision
 having raises         | t      | same ERR 22P02 invalid input syntax for type double precision: "abc"
 io bool raises        | t      | same ERR 22P02 invalid input syntax for type boolean: "maybe"
 io float8 raises      | t      | same ERR 22P02 invalid input syntax for type double precision: "abc"
 io int4 raises        | t      | same ERR 22P02 invalid input syntax for type integer: "3.5"
 io int8 raises        | t      | same ERR 22P02 invalid input syntax for type bigint: "1.5"
 key raises            | t      | same ERR 22P02 invalid input syntax for type double precision: "abc"
 other leaf            | t      | same rows
(12 rows)

-- the cast runs for some rows only, or the leaf is read otherwise: the normal plan, rows or error as the heap (a condition or a FILTER on the cast: agg_scalar_dirty_where, agg_scalar_dirty_filter)
TRUNCATE s_q;
INSERT INTO s_q VALUES
	('case branch',        'SELECT grp, sum(CASE WHEN grp = 1 THEN (doc ->> ''v'')::float8 END) FROM s_t GROUP BY grp'),
	('case branch drops',  'SELECT grp, sum(CASE WHEN id <> 1234 THEN (doc ->> ''v'')::float8 END) FROM s_t GROUP BY grp'),
	('two kinds',          'SELECT grp, sum((doc ->> ''v'')::float8), sum((doc -> ''v'')::float8) FROM s_t GROUP BY grp'),
	('typeof',             'SELECT grp, sum((doc ->> ''v'')::float8) FROM s_t WHERE jsonb_typeof(doc -> ''v'') <> ''string'' GROUP BY grp'),
	('sub-select',         'SELECT grp, sum((doc ->> ''v'')::float8) FROM s_t WHERE id IN (SELECT id FROM s_t WHERE (doc ->> ''v'')::float8 > 3) GROUP BY grp');
SELECT n, pushed(q) AS pushed, outcome(q) FROM s_q ORDER BY n;
         n         | pushed |                               outcome                                
-------------------+--------+----------------------------------------------------------------------
 case branch       | t      | same rows
 case branch drops | t      | same rows
 sub-select        | f      | same ERR 22P02 invalid input syntax for type double precision: "abc"
 two kinds         | f      | same ERR 22023 cannot cast jsonb string to type double precision
 typeof            | f      | same rows
(5 rows)

-- rows the snapshot does not see raise nothing: the dirty rows are deleted, their units still hold them
DELETE FROM s_t WHERE id IN (1234, 1236, 1238, 1240);
SELECT n, pushed(q) AS pushed, outcome(q) FROM (VALUES
	('io float8 deleted',   'SELECT grp, sum((doc ->> ''v'')::float8) FROM s_t GROUP BY grp'),
	('cast float8 deleted', 'SELECT grp, sum((doc -> ''v'')::float8) FROM s_t GROUP BY grp'),
	('io int4 deleted',     'SELECT grp, sum((doc ->> ''n'')::int4) FROM s_t GROUP BY grp'),
-- id + 0: the b-tree of the primary key answers a short list of keys (agg_unique_key_in_list), and the scan is the plan under test
	('only the deleted',    'SELECT grp, sum((doc -> ''v'')::float8) FROM s_t WHERE id + 0 IN (1234, 1236, 1238) GROUP BY grp')) v(n, q) ORDER BY n;
          n          | pushed |                             outcome                              
---------------------+--------+------------------------------------------------------------------
 cast float8 deleted | f      | same ERR 22023 cannot cast jsonb string to type double precision
 io float8 deleted   | t      | same rows
 io int4 deleted     | t      | same rows
 only the deleted    | f      | same rows
(4 rows)

DROP TABLE s_q;
DROP TABLE s_t;
DROP FUNCTION reread(text);
DROP FUNCTION pushed(text);
DROP FUNCTION outcome(text);
DROP FUNCTION runq(text, bool), outcome, runs_in_scan;
DROP EXTENSION pg_sift_helpers;
DROP EXTENSION pg_sift;