--
-- Reproducers showing that two regression tests added by the v2 patch
-- ("Fix COUNT's logic for window run condition support", bug #19533)
-- assert plans that produce wrong results.
--
-- Each case uses the exact window definition from the v2 test, a dataset
-- that exposes it, and a "c + 0" baseline qual as the oracle: adding
-- "+ 0" blocks the run condition pushdown, so any difference between the
-- two results is a wrong result caused by the pushdown.
--
-- Verified against the v2 patch applied to master (20devel): in both cases
-- the pushed-down query returns fewer rows than the oracle.  Note that the
-- v2 regression run itself doesn't catch either, because empsalary contains
-- no NULL empnos.
--

DROP TABLE IF EXISTS wt1, wt2;

------------------------------------------------------------------------------
-- Case 1: the third of v2's "Ensure the following 3 ROWS mode tests also
--         push down" tests:
--
--     count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING
--                        AND CURRENT ROW EXCLUDE TIES) ... WHERE c <= 3
--
-- The expected output asserts a Run Condition.  But EXCLUDE TIES keeps the
-- current row (whose count argument may be NULL) while dropping its earlier
-- peers, so the count is not monotonically increasing.  This uses the
-- test's own qual, c <= 3.
------------------------------------------------------------------------------

CREATE TABLE wt1 (salary int, empno int);
INSERT INTO wt1 VALUES (100,1),(200,2),(300,3),(400,4),(400,NULL);
-- counts are 1,2,3,4,3: for the second 400-peer, EXCLUDE TIES removes the
-- first peer's non-NULL empno while the row's own NULL empno counts nothing.

EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno, salary,
          count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND CURRENT ROW EXCLUDE TIES) c
   FROM wt1) emp
WHERE c <= 3;

-- v2: 3 rows.  Correct: 4 rows (the (NULL,400,c=3) row is lost because the
-- executor stops when the count hits 4 one row earlier).
SELECT * FROM
  (SELECT empno, salary,
          count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND CURRENT ROW EXCLUDE TIES) c
   FROM wt1) emp
WHERE c <= 3;

SELECT * FROM
  (SELECT empno, salary,
          count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND CURRENT ROW EXCLUDE TIES) c
   FROM wt1) emp
WHERE c + 0 <= 3;   -- oracle: blocks the pushdown

------------------------------------------------------------------------------
-- Case 2: v2's "As above, but check we detect it's monotonically increasing"
--         test:
--
--     count(empno) OVER (ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
--
-- The expected output asserts a Run Condition, justified by v2's new rule
-- "no ORDER BY + ROWS mode => monotonically increasing".  But the count
-- here is just (empno IS NOT NULL) for each row, i.e. 0 or 1 in any order.
-- The test's own qual (c <= 3) can never fail for this window since the
-- count is at most 1, so the constant is lowered to c <= 0 to let the
-- false INCREASING claim misfire.
------------------------------------------------------------------------------

CREATE TABLE wt2 (empno int, salary int);
INSERT INTO wt2 VALUES (1,100),(NULL,200),(2,300);
-- counts are 1,0,1

EXPLAIN (COSTS OFF)
SELECT * FROM
  (SELECT empno, salary,
          count(empno) OVER (ROWS BETWEEN CURRENT ROW AND CURRENT ROW) c
   FROM wt2) emp
WHERE c <= 0;

-- v2: 0 rows.  Correct: 1 row (the NULL-empno row has c = 0, but the
-- "increasing" claim stops output at the first row's c = 1).
SELECT * FROM
  (SELECT empno, salary,
          count(empno) OVER (ROWS BETWEEN CURRENT ROW AND CURRENT ROW) c
   FROM wt2) emp
WHERE c <= 0;

SELECT * FROM
  (SELECT empno, salary,
          count(empno) OVER (ROWS BETWEEN CURRENT ROW AND CURRENT ROW) c
   FROM wt2) emp
WHERE c + 0 <= 0;   -- oracle: blocks the pushdown
