✔ PASS Warehouse & Statistical task_090_Q90_three_valued_logic_safe_anti_join Scaled: 1M+ Records Ordered: Yes

Three-Valued Logic Safe Anti-Join (Eliminating the NOT IN NULL Trap)

Candidate Latency (1M Rows)

52.48 ms

(1.1x slower than golden)

Golden Latency (1M Rows)

49.69 ms

DuckDB in-memory engine

Underlying Data Volume

2,000,000 rows

26.7 MiB across 2 tables

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 4.9s

✔ Correctness Verified on 1M+ Records

Candidate SQL output matches ground truth golden query across all multiset rows and numeric tolerances (tol=1e-3). Zero data skew or anti-patterns detected on full dataset scale.

1. Analytical Significance & What It Means

Scenario & Frontier Trap

Business Scenario & Problem

Marketing queries to find active customers who have not churned often use `WHERE id NOT IN (SELECT customer_id FROM churned_users)`. If a single `NULL` exists in `churned_users`, SQL three-valued logic causes the entire query to return ZERO rows.

⚡ The Frontier Trap (Why LLMs Fail)

In SQL, `val NOT IN (1, NULL)` evaluates to `UNKNOWN`, which evaluates to `FALSE` in WHERE clauses, silently discarding every single record.

Prompt / User Request Given to Model

Marketing queries to find active customers who have not churned often use `WHERE id NOT IN (SELECT customer_id FROM churned_users)`. If a single `NULL` exists in `churned_users`, SQL three-valued logic causes the entire query to return ZERO rows.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: customer_id, name.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

2 Tables (2,000,000 rows • 26.7 MiB)

Production-scale fixtures loaded into the isolated DuckDB sandbox with at least 1,000,000 records per table:

📊 all_customers 1,000,000 rows 22.9 MiB Data Volume

customer_id (INTEGER) name (VARCHAR)

Data Preview (showing sample rows from 1,000,000 total rows):

#	customer_id	name
1	1	Alice
2	2	Bob
3	3	Charlie
4	3	Alice
5	4	Bob
6	5	Charlie
7	6	Alice
8	7	Bob
9	8	Charlie
10	9	Alice
11	10	Bob
12	11	Charlie
13	12	Alice
14	13	Bob
15	14	Charlie

📊 churned_customers 1,000,000 rows 3.8 MiB Data Volume

customer_id (INTEGER)

Data Preview (showing sample rows from 1,000,000 total rows):

#	customer_id
1	2
2	NULL
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14

3. Graphical Query Execution Plan & Cost Bottlenecks

Physical Operator Tree (DuckDB 2.0 Profiler)

Visual flowchart of the physical execution pipeline. Operators with high relative costs (≥ 20% of query runtime or heavy latency) are visually highlighted:

Pipeline Latency Distribution (Total Plan Time: 78.13 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders

📐 PROJECTION 0.0%

Latency: 0.00 ms

Rows In/Out: 1

Data Vol: 20 B

📁 Projections: ['#0', '__internal_...

⏳ ORDER_BY 0.1%

Latency: 0.10 ms

Rows In/Out: 1

Data Vol: 12 B

📐 PROJECTION 0.0%

Latency: 0.01 ms

Rows In/Out: 1

Data Vol: 12 B

📁 Projections: ['#0', '__internal_...

⚡ HASH_JOIN 🔥 83.1% Cost

Latency: 64.96 ms

Rows In/Out: 1

Data Vol: 20 B

🔍 TABLE_SCAN 15.7%

Latency: 12.27 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

📁 memory.main.all_customers

🔍 TABLE_SCAN 1.0%

Latency: 0.79 ms

Rows In/Out: 999,999

Data Vol: 3.81 MB

📁 memory.main.churned_customers

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
HASH_JOIN	83.1%	64.96 ms	1	0.00 MB	-	🔥 BOTTLENECK
TABLE_SCAN	15.7%	12.27 ms	1,000,000	19.07 MB	memory.main.all_customers	Normal
TABLE_SCAN	1.0%	0.79 ms	999,999	3.81 MB	memory.main.churned_customers	Normal
ORDER_BY	0.1%	0.10 ms	1	0.00 MB	-	Normal
PROJECTION	0.0%	0.01 ms	1	0.00 MB	Projections: ['#0', '__internal_compress_string_	Normal
PROJECTION	0.0%	0.00 ms	1	0.00 MB	Projections: ['#0', '__internal_decompress_strin	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

SELECT c.customer_id,
       c.name
FROM all_customers AS c
WHERE NOT EXISTS
    (SELECT 1
     FROM churned_customers AS ch
     WHERE ch.customer_id = c.customer_id)
ORDER BY c.customer_id;

-- Robust anti-join that is immune to NULL records in the subquery

SELECT c.customer_id,
       c.name
FROM all_customers c
WHERE NOT EXISTS
    (SELECT 1
     FROM churned_customers ch
     WHERE ch.customer_id = c.customer_id)
ORDER BY c.customer_id;

CREATE TABLE all_customers (customer_id INT, name VARCHAR);


CREATE TABLE churned_customers (customer_id INT);


INSERT INTO all_customers
VALUES (1, 'Alice'),
       (2, 'Bob'),
       (3, 'Charlie');

-- Note the presence of a NULL record in the churned table!

INSERT INTO churned_customers
VALUES (2),
       (NULL);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (1 rows × 2 cols)

#	customer_id	name
1	1	Alice

Expected Golden Output (1 rows × 2 cols)

#	customer_id	name
1	1	Alice

← Prev: SARGable Temporal Partiti... ⊞ Back to Benchmark Dashboard Next: Dynamic PIVOT of Monthly ... →