-- SELECT DISTINCT as a group-by with no aggregate: the distinct stage of the planner takes the SiftAgg path. Every query runs with and without
-- the pushdown; the results must be the same. A window function runs above the grouping and keeps its plan; the DISTINCT of a grouped query stays above the grouping, which is the scan's. DISTINCT ON
-- picks the first row of each group in the order of the ORDER BY (agg_distinct_on): here the ORDER BY is the select list, so the row it picks has no other column to differ in.
CREATE EXTENSION pg_sift;
SET max_parallel_workers_per_gather = 0;
SET cpu_tuple_cost = 0.5;
CREATE TABLE ds (id int, g int, k text, d date, n int, v numeric(10,2), c int);
INSERT INTO ds SELECT i, i % 7, CASE WHEN i % 11 = 0 THEN NULL ELSE 'k' || i % 5 END, date '2024-01-01' + i % 400, i % 100, (i % 977) / 7.0,
	CASE WHEN i % 13 = 0 THEN NULL ELSE i % 300 END FROM generate_series(1, 20000) i;
CREATE INDEX ds_col ON ds USING sift_col (id, g, k, d, n, v, c);
NOTICE:  sift_col index "ds_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, k
HINT:  Reasons, costs and how to turn each off: SELECT * FROM sift.index_choices('ds_col');
CREATE TABLE ds_dim (g int, name text);
INSERT INTO ds_dim SELECT i, 'g' || i % 3 FROM generate_series(0, 6) i;
VACUUM ANALYZE ds;
\ir test/agg_rows_helpers.sql
\set ECHO none
CREATE TABLE agg_q (q text);
INSERT INTO agg_q VALUES
	('SELECT DISTINCT g FROM ds'),
	('SELECT DISTINCT k FROM ds'),
	('SELECT DISTINCT g, k FROM ds'),
	('SELECT DISTINCT g, k, c FROM ds WHERE n > 90'),
	('SELECT DISTINCT date_trunc(''month'', d) FROM ds'),
	('SELECT DISTINCT date_trunc(''month'', d), upper(k), g % 3 FROM ds WHERE v > 20'),
	('SELECT DISTINCT c IS NULL, k IS NULL, g FROM ds'),
	('SELECT DISTINCT k FROM ds WHERE id < 0'),
	('SELECT DISTINCT n, c FROM ds WHERE n < 10 AND c IS NOT NULL'),
	('SELECT DISTINCT g, k FROM ds ORDER BY g DESC, k NULLS FIRST LIMIT 7'),
	('SELECT count(*) FROM (SELECT DISTINCT c FROM ds) t'),
	('SELECT count(*), count(k) FROM (SELECT DISTINCT g, k FROM ds) t'),
	('SELECT DISTINCT m.name, ds.k FROM ds JOIN ds_dim m ON m.g = ds.g'),
	('SELECT DISTINCT g FROM ds WHERE k IN (''k1'', ''k2'') OR c = 5'),
	('SELECT DISTINCT ON (g) g, k FROM ds ORDER BY g, k'),
	('SELECT DISTINCT g, count(*) FROM ds GROUP BY g, k'),
	('SELECT DISTINCT g, rank() OVER (ORDER BY g) FROM ds');
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 DISTINCT ON (g) g, k FROM ds ORDER BY g, k                            | t      | t
 SELECT DISTINCT c IS NULL, k IS NULL, g FROM ds                              | t      | t
 SELECT DISTINCT date_trunc('month', d) FROM ds                               | t      | t
 SELECT DISTINCT date_trunc('month', d), upper(k), g % 3 FROM ds WHERE v > 20 | t      | t
 SELECT DISTINCT g FROM ds                                                    | t      | t
 SELECT DISTINCT g FROM ds WHERE k IN ('k1', 'k2') OR c = 5                   | t      | t
 SELECT DISTINCT g, count(*) FROM ds GROUP BY g, k                            | t      | t
 SELECT DISTINCT g, k FROM ds                                                 | t      | t
 SELECT DISTINCT g, k FROM ds ORDER BY g DESC, k NULLS FIRST LIMIT 7          | t      | t
 SELECT DISTINCT g, k, c FROM ds WHERE n > 90                                 | t      | t
 SELECT DISTINCT g, rank() OVER (ORDER BY g) FROM ds                          | f      | t
 SELECT DISTINCT k FROM ds                                                    | t      | t
 SELECT DISTINCT k FROM ds WHERE id < 0                                       | t      | t
 SELECT DISTINCT m.name, ds.k FROM ds JOIN ds_dim m ON m.g = ds.g             | t      | t
 SELECT DISTINCT n, c FROM ds WHERE n < 10 AND c IS NOT NULL                  | t      | t
 SELECT count(*) FROM (SELECT DISTINCT c FROM ds) t                           | t      | t
 SELECT count(*), count(k) FROM (SELECT DISTINCT g, k FROM ds) t              | t      | t
(17 rows)

SET sift.enable_agg = on;
EXPLAIN (COSTS OFF) SELECT DISTINCT g, k FROM ds;
      QUERY PLAN       
-----------------------
 Custom Scan (SiftAgg)
   Index: ds_col
   Group Keys: g, k
(3 rows)

EXPLAIN (COSTS OFF) SELECT DISTINCT ON (g) g, k FROM ds ORDER BY g, k;
                        QUERY PLAN                        
----------------------------------------------------------
 Sort
   Sort Key: g, k
   ->  Custom Scan (SiftAgg)
         Index: ds_col
         Group Keys: g
         Pushed Aggregates: first_row(ds.k ORDER BY ds.k)
(6 rows)

DROP TABLE ds, ds_dim, agg_q;
DROP FUNCTION agg_rows, agg_pushed;
DROP EXTENSION pg_sift;