-- pg_orca LATERAL regression test
-- Locks in the LATERAL-shape cases enabled by the LATERAL feature work.
--
-- Base shapes (A-F):
--   A  uncorrelated LATERAL derived table
--   B  equi-correlated LATERAL derived table  -> Hash Join
--   C  LATERAL scalar projection (no FROM)
--   D  LATERAL TVF with outer-ref arg
--   E  LATERAL with LIMIT/Sort/Filter
--   F  LEFT JOIN LATERAL with equi-correlation -> Hash Right Join
--
-- Nested / composite (N1-N11):
--   N1  2-level chain (b refs a, c refs b)
--   N2  2 LATERALs both refer only to outermost
--   N3  2nd LATERAL refers to outer AND middle
--   N4  LATERAL nested inside another LATERAL
--   N5  LATERAL contains an inner JOIN
--   N6  LEFT JOIN LATERAL with nested LATERAL + LIMIT
--   N7  LATERAL contains an aggregate
--   N8  LATERAL with non-equi range correlation
--   N9  3-level chain ending in a TVF
--   N10 LATERAL inside EXISTS
--   N11 LEFT LATERAL with empty inner side
--   N12 LATERAL ref to derived-table computed column + aggregate
--       (regression for sibling-correlated outer-ref pruning bug)
--
-- Edge cases (E1-E12):
--   E1  varlevelsup=2 (LATERAL inside correlated subquery)
--   E2  LATERAL inside a CTE body (regression for the InnerApply
--       PopCopyWithRemappedColumns nullptr crash when the CTE consumer
--       inlines a LATERAL-derived Apply)
--   E3  VALUES + LATERAL
--   E4  LATERAL + GROUP BY at the outer
--   E5  LATERAL + GROUPING SETS
--   E6  LATERAL + window function
--   E7  UNION ALL inside the LATERAL body
--   E8  LATERAL with DISTINCT outside
--   E9  LATERAL top-N per outer (ORDER BY ... LIMIT 1)
--   E10 INSERT...SELECT with LATERAL
--   E11 3-level LATERAL where the grandchild references the outermost
--   E12 LATERAL unnest(array)
LOAD 'pg_orca';
SET pg_orca.enable_orca = on;
SET client_min_messages = warning;
-- start_ignore
DROP TABLE IF EXISTS lat_t1;
DROP TABLE IF EXISTS lat_t2;
-- end_ignore
CREATE TABLE lat_t1 (x int, y int);
CREATE TABLE lat_t2 (a int, b int);
INSERT INTO lat_t1 SELECT i, i*10  FROM generate_series(1, 5)  i;
INSERT INTO lat_t2 SELECT i, i*100 FROM generate_series(1, 20) i;
ANALYZE lat_t1;
ANALYZE lat_t2;
-- A: uncorrelated LATERAL (sanity)
EXPLAIN (COSTS OFF)
SELECT * FROM lat_t1, LATERAL (SELECT * FROM lat_t2) s ORDER BY 1, 3;
           QUERY PLAN           
--------------------------------
 Sort
   Sort Key: lat_t1.x, lat_t2.a
   ->  Nested Loop
         Join Filter: true
         ->  Seq Scan on lat_t1
         ->  Seq Scan on lat_t2
 Optimizer: pg_orca
(7 rows)

SELECT count(*) FROM lat_t1, LATERAL (SELECT * FROM lat_t2) s;
 count 
-------
   100
(1 row)

-- B: equi-correlated LATERAL derived table -> Hash Join
EXPLAIN (COSTS OFF)
SELECT * FROM lat_t1, LATERAL (SELECT * FROM lat_t2 WHERE lat_t2.a = lat_t1.x) s ORDER BY 1;
                QUERY PLAN                
------------------------------------------
 Sort
   Sort Key: lat_t1.x
   ->  Hash Join
         Hash Cond: (lat_t1.x = lat_t2.a)
         ->  Seq Scan on lat_t1
         ->  Hash
               ->  Seq Scan on lat_t2
 Optimizer: pg_orca
(8 rows)

SELECT * FROM lat_t1, LATERAL (SELECT * FROM lat_t2 WHERE lat_t2.a = lat_t1.x) s ORDER BY 1;
 x | y  | a |  b  
---+----+---+-----
 1 | 10 | 1 | 100
 2 | 20 | 2 | 200
 3 | 30 | 3 | 300
 4 | 40 | 4 | 400
 5 | 50 | 5 | 500
(5 rows)

-- C: LATERAL scalar projection with no FROM
EXPLAIN (COSTS OFF)
SELECT * FROM lat_t1, LATERAL (SELECT lat_t1.x + lat_t1.y AS z) s ORDER BY 1;
           QUERY PLAN           
--------------------------------
 Nested Loop
   Join Filter: true
   ->  Sort
         Sort Key: x
         ->  Seq Scan on lat_t1
   ->  Materialize
         ->  Result
               ->  Result
 Optimizer: pg_orca
(9 rows)

SELECT * FROM lat_t1, LATERAL (SELECT lat_t1.x + lat_t1.y AS z) s ORDER BY 1;
 x | y  | z  
---+----+----
 1 | 10 | 11
 2 | 20 | 22
 3 | 30 | 33
 4 | 40 | 44
 5 | 50 | 55
(5 rows)

-- D: LATERAL TVF with outer-ref arg
EXPLAIN (COSTS OFF)
SELECT * FROM lat_t1, LATERAL generate_series(1, lat_t1.x) g ORDER BY 1, 3;
                   QUERY PLAN                   
------------------------------------------------
 Sort
   Sort Key: lat_t1.x, g.g
   ->  Nested Loop
         Join Filter: true
         ->  Seq Scan on lat_t1
         ->  Function Scan on generate_series g
 Optimizer: pg_orca
(7 rows)

SELECT * FROM lat_t1, LATERAL generate_series(1, lat_t1.x) g ORDER BY 1, 3;
 x | y  | g 
---+----+---
 1 | 10 | 1
 2 | 20 | 1
 2 | 20 | 2
 3 | 30 | 1
 3 | 30 | 2
 3 | 30 | 3
 4 | 40 | 1
 4 | 40 | 2
 4 | 40 | 3
 4 | 40 | 4
 5 | 50 | 1
 5 | 50 | 2
 5 | 50 | 3
 5 | 50 | 4
 5 | 50 | 5
(15 rows)

-- E: LATERAL with LIMIT (per-outer top-N)
EXPLAIN (COSTS OFF)
SELECT * FROM lat_t1, LATERAL (
    SELECT * FROM lat_t2 WHERE lat_t2.a >= lat_t1.x ORDER BY lat_t2.a LIMIT 2
) s
ORDER BY 1, 3;
                    QUERY PLAN                     
---------------------------------------------------
 Sort
   Sort Key: lat_t1.x, lat_t2.a
   ->  Nested Loop
         Join Filter: true
         ->  Seq Scan on lat_t1
         ->  Limit
               ->  Sort
                     Sort Key: lat_t2.a
                     ->  Seq Scan on lat_t2
                           Filter: (a >= lat_t1.x)
 Optimizer: pg_orca
(11 rows)

SELECT * FROM lat_t1, LATERAL (
    SELECT * FROM lat_t2 WHERE lat_t2.a >= lat_t1.x ORDER BY lat_t2.a LIMIT 2
) s
ORDER BY 1, 3;
 x | y  | a |  b  
---+----+---+-----
 1 | 10 | 1 | 100
 1 | 10 | 2 | 200
 2 | 20 | 2 | 200
 2 | 20 | 3 | 300
 3 | 30 | 3 | 300
 3 | 30 | 4 | 400
 4 | 40 | 4 | 400
 4 | 40 | 5 | 500
 5 | 50 | 5 | 500
 5 | 50 | 6 | 600
(10 rows)

-- F: LEFT JOIN LATERAL with equi-correlation -> Hash Right Join
EXPLAIN (COSTS OFF)
SELECT * FROM lat_t1
LEFT JOIN LATERAL (SELECT * FROM lat_t2 WHERE lat_t2.a = lat_t1.x) s ON true
ORDER BY 1;
                QUERY PLAN                
------------------------------------------
 Sort
   Sort Key: lat_t1.x
   ->  Hash Right Join
         Hash Cond: (lat_t2.a = lat_t1.x)
         ->  Seq Scan on lat_t2
         ->  Hash
               ->  Seq Scan on lat_t1
 Optimizer: pg_orca
(8 rows)

SELECT * FROM lat_t1
LEFT JOIN LATERAL (SELECT * FROM lat_t2 WHERE lat_t2.a = lat_t1.x) s ON true
ORDER BY 1;
 x | y  | a |  b  
---+----+---+-----
 1 | 10 | 1 | 100
 2 | 20 | 2 | 200
 3 | 30 | 3 | 300
 4 | 40 | 4 | 400
 5 | 50 | 5 | 500
(5 rows)

-- =========================================================================
-- Nested / composite cases use three tables na/nb/nc with a chain shape.
-- =========================================================================
-- start_ignore
DROP TABLE IF EXISTS lat_na;
DROP TABLE IF EXISTS lat_nb;
DROP TABLE IF EXISTS lat_nc;
-- end_ignore
CREATE TABLE lat_na (id int, val int);
CREATE TABLE lat_nb (id int, ref int, val int);
CREATE TABLE lat_nc (id int, ref int, val int);
INSERT INTO lat_na SELECT i, i*10  FROM generate_series(1, 4)  i;
INSERT INTO lat_nb SELECT i, (i-1)%4+1, i*100  FROM generate_series(1, 8)  i;
INSERT INTO lat_nc SELECT i, (i-1)%8+1, i*1000 FROM generate_series(1, 12) i;
ANALYZE lat_na;
ANALYZE lat_nb;
ANALYZE lat_nc;
-- N1: 2-level chain, each LATERAL refs its immediate outer
SELECT count(*) FROM lat_na,
  LATERAL (SELECT * FROM lat_nb WHERE lat_nb.ref = lat_na.id) b,
  LATERAL (SELECT * FROM lat_nc WHERE lat_nc.ref = b.id) c;
 count 
-------
    12
(1 row)

-- N2: 2 LATERALs both refer only to outermost
SELECT count(*) FROM lat_na,
  LATERAL (SELECT * FROM lat_nb WHERE lat_nb.ref = lat_na.id) b,
  LATERAL (SELECT * FROM lat_nc WHERE lat_nc.ref = lat_na.id) c;
 count 
-------
    16
(1 row)

-- N3: 2nd LATERAL refs both outermost and middle
SELECT count(*) FROM lat_na,
  LATERAL (SELECT * FROM lat_nb WHERE lat_nb.ref = lat_na.id) b,
  LATERAL (SELECT * FROM lat_nc
           WHERE lat_nc.ref = b.id AND lat_nc.val > lat_na.val*50) c;
 count 
-------
    12
(1 row)

-- N4: LATERAL inside another LATERAL (recursive nesting)
SELECT count(*) FROM lat_na,
  LATERAL (
    SELECT lat_nb.id AS bid, lat_nc.id AS cid
    FROM lat_nb,
         LATERAL (SELECT * FROM lat_nc WHERE lat_nc.ref = lat_nb.id) lat_nc
    WHERE lat_nb.ref = lat_na.id
  ) bc;
 count 
-------
    12
(1 row)

-- N5: LATERAL with inner JOIN -> ORCA flattens to a 3-way chain
EXPLAIN (COSTS OFF)
SELECT * FROM lat_na,
  LATERAL (SELECT lat_nb.id AS bid, lat_nc.id AS cid
           FROM lat_nb JOIN lat_nc ON lat_nc.ref = lat_nb.id
           WHERE lat_nb.ref = lat_na.id) j
ORDER BY lat_na.id, j.bid, j.cid;
                       QUERY PLAN                        
---------------------------------------------------------
 Sort
   Sort Key: lat_na.id, lat_nb.id, lat_nc.id
   ->  Hash Join
         Hash Cond: (lat_nc.ref = lat_nb.id)
         ->  Seq Scan on lat_nc
         ->  Hash
               ->  Hash Join
                     Hash Cond: (lat_nb.ref = lat_na.id)
                     ->  Seq Scan on lat_nb
                     ->  Hash
                           ->  Seq Scan on lat_na
 Optimizer: pg_orca
(12 rows)

SELECT count(*) FROM lat_na,
  LATERAL (SELECT lat_nb.id AS bid, lat_nc.id AS cid
           FROM lat_nb JOIN lat_nc ON lat_nc.ref = lat_nb.id
           WHERE lat_nb.ref = lat_na.id) j;
 count 
-------
    12
(1 row)

-- N6: LEFT JOIN LATERAL with nested LATERAL + LIMIT
SELECT count(*) FROM lat_na LEFT JOIN LATERAL (
  SELECT * FROM lat_nb,
       LATERAL (SELECT val AS cv FROM lat_nc
                WHERE lat_nc.ref = lat_nb.id LIMIT 1) c
  WHERE lat_nb.ref = lat_na.id
) bc ON true;
 count 
-------
     8
(1 row)

-- N7: LATERAL containing an aggregate
SELECT lat_na.id, agg.s
FROM lat_na,
     LATERAL (SELECT sum(val) AS s FROM lat_nb
              WHERE lat_nb.ref = lat_na.id) agg
ORDER BY lat_na.id;
 id |  s   
----+------
  1 |  600
  2 |  800
  3 | 1000
  4 | 1200
(4 rows)

-- N8: LATERAL non-equi range correlation
SELECT count(*) FROM lat_na,
  LATERAL (SELECT * FROM lat_nb
           WHERE lat_nb.val > lat_na.val*30
             AND lat_nb.val < lat_na.val*70) s;
 count 
-------
     5
(1 row)

-- N9: 3-level chain ending in a TVF
SELECT count(*) FROM lat_na,
  LATERAL (SELECT * FROM lat_nb WHERE lat_nb.ref = lat_na.id) b,
  LATERAL (SELECT * FROM lat_nc WHERE lat_nc.ref = b.id) c,
  LATERAL (SELECT generate_series(1, c.id % 3 + 1) AS g) g;
 count 
-------
    24
(1 row)

-- N10: LATERAL inside EXISTS
SELECT lat_na.id FROM lat_na
WHERE EXISTS (
  SELECT 1 FROM lat_nb,
       LATERAL generate_series(1, lat_nb.id) g
  WHERE lat_nb.ref = lat_na.id AND g.g = lat_na.id)
ORDER BY lat_na.id;
 id 
----
  1
  2
  3
  4
(4 rows)

-- N11: LEFT LATERAL whose inner filter excludes everything
SELECT count(*) FROM lat_na
LEFT JOIN LATERAL (SELECT * FROM lat_nb
                   WHERE lat_nb.ref = lat_na.id AND lat_nb.val < 0) s
ON true;
 count 
-------
     4
(1 row)

-- N12: LATERAL refs a derived-table computed column under an aggregate.
-- Regression for the sibling-correlated outer-ref pruning bug in
-- CExpressionPreprocessor::PexprPruneUnusedComputedColsRecursive: without
-- propagating each relational child's outer refs into the required-cols set,
-- the outer ComputeScalar that defines `dv = val*2` gets pruned and the
-- inner Filter's CScalarIdent "dv" becomes dangling -> DXL-to-PlStmt
-- "Attribute number 8 not found".
EXPLAIN (COSTS OFF)
SELECT count(*) FROM (SELECT id, val*2 AS dv FROM lat_na) a,
  LATERAL (SELECT * FROM lat_nb WHERE lat_nb.id = a.dv) s;
                     QUERY PLAN                      
-----------------------------------------------------
 Aggregate
   ->  Hash Join
         Hash Cond: (((lat_na.val * 2)) = lat_nb.id)
         ->  Seq Scan on lat_na
         ->  Hash
               ->  Seq Scan on lat_nb
 Optimizer: pg_orca
(7 rows)

SELECT count(*) FROM (SELECT id, val*2 AS dv FROM lat_na) a,
  LATERAL (SELECT * FROM lat_nb WHERE lat_nb.id = a.dv) s;
 count 
-------
     0
(1 row)

-- =========================================================================
-- Edge cases.
-- =========================================================================
-- E1: varlevelsup = 2 (LATERAL nested inside a correlated scalar subquery)
SELECT lat_na.id,
  (SELECT count(*) FROM lat_nb,
                        LATERAL (SELECT * FROM lat_nc
                                 WHERE lat_nc.ref = lat_nb.id
                                   AND lat_nc.val > lat_na.val) c
   WHERE lat_nb.ref = lat_na.id) AS cnt
FROM lat_na
ORDER BY lat_na.id;
 id | cnt 
----+-----
  1 |   3
  2 |   3
  3 |   3
  4 |   3
(4 rows)

-- E2: LATERAL inside a CTE body. Regression for the InnerApply
-- PopCopyWithRemappedColumns nullptr crash: the CTE consumer inlines its
-- producer, which deep-copies the Apply expression with remapped columns.
-- LATERAL-derived Apply has no inner scalar colref, and the 2-arg ctor
-- asserts on size>0, so the copy path has to detect nullptr and fall back
-- to the 1-arg ctor.
WITH t AS (
  SELECT * FROM lat_na, LATERAL (SELECT * FROM lat_nb
                                 WHERE lat_nb.ref = lat_na.id) b
)
SELECT count(*) FROM t;
 count 
-------
     8
(1 row)

-- E3: VALUES + LATERAL
SELECT count(*) FROM (VALUES (1),(2),(3)) v(x),
  LATERAL (SELECT * FROM lat_nb WHERE lat_nb.ref = v.x) s;
 count 
-------
     6
(1 row)

-- E4: LATERAL + GROUP BY at outer
SELECT lat_na.id, sum(s.val)
FROM lat_na, LATERAL (SELECT * FROM lat_nb WHERE lat_nb.ref = lat_na.id) s
GROUP BY lat_na.id
ORDER BY lat_na.id;
 id | sum  
----+------
  1 |  600
  2 |  800
  3 | 1000
  4 | 1200
(4 rows)

-- E5: LATERAL + GROUPING SETS
SELECT lat_na.id, sum(s.val)
FROM lat_na, LATERAL (SELECT * FROM lat_nb WHERE lat_nb.ref = lat_na.id) s
GROUP BY GROUPING SETS ((lat_na.id), ())
ORDER BY lat_na.id NULLS LAST;
 id | sum  
----+------
  1 |  600
  2 |  800
  3 | 1000
  4 | 1200
    | 3600
(5 rows)

-- E6: LATERAL + window function
SELECT lat_na.id, s.val, sum(s.val) OVER (PARTITION BY lat_na.id)
FROM lat_na, LATERAL (SELECT * FROM lat_nb WHERE lat_nb.ref = lat_na.id) s
ORDER BY 1, 2;
 id | val | sum  
----+-----+------
  1 | 100 |  600
  1 | 500 |  600
  2 | 200 |  800
  2 | 600 |  800
  3 | 300 | 1000
  3 | 700 | 1000
  4 | 400 | 1200
  4 | 800 | 1200
(8 rows)

-- E7: UNION ALL inside the LATERAL body
SELECT count(*) FROM lat_na, LATERAL (
  SELECT id FROM lat_nb WHERE lat_nb.ref = lat_na.id
  UNION ALL
  SELECT id FROM lat_nc WHERE lat_nc.ref = lat_na.id
) s;
 count 
-------
    16
(1 row)

-- E8: LATERAL with DISTINCT outside
SELECT count(DISTINCT s.id)
FROM lat_na, LATERAL (SELECT * FROM lat_nb WHERE lat_nb.ref = lat_na.id) s;
 count 
-------
     8
(1 row)

-- E9: LATERAL top-N per outer (very common idiom)
SELECT lat_na.id, s.id, s.val FROM lat_na,
  LATERAL (SELECT * FROM lat_nb WHERE lat_nb.ref = lat_na.id
           ORDER BY lat_nb.val DESC LIMIT 1) s
ORDER BY lat_na.id;
 id | id | val 
----+----+-----
  1 |  5 | 500
  2 |  6 | 600
  3 |  7 | 700
  4 |  8 | 800
(4 rows)

-- E10: INSERT...SELECT with LATERAL
-- start_ignore
DROP TABLE IF EXISTS lat_ins;
-- end_ignore
CREATE TABLE lat_ins (aid int, bid int);
INSERT INTO lat_ins
  SELECT lat_na.id, s.id FROM lat_na,
    LATERAL (SELECT * FROM lat_nb WHERE lat_nb.ref = lat_na.id) s;
SELECT count(*) FROM lat_ins;
 count 
-------
     8
(1 row)

-- E11: 3-level LATERAL where the grandchild references the outermost
SELECT count(*) FROM lat_na,
  LATERAL (SELECT * FROM lat_nb,
                LATERAL (SELECT * FROM lat_nc
                         WHERE lat_nc.ref = lat_nb.id
                           AND lat_nc.val > lat_na.val*100) c
           WHERE lat_nb.ref = lat_na.id) bc;
 count 
-------
     8
(1 row)

-- E12: LATERAL unnest(array)
-- start_ignore
DROP TABLE IF EXISTS lat_arr;
-- end_ignore
CREATE TABLE lat_arr (id int, nums int[]);
INSERT INTO lat_arr VALUES (1, ARRAY[1,3,5]), (2, ARRAY[2,4,6]);
SELECT a.id, u FROM lat_arr a, LATERAL unnest(a.nums) AS u ORDER BY a.id, u;
 id | u 
----+---
  1 | 1
  1 | 3
  1 | 5
  2 | 2
  2 | 4
  2 | 6
(6 rows)

-- E13: zero-column VALUES in LATERAL (PG select.sql corner case).
-- Each row is a NIL list; translated as a dummy-column const table, must not
-- fall back or crash and must preserve the VALUES row count.
-- start_ignore
DROP TABLE IF EXISTS lat_nocols;
-- end_ignore
CREATE TABLE lat_nocols ();
INSERT INTO lat_nocols DEFAULT VALUES;
INSERT INTO lat_nocols DEFAULT VALUES;
EXPLAIN (COSTS OFF) SELECT * FROM lat_nocols n, LATERAL (VALUES (n.*)) v;
           QUERY PLAN           
--------------------------------
 Nested Loop
   Join Filter: true
   ->  Result
   ->  Seq Scan on lat_nocols n
 Optimizer: pg_orca
(5 rows)

SELECT * FROM lat_nocols n, LATERAL (VALUES (n.*)) v;
--
(2 rows)

SELECT count(*) FROM lat_nocols n, LATERAL (VALUES (n.*), (n.*), (n.*)) v;
 count 
-------
     6
(1 row)

-- cleanup
-- start_ignore
DROP TABLE lat_t1;
DROP TABLE lat_t2;
DROP TABLE lat_na;
DROP TABLE lat_nb;
DROP TABLE lat_nc;
DROP TABLE lat_ins;
DROP TABLE lat_arr;
DROP TABLE lat_nocols;
-- end_ignore