-- SiftAgg over a sift_col index whose text is longer than a dictionary entry: units that store it inline, mixed with units that use
-- the dictionary, the write buffer, a merge. Every query runs with and without the pushdown; the results must be the same.
CREATE EXTENSION pg_sift;
SET max_parallel_workers_per_gather = 0;
SET cpu_tuple_cost = 0.5;
CREATE TABLE ag (id int, g int, s text, f float8, n int);
\ir test/agg_rows_helpers.sql
\set ECHO none
CREATE TABLE agg_q (q text);
INSERT INTO agg_q VALUES
	('SELECT count(*), count(s), count(DISTINCT s) FROM ag'),
	('SELECT g, count(*), count(s), count(DISTINCT s), min(s COLLATE "C"), max(s COLLATE "C") FROM ag GROUP BY g'),
	('SELECT s, count(*) FROM ag WHERE s IS NOT NULL GROUP BY s HAVING count(*) > 1'),
	('SELECT count(*), sum(n) FROM ag WHERE s = repeat(''é'', 160) || ''3'''),
	('SELECT count(*) FROM ag WHERE s IN (repeat(''é'', 160) || ''3'', ''short7'', ''absent'')'),
	('SELECT count(*), max(n) FROM ag WHERE s LIKE repeat(''é'', 50) || ''%'' COLLATE "C"'),
	('SELECT count(*) FROM ag WHERE s <> ''short7'''),
	('SELECT g, count(*) FROM ag WHERE s NOT IN (''short7'', ''short8'') GROUP BY g'),
	('SELECT count(*), sum(length(s)) FROM ag WHERE g = 2'),
	('SELECT f, count(*) FROM ag WHERE s IS NULL AND f IS NOT NULL GROUP BY f');
INSERT INTO ag SELECT i, i % 5,
	CASE i % 7 WHEN 0 THEN NULL WHEN 1 THEN repeat('é', 150 + i % 300) || (i % 13) WHEN 2 THEN 'short' || i % 50
		 ELSE md5(i::text) || repeat('x', 200 + i % 100) END,
	CASE i % 17 WHEN 0 THEN NULL WHEN 1 THEN -0.0 ELSE i / 8.0 END, i % 100
FROM generate_series(1, 30000) i;
CREATE INDEX ag_col ON ag USING sift_col (id, g, s, f, n) WITH (write_buffer_limit = 4096);
NOTICE:  sift_col index "ag_col": 1 automatic choice (rollup_columns), 1 advice (track_changes)
DETAIL:  advice: track_changes = the table has no one-column primary key; rollup_columns = g, n
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ag_col');
VACUUM ANALYZE ag;
SELECT sift.col_verify('ag_col', true);
 col_verify 
------------
 
(1 row)

SELECT inline_chunks > 0 AS inline, const_chunks + for_chunks + pack_chunks + codec_chunks > 0 AS coded FROM sift.col_leaves('ag_col') WHERE leaf = 2;
 inline | coded 
--------+-------
 t      | f
(1 row)

SELECT query, pushed, same FROM (SELECT q AS query, agg_pushed(q) AS pushed, agg_rows(q, true) IS NOT DISTINCT FROM agg_rows(q, false) AS same FROM agg_q) z ORDER BY query COLLATE "C";
                                                   query                                                    | pushed | same 
------------------------------------------------------------------------------------------------------------+--------+------
 SELECT count(*) FROM ag WHERE s <> 'short7'                                                                | t      | t
 SELECT count(*) FROM ag WHERE s IN (repeat('é', 160) || '3', 'short7', 'absent')                           | t      | t
 SELECT count(*), count(s), count(DISTINCT s) FROM ag                                                       | t      | t
 SELECT count(*), max(n) FROM ag WHERE s LIKE repeat('é', 50) || '%' COLLATE "C"                            | t      | t
 SELECT count(*), sum(length(s)) FROM ag WHERE g = 2                                                        | t      | t
 SELECT count(*), sum(n) FROM ag WHERE s = repeat('é', 160) || '3'                                          | t      | t
 SELECT f, count(*) FROM ag WHERE s IS NULL AND f IS NOT NULL GROUP BY f                                    | t      | t
 SELECT g, count(*) FROM ag WHERE s NOT IN ('short7', 'short8') GROUP BY g                                  | t      | t
 SELECT g, count(*), count(s), count(DISTINCT s), min(s COLLATE "C"), max(s COLLATE "C") FROM ag GROUP BY g | t      | t
 SELECT s, count(*) FROM ag WHERE s IS NOT NULL GROUP BY s HAVING count(*) > 1                              | t      | t
(10 rows)

-- rows in the write buffer, then sealed, then deleted and merged
INSERT INTO ag SELECT 40000 + i, i % 5, CASE WHEN i % 3 = 0 THEN repeat('é', 160) || '3' ELSE 'short' || i % 20 END, i, i % 100 FROM generate_series(1, 500) i;
SELECT query, pushed, same FROM (SELECT q AS query, agg_pushed(q) AS pushed, agg_rows(q, true) IS NOT DISTINCT FROM agg_rows(q, false) AS same FROM agg_q) z ORDER BY query COLLATE "C";
                                                   query                                                    | pushed | same 
------------------------------------------------------------------------------------------------------------+--------+------
 SELECT count(*) FROM ag WHERE s <> 'short7'                                                                | t      | t
 SELECT count(*) FROM ag WHERE s IN (repeat('é', 160) || '3', 'short7', 'absent')                           | t      | t
 SELECT count(*), count(s), count(DISTINCT s) FROM ag                                                       | t      | t
 SELECT count(*), max(n) FROM ag WHERE s LIKE repeat('é', 50) || '%' COLLATE "C"                            | t      | t
 SELECT count(*), sum(length(s)) FROM ag WHERE g = 2                                                        | t      | t
 SELECT count(*), sum(n) FROM ag WHERE s = repeat('é', 160) || '3'                                          | t      | t
 SELECT f, count(*) FROM ag WHERE s IS NULL AND f IS NOT NULL GROUP BY f                                    | t      | t
 SELECT g, count(*) FROM ag WHERE s NOT IN ('short7', 'short8') GROUP BY g                                  | t      | t
 SELECT g, count(*), count(s), count(DISTINCT s), min(s COLLATE "C"), max(s COLLATE "C") FROM ag GROUP BY g | t      | t
 SELECT s, count(*) FROM ag WHERE s IS NOT NULL GROUP BY s HAVING count(*) > 1                              | t      | t
(10 rows)

SELECT sift.col_promote('ag_col') >= 0 AS promoted;
 promoted 
----------
 t
(1 row)

SELECT query, pushed, same FROM (SELECT q AS query, agg_pushed(q) AS pushed, agg_rows(q, true) IS NOT DISTINCT FROM agg_rows(q, false) AS same FROM agg_q) z ORDER BY query COLLATE "C";
                                                   query                                                    | pushed | same 
------------------------------------------------------------------------------------------------------------+--------+------
 SELECT count(*) FROM ag WHERE s <> 'short7'                                                                | t      | t
 SELECT count(*) FROM ag WHERE s IN (repeat('é', 160) || '3', 'short7', 'absent')                           | t      | t
 SELECT count(*), count(s), count(DISTINCT s) FROM ag                                                       | t      | t
 SELECT count(*), max(n) FROM ag WHERE s LIKE repeat('é', 50) || '%' COLLATE "C"                            | t      | t
 SELECT count(*), sum(length(s)) FROM ag WHERE g = 2                                                        | t      | t
 SELECT count(*), sum(n) FROM ag WHERE s = repeat('é', 160) || '3'                                          | t      | t
 SELECT f, count(*) FROM ag WHERE s IS NULL AND f IS NOT NULL GROUP BY f                                    | t      | t
 SELECT g, count(*) FROM ag WHERE s NOT IN ('short7', 'short8') GROUP BY g                                  | t      | t
 SELECT g, count(*), count(s), count(DISTINCT s), min(s COLLATE "C"), max(s COLLATE "C") FROM ag GROUP BY g | t      | t
 SELECT s, count(*) FROM ag WHERE s IS NOT NULL GROUP BY s HAVING count(*) > 1                              | t      | t
(10 rows)

DELETE FROM ag WHERE id % 4 = 0;
VACUUM ag;
SELECT sift.col_merge('ag_col', true) >= 0 AS merged;
 merged 
--------
 t
(1 row)

SELECT sift.col_verify('ag_col', true);
 col_verify 
------------
 
(1 row)

SELECT query, pushed, same FROM (SELECT q AS query, agg_pushed(q) AS pushed, agg_rows(q, true) IS NOT DISTINCT FROM agg_rows(q, false) AS same FROM agg_q) z ORDER BY query COLLATE "C";
                                                   query                                                    | pushed | same 
------------------------------------------------------------------------------------------------------------+--------+------
 SELECT count(*) FROM ag WHERE s <> 'short7'                                                                | t      | t
 SELECT count(*) FROM ag WHERE s IN (repeat('é', 160) || '3', 'short7', 'absent')                           | t      | t
 SELECT count(*), count(s), count(DISTINCT s) FROM ag                                                       | t      | t
 SELECT count(*), max(n) FROM ag WHERE s LIKE repeat('é', 50) || '%' COLLATE "C"                            | t      | t
 SELECT count(*), sum(length(s)) FROM ag WHERE g = 2                                                        | t      | t
 SELECT count(*), sum(n) FROM ag WHERE s = repeat('é', 160) || '3'                                          | t      | t
 SELECT f, count(*) FROM ag WHERE s IS NULL AND f IS NOT NULL GROUP BY f                                    | t      | t
 SELECT g, count(*) FROM ag WHERE s NOT IN ('short7', 'short8') GROUP BY g                                  | t      | t
 SELECT g, count(*), count(s), count(DISTINCT s), min(s COLLATE "C"), max(s COLLATE "C") FROM ag GROUP BY g | t      | t
 SELECT s, count(*) FROM ag WHERE s IS NOT NULL GROUP BY s HAVING count(*) > 1                              | t      | t
(10 rows)

DROP TABLE ag, agg_q;
DROP FUNCTION agg_rows, agg_pushed;
DROP EXTENSION pg_sift;