-- Tests for SubPlan (unflattened subquery) pushdown.
--
-- These cover the planner residue that pull_up_sublinks CANNOT convert to
-- joins: correlated scalar subqueries, uncorrelated scalar subqueries in
-- WHERE/HAVING, IN-subqueries with GROUP BY/HAVING, and NOT IN. Each shape
-- pins the deparsed Remote SQL via EXPLAIN and then executes to validate the
-- results.
--
-- SubPlan pushdown is gated on ClickHouse 25.8+ (older analyzers reject the
-- correlated / NOT IN SQL we generate), so the whole test aborts on older
-- servers rather than carry version-specific expected output.
SET datestyle = 'ISO';
CREATE SERVER subplan_admin FOREIGN DATA WRAPPER clickhouse_fdw;
CREATE USER MAPPING FOR CURRENT_USER SERVER subplan_admin;
SELECT clickhouse_server_version('subplan_admin') AS ch_version \gset
SELECT (split_part(:'ch_version', '.', 1)::int,
        split_part(:'ch_version', '.', 2)::int) < (25, 8) AS no_ch258 \gset
\if :no_ch258
\echo 'SKIP: SubPlan pushdown requires ClickHouse 25.8 or higher'
\quit
\endif
CREATE SERVER subplan_svr FOREIGN DATA WRAPPER clickhouse_fdw
    OPTIONS(dbname 'subplan_test', driver 'binary');
CREATE USER MAPPING FOR CURRENT_USER SERVER subplan_svr;
CALL clickhouse_perform('subplan_admin', 'DROP DATABASE IF EXISTS subplan_test');
CALL clickhouse_perform('subplan_admin', 'CREATE DATABASE subplan_test');
CALL clickhouse_perform('subplan_admin', 'CREATE TABLE subplan_test.items
    (item_id Int32, grp Int32, price Decimal(15,2), qty Int32)
    ENGINE = MergeTree ORDER BY item_id');
CALL clickhouse_perform('subplan_admin', 'CREATE TABLE subplan_test.sales
    (sale_id Int32, item_id Int32, amount Decimal(15,2), region String)
    ENGINE = MergeTree ORDER BY sale_id');
CALL clickhouse_perform('subplan_admin', $$
    INSERT INTO subplan_test.items VALUES
    (1, 1, 10.00, 5), (2, 1, 20.00, 3), (3, 1, 30.00, 8),
    (4, 2, 15.00, 2), (5, 2, 25.00, 7), (6, 3, 50.00, 1)
$$);
-- Sale 8 distinguishes correct correlation from inner-scope capture: under
-- correct per-item correlation its group (item 1) has avg 166.67 so the
-- 1.5x threshold is 250.00 and sale 8 (250.00) does NOT qualify; under a
-- capture bug the threshold collapses to the global 1.5*avg = 241.88 and
-- sale 8 WOULD qualify.
CALL clickhouse_perform('subplan_admin', $$
    INSERT INTO subplan_test.sales VALUES
    (1, 1, 100.00, 'east'), (2, 1, 150.00, 'west'),
    (3, 2, 200.00, 'east'), (4, 3, 120.00, 'east'),
    (5, 4,  80.00, 'west'), (6, 5, 300.00, 'east'),
    (7, 5,  90.00, 'west'), (8, 1, 250.00, 'east')
$$);
CREATE SCHEMA subplan_test;
IMPORT FOREIGN SCHEMA subplan_test FROM SERVER subplan_svr INTO subplan_test;
SET SESSION search_path = subplan_test,public;
-- ============================================================
-- 1. Uncorrelated scalar subquery (TPC-H Q22 shape)
-- ============================================================
EXPLAIN (VERBOSE, COSTS OFF)
SELECT item_id, price FROM items
WHERE price > (SELECT avg(price) FROM items)
ORDER BY item_id;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on subplan_test.items
   Output: items.item_id, items.price
   Remote SQL: SELECT item_id, price FROM subplan_test.items WHERE ((price > {p1:Decimal})) ORDER BY item_id ASC NULLS LAST
   InitPlan 1
     ->  Foreign Scan
           Output: (avg(items_1.price))
           Relations: Aggregate on (items)
           Remote SQL: SELECT avg(price) FROM subplan_test.items
(8 rows)

SELECT item_id, price FROM items
WHERE price > (SELECT avg(price) FROM items)
ORDER BY item_id;
 item_id | price 
---------+-------
       3 | 30.00
       6 | 50.00
(2 rows)

-- ============================================================
-- 2. Correlated scalar subquery (TPC-H Q2/Q17 shape)
-- ============================================================
EXPLAIN (VERBOSE, COSTS OFF)
SELECT s.sale_id, s.amount FROM sales s
WHERE s.amount > (SELECT 1.5 * avg(s2.amount) FROM sales s2
                  WHERE s2.item_id = s.item_id)
ORDER BY s.sale_id;
                                                                                                          QUERY PLAN                                                                                                           
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on subplan_test.sales s
   Output: s.sale_id, s.amount
   Remote SQL: SELECT sale_id, amount FROM subplan_test.sales r1 WHERE ((r1.amount > (SELECT (1.5 * avg(q1_1.amount)) FROM subplan_test.sales q1_1 WHERE ((q1_1.item_id = (r1.item_id)))))) ORDER BY r1.sale_id ASC NULLS LAST
   SubPlan 1
     ->  Foreign Scan
           Output: ((1.5 * avg(s2.amount)))
           Relations: Aggregate on (sales s2)
           Remote SQL: SELECT (1.5 * avg(amount)) FROM subplan_test.sales WHERE ((item_id = {p1:Int32}))
(8 rows)

SELECT s.sale_id, s.amount FROM sales s
WHERE s.amount > (SELECT 1.5 * avg(s2.amount) FROM sales s2
                  WHERE s2.item_id = s.item_id)
ORDER BY s.sale_id;
 sale_id | amount 
---------+--------
       6 | 300.00
(1 row)

-- ============================================================
-- 3. Correlated scalar against a joined outer (Q2's exact shape:
--    correlation reaches a DIFFERENT outer table than the compared column)
-- ============================================================
EXPLAIN (VERBOSE, COSTS OFF)
SELECT i.item_id, s.amount FROM items i, sales s
WHERE i.item_id = s.item_id
  AND s.amount = (SELECT max(s2.amount) FROM sales s2
                  WHERE s2.item_id = i.item_id)
ORDER BY i.item_id;
                                                                                                                                             QUERY PLAN                                                                                                                                             
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: i.item_id, s.amount
   Relations: (items i) INNER JOIN (sales s)
   Remote SQL: SELECT r1.item_id, r2.amount FROM  subplan_test.items r1 ALL INNER JOIN subplan_test.sales r2 ON (((r1.item_id = r2.item_id))) WHERE (((SELECT max(q1_1.amount) FROM subplan_test.sales q1_1 WHERE ((q1_1.item_id = (r1.item_id)))) = r2.amount)) ORDER BY r1.item_id ASC NULLS LAST
(4 rows)

SELECT i.item_id, s.amount FROM items i, sales s
WHERE i.item_id = s.item_id
  AND s.amount = (SELECT max(s2.amount) FROM sales s2
                  WHERE s2.item_id = i.item_id)
ORDER BY i.item_id;
 item_id | amount 
---------+--------
       1 | 250.00
       2 | 200.00
       3 | 120.00
       4 |  80.00
       5 | 300.00
(5 rows)

-- ============================================================
-- 4. IN subquery with GROUP BY + HAVING (TPC-H Q18 shape)
--    HAVING blocks semijoin conversion, so this stays a SubPlan.
-- ============================================================
EXPLAIN (VERBOSE, COSTS OFF)
SELECT item_id, grp FROM items
WHERE item_id IN (SELECT item_id FROM sales
                  GROUP BY item_id HAVING sum(amount) > 150.00)
ORDER BY item_id;
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Merge Join
   Output: items.item_id, items.grp
   Inner Unique: true
   Merge Cond: (items.item_id = sales.item_id)
   ->  Foreign Scan on subplan_test.items
         Output: items.item_id, items.grp, items.price, items.qty
         Remote SQL: SELECT item_id, grp FROM subplan_test.items ORDER BY item_id ASC NULLS LAST
   ->  GroupAggregate
         Output: sales.item_id
         Group Key: sales.item_id
         Filter: (sum(sales.amount) > 150.00)
         ->  Foreign Scan on subplan_test.sales
               Output: sales.sale_id, sales.item_id, sales.amount, sales.region
               Remote SQL: SELECT item_id, amount FROM subplan_test.sales ORDER BY item_id ASC NULLS LAST
(14 rows)

SELECT item_id, grp FROM items
WHERE item_id IN (SELECT item_id FROM sales
                  GROUP BY item_id HAVING sum(amount) > 150.00)
ORDER BY item_id;
 item_id | grp 
---------+-----
       1 |   1
       2 |   1
       5 |   2
(3 rows)

-- ============================================================
-- 5. NOT IN (TPC-H Q16 shape) — arrives as NOT(ANY-SubPlan)
-- ============================================================
EXPLAIN (VERBOSE, COSTS OFF)
SELECT item_id FROM items
WHERE item_id NOT IN (SELECT item_id FROM sales WHERE region = 'east')
ORDER BY item_id;
                                                                                                 QUERY PLAN                                                                                                 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on subplan_test.items
   Output: items.item_id
   Remote SQL: SELECT item_id FROM subplan_test.items r1 WHERE ((NOT (r1.item_id IN (SELECT q1_1.item_id FROM subplan_test.sales q1_1 WHERE ((q1_1.region = 'east')))))) ORDER BY r1.item_id ASC NULLS LAST
   SubPlan 1
     ->  Foreign Scan on subplan_test.sales
           Output: sales.item_id
           Remote SQL: SELECT item_id FROM subplan_test.sales WHERE ((region = 'east'))
(7 rows)

SELECT item_id FROM items
WHERE item_id NOT IN (SELECT item_id FROM sales WHERE region = 'east')
ORDER BY item_id;
 item_id 
---------
       4
       6
(2 rows)

-- ============================================================
-- 6. Scalar subquery in HAVING (TPC-H Q11 shape)
-- ============================================================
EXPLAIN (VERBOSE, COSTS OFF)
SELECT grp, sum(price * qty) AS value FROM items
GROUP BY grp
HAVING sum(price * qty) > (SELECT sum(price * qty) * 0.2 FROM items)
ORDER BY value DESC;
                                                                                 QUERY PLAN                                                                                  
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: items.grp, (sum((items.price * (items.qty)::numeric)))
   Relations: Aggregate on (items)
   Remote SQL: SELECT grp, sum((price * qty)) FROM subplan_test.items GROUP BY grp HAVING ((sum((price * qty)) > {p1:Decimal})) ORDER BY sum((price * qty)) DESC NULLS FIRST
   InitPlan 1
     ->  Foreign Scan
           Output: ((sum((items_1.price * (items_1.qty)::numeric)) * 0.2))
           Relations: Aggregate on (items)
           Remote SQL: SELECT (sum((price * qty)) * 0.2) FROM subplan_test.items
(9 rows)

SELECT grp, sum(price * qty) AS value FROM items
GROUP BY grp
HAVING sum(price * qty) > (SELECT sum(price * qty) * 0.2 FROM items)
ORDER BY value DESC;
 grp | value  
-----+--------
   1 | 350.00
   2 | 205.00
(2 rows)

-- ============================================================
-- 7. Negative case: nested SubPlan must NOT push down (stays local)
-- ============================================================
EXPLAIN (VERBOSE, COSTS OFF)
SELECT item_id FROM items
WHERE price > (SELECT avg(price) FROM items
               WHERE qty > (SELECT avg(qty) FROM items))
ORDER BY item_id;
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Foreign Scan on subplan_test.items
   Output: items.item_id
   Remote SQL: SELECT item_id FROM subplan_test.items WHERE ((price > {p1:Decimal})) ORDER BY item_id ASC NULLS LAST
   InitPlan 2
     ->  Foreign Scan
           Output: (avg(items_2.price))
           Relations: Aggregate on (items)
           Remote SQL: SELECT avg(price) FROM subplan_test.items WHERE ((qty > {p1:Decimal}))
           InitPlan 1
             ->  Foreign Scan
                   Output: (avg(items_1.qty))
                   Relations: Aggregate on (items)
                   Remote SQL: SELECT avg(qty) FROM subplan_test.items
(13 rows)

SELECT item_id FROM items
WHERE price > (SELECT avg(price) FROM items
               WHERE qty > (SELECT avg(qty) FROM items))
ORDER BY item_id;
 item_id 
---------
       3
       5
       6
(3 rows)

-- ============================================================
-- 8. Negative case: multi-row correlated scalar (no aggregate) must
--    NOT push down — zero-row semantics differ between PG and CH.
-- ============================================================
EXPLAIN (VERBOSE, COSTS OFF)
SELECT s.sale_id FROM sales s
WHERE s.amount = (SELECT s2.amount FROM sales s2
                  WHERE s2.sale_id = s.sale_id + 1)
ORDER BY s.sale_id;
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Foreign Scan on subplan_test.sales s
   Output: s.sale_id
   Filter: (s.amount = (SubPlan 1))
   Remote SQL: SELECT sale_id, amount FROM subplan_test.sales ORDER BY sale_id ASC NULLS LAST
   SubPlan 1
     ->  Foreign Scan on subplan_test.sales s2
           Output: s2.amount
           Remote SQL: SELECT amount FROM subplan_test.sales WHERE ((sale_id = ({p1:Int32} + 1)))
(8 rows)

-- ============================================================
-- 9. Identity: the plan-time version probe must connect as the user the
--    executor will scan as — the view owner, via the RTE's checkAsUser —
--    not the invoker. regress_subplan_nomap has NO user mapping of its
--    own, so resolving the invoker instead would fail the EXPLAIN with
--    "user mapping not found".
-- ============================================================
CREATE ROLE regress_subplan_nomap;
GRANT USAGE ON SCHEMA subplan_test TO regress_subplan_nomap;
CREATE VIEW sales_v AS SELECT * FROM sales;
GRANT SELECT ON sales_v TO regress_subplan_nomap;
SET ROLE regress_subplan_nomap;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT v.sale_id, v.amount FROM sales_v v
WHERE v.amount > (SELECT 1.5 * avg(v2.amount) FROM sales_v v2
                  WHERE v2.item_id = v.item_id)
ORDER BY v.sale_id;
                                                                                                          QUERY PLAN                                                                                                           
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on subplan_test.sales
   Output: sales.sale_id, sales.amount
   Remote SQL: SELECT sale_id, amount FROM subplan_test.sales r2 WHERE ((r2.amount > (SELECT (1.5 * avg(q1_2.amount)) FROM subplan_test.sales q1_2 WHERE ((q1_2.item_id = (r2.item_id)))))) ORDER BY r2.sale_id ASC NULLS LAST
   SubPlan 1
     ->  Foreign Scan
           Output: ((1.5 * avg(sales_1.amount)))
           Relations: Aggregate on (sales)
           Remote SQL: SELECT (1.5 * avg(amount)) FROM subplan_test.sales WHERE ((item_id = {p1:Int32}))
(8 rows)

SELECT v.sale_id, v.amount FROM sales_v v
WHERE v.amount > (SELECT 1.5 * avg(v2.amount) FROM sales_v v2
                  WHERE v2.item_id = v.item_id)
ORDER BY v.sale_id;
 sale_id | amount 
---------+--------
       6 | 300.00
(1 row)

RESET ROLE;
DROP VIEW sales_v;
DROP OWNED BY regress_subplan_nomap;
DROP ROLE regress_subplan_nomap;
-- ============================================================
-- 10. NULL semantics: ClickHouse evaluates IN under two-valued
--     logic, so a native x NOT IN (SELECT ..) diverges whenever a
--     NULL can reach the comparison: Postgres computes NULL (row
--     dropped) where ClickHouse returns TRUE. Shape 5 above ships
--     plain because both item_id columns import as NOT NULL; with a
--     nullable column on either side the deparse compensates with
--     guards — a probe-NULL CASE (empty set is TRUE, else dropped)
--     and a NOT EXISTS poison check for NULLs in the set — each
--     emitted only when the corresponding proof fails.
-- ============================================================
CALL clickhouse_perform('subplan_admin', 'CREATE TABLE subplan_test.maybe_null
    (id Int32, val Nullable(Int32)) ENGINE = MergeTree ORDER BY id');
CALL clickhouse_perform('subplan_admin', $$
    INSERT INTO subplan_test.maybe_null VALUES (1, 1), (2, 2), (3, NULL), (4, 7)
$$);
CREATE FOREIGN TABLE maybe_null (id int NOT NULL, val int)
    SERVER subplan_svr OPTIONS (table_name 'maybe_null');
-- Nullable inner column, NOT NULL probe: ships as NOT IN plus the poison
-- guard (no CASE). Postgres returns no rows — the set holds a NULL — where
-- an unguarded pushdown would return items 4..6
EXPLAIN (VERBOSE, COSTS OFF)
SELECT item_id FROM items
WHERE item_id NOT IN (SELECT val FROM maybe_null)
ORDER BY item_id;
                                                                                                                           QUERY PLAN                                                                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on subplan_test.items
   Output: items.item_id
   Remote SQL: SELECT item_id FROM subplan_test.items r1 WHERE ((r1.item_id NOT IN (SELECT q1_1.val FROM subplan_test.maybe_null q1_1) AND NOT EXISTS (SELECT 1 FROM subplan_test.maybe_null q1_1 WHERE (q1_1.val IS NULL)))) ORDER BY r1.item_id ASC NULLS LAST
   SubPlan 1
     ->  Foreign Scan on subplan_test.maybe_null
           Output: maybe_null.val
           Remote SQL: SELECT val FROM subplan_test.maybe_null
(7 rows)

SELECT item_id FROM items
WHERE item_id NOT IN (SELECT val FROM maybe_null)
ORDER BY item_id;
 item_id 
---------
(0 rows)

-- Nullable probe, NOT NULL inner column: ships as the probe-NULL CASE with
-- no poison guard (east sales are items {1,2,3,5}, so only val = 7
-- qualifies; val IS NULL must not)
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM maybe_null
WHERE val NOT IN (SELECT item_id FROM sales WHERE region = 'east')
ORDER BY id;
                                                                                                                                                               QUERY PLAN                                                                                                                                                                
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on subplan_test.maybe_null
   Output: maybe_null.id
   Remote SQL: SELECT id FROM subplan_test.maybe_null r1 WHERE ((CASE WHEN (r1.val) IS NULL THEN NOT EXISTS (SELECT q1_1.item_id FROM subplan_test.sales q1_1 WHERE ((q1_1.region = 'east'))) ELSE (r1.val NOT IN (SELECT q1_1.item_id FROM subplan_test.sales q1_1 WHERE ((q1_1.region = 'east')))) END)) ORDER BY r1.id ASC NULLS LAST
   SubPlan 1
     ->  Foreign Scan on subplan_test.sales
           Output: sales.item_id
           Remote SQL: SELECT item_id FROM subplan_test.sales WHERE ((region = 'east'))
(7 rows)

SELECT id FROM maybe_null
WHERE val NOT IN (SELECT item_id FROM sales WHERE region = 'east')
ORDER BY id;
 id 
----
  4
(1 row)

-- Both sides nullable: the full form, CASE and poison guard together.
-- The set holds a NULL, so no probe — NULL included — can qualify
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM maybe_null m
WHERE m.val NOT IN (SELECT val FROM maybe_null)
ORDER BY id;
                                                                                                                                                                            QUERY PLAN                                                                                                                                                                            
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on subplan_test.maybe_null m
   Output: m.id
   Remote SQL: SELECT id FROM subplan_test.maybe_null r1 WHERE ((CASE WHEN (r1.val) IS NULL THEN NOT EXISTS (SELECT q1_1.val FROM subplan_test.maybe_null q1_1) ELSE (r1.val NOT IN (SELECT q1_1.val FROM subplan_test.maybe_null q1_1) AND NOT EXISTS (SELECT 1 FROM subplan_test.maybe_null q1_1 WHERE (q1_1.val IS NULL))) END)) ORDER BY r1.id ASC NULLS LAST
   SubPlan 1
     ->  Foreign Scan on subplan_test.maybe_null
           Output: maybe_null.val
           Remote SQL: SELECT val FROM subplan_test.maybe_null
(7 rows)

SELECT id FROM maybe_null m
WHERE m.val NOT IN (SELECT val FROM maybe_null)
ORDER BY id;
 id 
----
(0 rows)

-- Positive IN over the same nullable column diverges only toward FALSE,
-- which a WHERE treats like the NULL Postgres computes: still ships
EXPLAIN (VERBOSE, COSTS OFF)
SELECT item_id FROM items
WHERE item_id IN (SELECT val FROM maybe_null)
ORDER BY item_id;
                                                                              QUERY PLAN                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: items.item_id
   Relations: (items) LEFT SEMI JOIN (maybe_null)
   Remote SQL: SELECT r1.item_id FROM  subplan_test.items r1 LEFT SEMI JOIN subplan_test.maybe_null r3 ON (((r1.item_id = r3.val))) ORDER BY r1.item_id ASC NULLS LAST
(4 rows)

SELECT item_id FROM items
WHERE item_id IN (SELECT val FROM maybe_null)
ORDER BY item_id;
 item_id 
---------
       1
       2
(2 rows)

DROP FOREIGN TABLE maybe_null;
-- Cleanup
SET SESSION search_path = public;
DROP SCHEMA subplan_test CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to foreign table subplan_test.items
drop cascades to foreign table subplan_test.sales
DROP USER MAPPING FOR CURRENT_USER SERVER subplan_svr;
DROP SERVER subplan_svr CASCADE;
CALL clickhouse_perform('subplan_admin', 'DROP DATABASE subplan_test');