--
-- Brute-force test of COUNT window run-condition pushdown correctness.
--
-- Enumerates all 3,072 combinations of
--
--     {ORDER BY, no ORDER BY} x {ROWS, RANGE, GROUPS}
--     x 8 start bounds x 8 end bounds x 4 exclusion clauses
--     x {count(*), count(e)}
--
-- (bounds: UNBOUNDED, {0,1,2} PRECEDING/FOLLOWING, CURRENT ROW),
-- discarding combinations the server rejects as invalid.  For each
-- remaining window and each qual "c <= K" / "c >= K" / "c = K" with
-- K in 0..6, if the plan contains a "Run Condition" the query result
-- is compared against the same query with "c + 0" in the qual, which
-- blocks the pushdown and therefore serves as an oracle.  The window
-- function itself is computed by an identical subplan either way, so
-- any difference in the filtered result is a wrong result caused by
-- the run condition.
--
-- The six datasets vary peer-group sizes, the position of NULL count
-- arguments within peer groups, and NULL sort keys.
--
-- Usage: psql -X -f count_runcondition_matrix.sql
-- Prints a summary and a per-window failure list at the end;
-- an empty failure list means no wrong results were found.
--


CREATE TEMP TABLE d1 (s int, e int);  -- distinct sort keys, no NULLs
INSERT INTO d1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5);

CREATE TEMP TABLE d2 (s int, e int);  -- peer groups, NULL arg last in group
INSERT INTO d2 VALUES (1,1),(2,2),(2,NULL),(3,3),(3,4),(3,NULL);

CREATE TEMP TABLE d3 (s int, e int);  -- NULL arg first in its group
INSERT INTO d3 VALUES (1,NULL),(2,NULL),(2,2),(3,NULL),(3,3),(3,4);

CREATE TEMP TABLE d4 (s int, e int);  -- NULLs scattered, varying group sizes
INSERT INTO d4 VALUES (1,1),(1,NULL),(2,NULL),(3,3),(3,NULL),(3,5),(4,NULL);

CREATE TEMP TABLE d5 (s int, e int);  -- NULL sort keys
INSERT INTO d5 VALUES (NULL,1),(NULL,NULL),(1,1),(2,NULL),(2,2);

CREATE TEMP TABLE d6 (s int, e int);  -- one big peer group
INSERT INTO d6 VALUES (1,1),(1,NULL),(1,2),(1,NULL),(1,3);

CREATE TEMP TABLE mismatches (
    winspec text,
    fn      text,
    dir     text,
    k       int,
    ds      text,
    pushed  text,
    oracle  text
);

DO $$
DECLARE
    obs    text[] := ARRAY['ORDER BY s ', ''];
    modes  text[] := ARRAY['ROWS', 'RANGE', 'GROUPS'];
    sbounds text[] := ARRAY['UNBOUNDED PRECEDING', '2 PRECEDING',
                            '1 PRECEDING', '0 PRECEDING', 'CURRENT ROW',
                            '0 FOLLOWING', '1 FOLLOWING', '2 FOLLOWING'];
    ebounds text[] := ARRAY['2 PRECEDING', '1 PRECEDING', '0 PRECEDING',
                            'CURRENT ROW', '0 FOLLOWING', '1 FOLLOWING',
                            '2 FOLLOWING', 'UNBOUNDED FOLLOWING'];
    exs    text[] := ARRAY['', ' EXCLUDE CURRENT ROW',
                           ' EXCLUDE GROUP', ' EXCLUDE TIES'];
    fns    text[] := ARRAY['count(*)', 'count(e)'];
    dss    text[] := ARRAY['d1', 'd2', 'd3', 'd4', 'd5', 'd6'];
    dirs   text[] := ARRAY['<=', '>=', '='];

    ob text; mode text; sb text; eb text; ex text; fn text;
    dir text; ds text; k int;
    winspec text; sub text; plan text; ln text;
    r_push text; r_oracle text;
    spec_valid boolean;
    n_enum int := 0; n_valid int := 0; n_rc int := 0; n_cmp int := 0;
BEGIN
    FOREACH ob IN ARRAY obs LOOP
    FOREACH mode IN ARRAY modes LOOP
    FOREACH sb IN ARRAY sbounds LOOP
    FOREACH eb IN ARRAY ebounds LOOP
    FOREACH ex IN ARRAY exs LOOP
    FOREACH fn IN ARRAY fns LOOP
        n_enum := n_enum + 1;
        winspec := ob || mode || ' BETWEEN ' || sb || ' AND ' || eb || ex;
        sub := format('SELECT s, e, %s OVER (%s) AS c FROM %%s', fn, winspec);
        spec_valid := true;

        FOREACH dir IN ARRAY dirs LOOP
            EXIT WHEN NOT spec_valid;

            -- probe the plan; skip specs the server rejects
            plan := '';
            BEGIN
                FOR ln IN EXECUTE format(
                    'EXPLAIN (COSTS OFF) SELECT * FROM (%s) t WHERE c %s 3',
                    format(sub, 'd1'), dir)
                LOOP
                    plan := plan || ln || E'\n';
                END LOOP;
            EXCEPTION WHEN OTHERS THEN
                spec_valid := false;
                CONTINUE;
            END;
            IF dir = '<=' THEN n_valid := n_valid + 1; END IF;

            CONTINUE WHEN plan NOT LIKE '%Run Condition%';
            n_rc := n_rc + 1;

            FOREACH ds IN ARRAY dss LOOP
                FOR k IN 0..6 LOOP
                    n_cmp := n_cmp + 1;
                    EXECUTE format(
                        'SELECT array_agg((s,e,c)::text
                                 ORDER BY s NULLS FIRST, e NULLS FIRST, c)::text
                         FROM (%s) t WHERE c %s %s',
                        format(sub, ds), dir, k) INTO r_push;
                    EXECUTE format(
                        'SELECT array_agg((s,e,c)::text
                                 ORDER BY s NULLS FIRST, e NULLS FIRST, c)::text
                         FROM (%s) t WHERE c + 0 %s %s',
                        format(sub, ds), dir, k) INTO r_oracle;
                    IF r_push IS DISTINCT FROM r_oracle THEN
                        INSERT INTO mismatches
                        VALUES (winspec, fn, dir, k, ds, r_push, r_oracle);
                    END IF;
                END LOOP;
            END LOOP;
        END LOOP;
    END LOOP;
    END LOOP;
    END LOOP;
    END LOOP;
    END LOOP;
    END LOOP;

    RAISE NOTICE 'enumerated: %, valid: %, with run condition (spec x dir): %, comparisons: %',
        n_enum, n_valid, n_rc, n_cmp;
END;
$$;

SELECT count(*) AS mismatching_queries,
       count(DISTINCT winspec || fn) AS distinct_bad_windows
FROM mismatches;

SELECT winspec, fn,
       string_agg(DISTINCT dir, ',') AS dirs,
       string_agg(DISTINCT ds, ',')  AS datasets,
       count(*) AS nfail
FROM mismatches
GROUP BY winspec, fn
ORDER BY winspec, fn;
