-- Dynamic Filter Test: Hash Join
-- Tests dynamic filters generated by hash join

-- Test 1.1: Basic Hash Join
CREATE TABLE orders ("id" INT, customer_id INT, amount DOUBLE, ts TIMESTAMP TIME INDEX);
CREATE TABLE customers (customer_id INT, "name" STRING, tier STRING, ts TIMESTAMP TIME INDEX);

-- Insert sufficient test data
INSERT INTO orders VALUES
  (1, 1, 100.0, '2024-01-01 00:00:00'),
  (2, 2, 200.0, '2024-01-02 00:00:00'),
  (3, 1, 150.0, '2024-01-03 00:00:00');
-- Insert more rows to trigger dynamic filter

INSERT INTO customers VALUES
  (1, 'Alice', 'gold', '2024-01-01 00:00:00'),
  (2, 'Bob', 'silver', '2024-01-02 00:00:00');

-- Test: Basic hash join should produce dynamic filter
-- The hash join will build hash table from customers (small table)
-- and probe with orders (larger table), potentially using dynamic filter
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE (partitioning.*) REDACTED
EXPLAIN SELECT o."id", o.amount, c."name", c.tier
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.tier = 'gold';

-- SQLNESS REPLACE ("metrics_per_partition":\s*.*metrics=) "metrics_per_partition": REDACTED metrics=
-- SQLNESS REPLACE (metrics=\{.*\}) metrics=REDACTED
-- SQLNESS REPLACE (metrics=\[[^\]]*\]) metrics=REDACTED
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
-- SQLNESS REPLACE (=Hash.*) =REDACTED
-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE "(file_id|time_range_start|time_range_end)":"[^"]+" "$1":"REDACTED"
-- SQLNESS REPLACE ("[a-z_]+":"[0-9\.]+(ns|us|µs|ms|s)") "DURATION": REDACTED
-- SQLNESS REPLACE "(size|flat_format)":\s*(\d+|true|false) "$1":REDACTED
-- SQLNESS REPLACE ,\s*filter=.*?metrics=  metrics=
-- SQLNESS REPLACE Total\s+rows:\s+\d+ Total rows: REDACTED
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
-- SQLNESS REPLACE "partition_count":\{(.*?)\} "partition_count":REDACTED
-- SQLNESS REPLACE ,\s"dyn_filters":\s\["DynamicFilter\s\[[^"]*\]"\] , "dyn_filters": ["DynamicFilter [ REDACTED ]"]
-- SQLNESS REPLACE metrics=REDACTED\s*\| metrics=REDACTED_|
EXPLAIN ANALYZE VERBOSE SELECT o."id", o.amount, c."name", c.tier
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.tier = 'gold';

-- Verify correctness
-- SQLNESS SORT_RESULT
SELECT o."id", o.amount, c."name", c.tier
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.tier = 'gold';

DROP TABLE orders;
DROP TABLE customers;

-- Test 1.2: Hash Join with Projection Alias
CREATE TABLE orders ("id" INT, customer_id INT, amount DOUBLE, ts TIMESTAMP TIME INDEX);
CREATE TABLE customers (customer_id INT, "name" STRING, tier STRING, ts TIMESTAMP TIME INDEX);

-- Insert test data
INSERT INTO orders VALUES
  (1, 1, 100.0, '2024-01-01 00:00:00'),
  (2, 2, 200.0, '2024-01-02 00:00:00'),
  (3, 1, 150.0, '2024-01-03 00:00:00'),
  (4, 3, 300.0, '2024-01-04 00:00:00');

INSERT INTO customers VALUES
  (1, 'Alice', 'gold', '2024-01-01 00:00:00'),
  (2, 'Bob', 'silver', '2024-01-02 00:00:00'),
  (3, 'Charlie', 'bronze', '2024-01-03 00:00:00');

-- Test: Hash join with projection alias
-- The alias on customer_id tests that dynamic filter correctly handles column references
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE (partitioning.*) REDACTED
EXPLAIN SELECT o."id", o.amount, c."name", c.tier
FROM (SELECT "id", customer_id as cid, amount, ts FROM orders) o
JOIN customers c ON o.cid = c.customer_id
WHERE c.tier IN ('gold', 'silver');

-- SQLNESS REPLACE ("metrics_per_partition":\s*.*metrics=) "metrics_per_partition": REDACTED metrics=
-- SQLNESS REPLACE (metrics=\{.*\}) metrics=REDACTED
-- SQLNESS REPLACE (metrics=\[[^\]]*\]) metrics=REDACTED
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
-- SQLNESS REPLACE (=Hash.*) =REDACTED
-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE "(file_id|time_range_start|time_range_end)":"[^"]+" "$1":"REDACTED"
-- SQLNESS REPLACE ("[a-z_]+":"[0-9\.]+(ns|us|µs|ms|s)") "DURATION": REDACTED
-- SQLNESS REPLACE "(size|flat_format)":\s*(\d+|true|false) "$1":REDACTED
-- SQLNESS REPLACE ,\s*filter=.*?metrics=  metrics=
-- SQLNESS REPLACE Total\s+rows:\s+\d+ Total rows: REDACTED
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
-- SQLNESS REPLACE "partition_count":\{(.*?)\} "partition_count":REDACTED
-- SQLNESS REPLACE ,\s"dyn_filters":\s\["DynamicFilter\s\[[^"]*\]"\] , "dyn_filters": ["DynamicFilter [ REDACTED ]"]
-- SQLNESS REPLACE metrics=REDACTED\s*\| metrics=REDACTED_|
EXPLAIN ANALYZE VERBOSE SELECT o."id", o.amount, c."name", c.tier
FROM (SELECT "id", customer_id as cid, amount, ts FROM orders) o
JOIN customers c ON o.cid = c.customer_id
WHERE c.tier IN ('gold', 'silver');

-- Verify correctness
-- SQLNESS SORT_RESULT
SELECT o."id", o.amount, c."name", c.tier
FROM (SELECT "id", customer_id as cid, amount, ts FROM orders) o
JOIN customers c ON o.cid = c.customer_id
WHERE c.tier IN ('gold', 'silver');

DROP TABLE orders;
DROP TABLE customers;
