-- ---------------------------------------------------------------------------
-- PostgreSQL finding #81 --- useless-left-join removal silently drops a WHERE
-- equality when a PlaceHolderVar collapses a join EquivalenceClass to one
-- relation.
--
-- 13 distinct query shapes, all reducing to the same defect.  Found by a
-- differential fuzzer over a 24,000-query sweep; every one of the 13 was
-- classified with an instrumented build and fired the left-join-removal
-- EC-collapse canary (and nothing else).
--
-- Verified against stock master 13b7a8a0ef5 (PG 20devel), --enable-cassert,
-- all default GUCs.  Independent of enable_self_join_elimination and of every
-- scan/join-method GUC.
--
--   psql -d yourdb -f finding-81-queries.sql > got.out 2>&1
--   diff -u finding-81-expected.out got.out
--
-- Any difference is the bug: master returns extra rows because the equality
-- qual is deleted from the plan outright (not mis-evaluated --- absent).
--
-- Each query is the verbatim fuzzer output, wrapped in
--   SELECT * FROM ( ... ) AS q ORDER BY 1,2,...
-- purely to make row order deterministic.  The wrapper is a no-op for the bug:
-- all 13 return the identical row multiset with and without it.
--
-- expected rows / master rows:
--   seed 1000157  expected   0, master   6   (self-join above (t1 LEFT JOIN t1); lost qual in WHERE)
--   seed 1002653  expected   0, master  18   (FULL JOIN above the RTE_RESULT; subquery has a WHERE true)
--   seed 1003561  expected   0, master   1   (GROUP BY above the lost qual)
--   seed 1005449  expected   0, master  12   (PHV nested one level deeper, inside a further pullable subquery)
--   seed 1006502  expected   0, master   1   (plainest shape: (SELECT k WHERE true), lost qual in WHERE)
--   seed 1007496  expected   0, master 190   (LATERAL PHV + an extra cross-joined rel; 0 -> 190 rows)
--   seed 1011990  expected   0, master   4   (FULL JOIN above; partitioned table pt as the outer rel)
--   seed 1015686  expected   1, master  10   (LATERAL FROM-less subquery referencing an outer rel; partial loss)
--   seed 1021969  expected  20, master  80   (lost qual is in the ON clause of the join above, not WHERE)
--   seed 1022687  expected   2, master  10   (partial loss: 2 correct rows -> 10)
--   seed 1023131  expected  80, master 320   (also contains a self-join; reproduces with SJE off, so not #34)
--   seed 1023355  expected   6, master  18   (LATERAL with its own WHERE; partial loss 6 -> 18)
--   seed 1023484  expected   0, master   9   (minimal: (SELECT 4 AS v0), ON t21.a = t21.a)
-- ---------------------------------------------------------------------------

\set ON_ERROR_STOP on
\pset pager off
-- unaligned output: no column padding, so this file has no trailing
-- whitespace and diffs cleanly regardless of how it is transported.
\pset format unaligned
\pset fieldsep '|'
\pset null 'NULL'

DROP SCHEMA IF EXISTS pj81 CASCADE;
CREATE SCHEMA pj81;

CREATE TABLE t1 (id int PRIMARY KEY, a int, b int, c text);
CREATE TABLE t2 (id int PRIMARY KEY, a int, b int);
CREATE TABLE u  (id int PRIMARY KEY, a int);
CREATE TABLE pt (id int, a int) PARTITION BY RANGE (id);
CREATE TABLE pt1 PARTITION OF pt FOR VALUES FROM (0)  TO (10);
CREATE TABLE pt2 PARTITION OF pt FOR VALUES FROM (10) TO (100);

INSERT INTO t1 (id, a, b, c) VALUES
  (1, 1, 1, 'x1'),
  (2, 2, 2, 'x2'),
  (3, 3, 0, 'x3'),
  (4, 4, 1, 'x4'),
  (5, 0, 2, 'x5'),
  (6, 1, 0, 'x6'),
  (7, NULL, 1, 'x7'),
  (8, 3, 2, 'x8'),
  (9, 4, 0, 'x9'),
  (10, 0, 1, 'x10'),
  (11, 1, 2, 'x11'),
  (12, 2, 0, 'x12'),
  (13, 3, 1, 'x13'),
  (14, NULL, 2, 'x14'),
  (15, 0, 0, 'x15'),
  (16, 1, 1, 'x16'),
  (17, 2, 2, 'x17'),
  (18, 3, 0, 'x18'),
  (19, 4, 1, 'x19'),
  (20, 0, 2, 'x20');

INSERT INTO t2 (id, a, b) VALUES
  (1, 1, 1),
  (2, 2, 0),
  (3, 0, 1),
  (4, NULL, 0),
  (5, 2, 1),
  (6, 0, 0),
  (7, 1, 1),
  (8, NULL, 0),
  (9, 0, 1),
  (10, 1, 0),
  (11, 2, 1),
  (12, NULL, 0);

INSERT INTO u (id, a) VALUES
  (1, 1),
  (2, 2),
  (3, 3),
  (4, 0),
  (5, 1),
  (6, 2),
  (7, 3),
  (8, 0),
  (9, 1),
  (10, 2);

INSERT INTO pt (id, a) VALUES
  (1, 1),
  (2, 2),
  (3, 3),
  (4, 4),
  (5, 0),
  (6, 1),
  (7, 2),
  (8, 3),
  (9, 4),
  (10, 0),
  (11, 1),
  (12, 2),
  (13, 3),
  (14, 4),
  (15, 0),
  (16, 1),
  (17, 2),
  (18, 3),
  (19, 4),
  (20, 0);

ANALYZE t1, t2, u, pt;


\echo '=== Q01  seed 1000157  --- expected 0 row(s), master returns 6 ==='
-- self-join above (t1 LEFT JOIN t1); lost qual in WHERE
SELECT * FROM (
  SELECT (coalesce(rr3.v0,4) * 2) AS o0, coalesce(t11.a, 1) AS o1, coalesce(t11.id + 0, 0) AS o2, (t11.id * 1) AS o3 FROM ((t1 AS t11 LEFT JOIN t1 AS t12 ON t11.b = t12.id) LEFT JOIN (SELECT 1 AS v0, 2 AS v1) AS rr3 ON rr3.v1 = t11.b + 2) WHERE rr3.v1 = t11.b
) AS q ORDER BY 1, 2, 3, 4;

\echo '=== Q02  seed 1002653  --- expected 0 row(s), master returns 18 ==='
-- FULL JOIN above the RTE_RESULT; subquery has a WHERE true
SELECT * FROM (
  SELECT t12.b AS o0 FROM ((SELECT 7 AS v0 WHERE true) AS rr1 FULL JOIN (t1 AS t12 LEFT JOIN t2 AS t23 ON t12.id = t23.id) ON (t12.a = t12.a AND (t12.id > 0 OR t12.b < 3))) WHERE (rr1.v0 = t12.b AND (rr1.v0 > 1 OR t12.a < 0))
) AS q ORDER BY 1;

\echo '=== Q03  seed 1003561  --- expected 0 row(s), master returns 1 ==='
-- GROUP BY above the lost qual
SELECT * FROM (
  SELECT coalesce(rr3.v0 + 2, 2) AS o0 FROM ((t1 AS t11 LEFT JOIN t2 AS t22 ON t11.id = t22.id) LEFT JOIN (SELECT 6 AS v0, 1 AS v1) AS rr3 ON (t11.id = t11.b OR t11.b IS NULL)) WHERE t11.b = rr3.v0 GROUP BY 1
) AS q ORDER BY 1;

\echo '=== Q04  seed 1005449  --- expected 0 row(s), master returns 12 ==='
-- PHV nested one level deeper, inside a further pullable subquery
SELECT * FROM (
  SELECT (t21.a IS NULL)::int AS o0, (t21.id * 1) AS o1 FROM ((t2 AS t21 LEFT JOIN u AS u2 ON t21.id = u2.id) LEFT JOIN (SELECT (coalesce(rr3.v1,2) * 2) AS v0, rr3.v0 AS v1 FROM (SELECT 5 AS v0, 7 AS v1) AS rr3) AS sq4 ON t21.b = t21.b) WHERE (sq4.v1 = t21.a OR t21.id IS NULL)
) AS q ORDER BY 1, 2;

\echo '=== Q05  seed 1006502  --- expected 0 row(s), master returns 1 ==='
-- plainest shape: (SELECT k WHERE true), lost qual in WHERE
SELECT * FROM (
  SELECT (case when rr3.v0 > 1 then rr3.v0 end) AS o0 FROM ((t2 AS t21 LEFT JOIN t1 AS t12 ON t21.id = t12.id) LEFT JOIN (SELECT 6 AS v0 WHERE true) AS rr3 ON t21.id = t21.b) WHERE rr3.v0 = t21.id
) AS q ORDER BY 1;

\echo '=== Q06  seed 1007496  --- expected 0 row(s), master returns 190 ==='
-- LATERAL PHV + an extra cross-joined rel; 0 -> 190 rows
SELECT * FROM (
  SELECT (case when sq5.v1 > 4 then sq5.v1 end) AS o0 FROM u AS u1, (LATERAL (SELECT (rr4.v1 IS NULL)::int AS v0, (rr4.v0 IS NULL)::int AS v1 FROM ((t1 AS t12 LEFT JOIN t2 AS t23 ON t12.b = t23.id) LEFT JOIN LATERAL (SELECT coalesce(t12.b, 3) AS v0, (case when t12.b > 4 then t12.b end) AS v1) AS rr4 ON true) WHERE t12.id > 1) AS sq5 LEFT JOIN (SELECT 9 AS v0) AS rr6 ON sq5.v0 = sq5.v0) WHERE (rr6.v0 = sq5.v0 OR u1.id IS NULL)
) AS q ORDER BY 1;

\echo '=== Q07  seed 1011990  --- expected 0 row(s), master returns 4 ==='
-- FULL JOIN above; partitioned table pt as the outer rel
SELECT * FROM (
  SELECT (case when rr1.v0 is null then 1 else rr1.v0 end) AS o0, (rr1.v0 IS NULL)::int AS o1, coalesce(pt2.id + 0, 0) AS o2 FROM ((SELECT 8 AS v0) AS rr1 FULL JOIN (pt AS pt2 LEFT JOIN t2 AS t23 ON pt2.id = t23.id) ON (pt2.id = pt2.a OR rr1.v0 IS NULL)) WHERE rr1.v0 = pt2.id
) AS q ORDER BY 1, 2, 3;

\echo '=== Q08  seed 1015686  --- expected 1 row(s), master returns 10 ==='
-- LATERAL FROM-less subquery referencing an outer rel; partial loss
SELECT * FROM (
  SELECT coalesce(rr4.v0 + 2, 2) AS o0, (rr1.v0 IS NULL)::int AS o1 FROM (SELECT 6 AS v0 WHERE true) AS rr1, ((u AS u2 LEFT JOIN t1 AS t13 ON u2.a = t13.id) LEFT JOIN LATERAL (SELECT coalesce(rr1.v0 + 2, 2) AS v0, coalesce(u2.id, 1) AS v1) AS rr4 ON true) WHERE rr4.v0 = rr4.v1
) AS q ORDER BY 1, 2;

\echo '=== Q09  seed 1021969  --- expected 20 row(s), master returns 80 ==='
-- lost qual is in the ON clause of the join above, not WHERE
SELECT * FROM (
  SELECT (coalesce(t13.b,0) * 2) AS o0, (t13.id + 2) AS o1 FROM (pt AS pt1 LEFT JOIN ((SELECT 2 AS v0 WHERE true) AS rr2 FULL JOIN (t1 AS t13 LEFT JOIN t2 AS t24 ON t13.b = t24.id) ON (t13.id = t13.a OR t13.b IS NULL)) ON t13.a = rr2.v0)
) AS q ORDER BY 1, 2;

\echo '=== Q10  seed 1022687  --- expected 2 row(s), master returns 10 ==='
-- partial loss: 2 correct rows -> 10
SELECT * FROM (
  SELECT u1.id AS o0, (coalesce(rr3.v0,0) * 2) AS o1 FROM ((u AS u1 LEFT JOIN u AS u2 ON u1.a = u2.id) LEFT JOIN (SELECT 0 AS v0, 1 AS v1) AS rr3 ON rr3.v0 = rr3.v0) WHERE u1.a = rr3.v0
) AS q ORDER BY 1, 2;

\echo '=== Q11  seed 1023131  --- expected 80 row(s), master returns 320 ==='
-- also contains a self-join; reproduces with SJE off, so not #34
SELECT * FROM (
  SELECT (pt5.id + 0) AS o0, (pt5.id + 2) AS o1, (case when pt5.a > 0 then pt5.a end) AS o2 FROM ((SELECT coalesce(t12.b + 3, 3) AS v0 FROM pt AS pt1, (t1 AS t12 JOIN t1 AS t13 ON t12.id = t13.id) WHERE pt1.a > 3) AS sq4 LEFT JOIN ((pt AS pt5 LEFT JOIN t2 AS t26 ON pt5.a = t26.id) FULL JOIN (SELECT 7 AS v0) AS rr7 ON pt5.a = pt5.id) ON (rr7.v0 = pt5.id AND (sq4.v0 > 0 OR sq4.v0 < 1)))
) AS q ORDER BY 1, 2, 3;

\echo '=== Q12  seed 1023355  --- expected 6 row(s), master returns 18 ==='
-- LATERAL with its own WHERE; partial loss 6 -> 18
SELECT * FROM (
  SELECT coalesce(rr3.v0 + 1, 1) AS o0, (-rr3.v0) AS o1, (rr3.v0 + 3) AS o2, coalesce(t11.b, 4) AS o3 FROM ((t1 AS t11 LEFT JOIN t2 AS t22 ON t11.a = t22.id) LEFT JOIN LATERAL (SELECT (t11.id IS NULL)::int AS v0 WHERE t11.id > 2) AS rr3 ON true) WHERE rr3.v0 = t11.b
) AS q ORDER BY 1, 2, 3, 4;

\echo '=== Q13  seed 1023484  --- expected 0 row(s), master returns 9 ==='
-- minimal: (SELECT 4 AS v0), ON t21.a = t21.a
SELECT * FROM (
  SELECT t21.id AS o0 FROM ((t2 AS t21 LEFT JOIN u AS u2 ON t21.b = u2.id) LEFT JOIN (SELECT 4 AS v0) AS rr3 ON t21.a = t21.a) WHERE t21.a = rr3.v0
) AS q ORDER BY 1;
