✔ PASS Three-Valued Logic in Anti-Joins task_04_three_valued_logic Scaled: 1M+ Records Ordered: No

Customer Anti-Join with Three-Valued Null Semantics

Candidate Latency (1M Rows)

44.67 ms

(1.1x faster than golden)

Golden Latency (1M Rows)

48.48 ms

DuckDB in-memory engine

Underlying Data Volume

3,000,000 rows

45.8 MiB across 3 tables

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 5.1s

✔ 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

Exposes silent data loss caused by SQL three-valued logic when models use NOT IN over subqueries containing NULLs.

⚡ The Frontier Trap (Why LLMs Fail)

In ANSI SQL three-valued logic, `x NOT IN (1, NULL)` evaluates to `UNKNOWN` rather than TRUE because SQL cannot guarantee x is not equal to the unknown value. Consequently, any query using `WHERE id NOT IN (SELECT id FROM ...)` where the subquery contains even a single NULL value evaluates to UNKNOWN for all rows, silently returning 0 results. Correct solutions require NOT EXISTS or LEFT JOIN ... WHERE ... IS NULL.

Prompt / User Request Given to Model

Find all customers who have NEVER filed a product return. Check against the `returns` table.

Required output columns:
- customer_id (INT)
- customer_name (VARCHAR)

Row order does not matter.

2. Underlying Tables, Row Counts & Data Volume

3 Tables (3,000,000 rows • 45.8 MiB)

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

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

customer_id (INTEGER) customer_name (VARCHAR)

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

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

📊 orders 1,000,000 rows 11.4 MiB Data Volume

order_id (INTEGER) customer_id (INTEGER) order_date (DATE)

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

#	order_id	customer_id	order_date
1	101	1	2025-01-01 00:00:00
2	102	2	2025-01-02 00:00:00
3	103	3	2025-01-03 00:00:00
4	103	3	2026-01-01 00:00:00
5	104	4	2026-01-02 00:00:00
6	105	5	2026-01-03 00:00:00
7	106	6	2026-01-04 00:00:00
8	107	7	2026-01-05 00:00:00
9	108	8	2026-01-06 00:00:00
10	109	9	2026-01-07 00:00:00
11	110	10	2026-01-08 00:00:00
12	111	11	2026-01-09 00:00:00
13	112	12	2026-01-10 00:00:00
14	113	13	2026-01-11 00:00:00
15	114	14	2026-01-12 00:00:00

📊 returns 1,000,000 rows 11.4 MiB Data Volume

return_id (INTEGER) order_id (INTEGER) customer_id (INTEGER)

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

#	return_id	order_id	customer_id
1	1	101	1
2	2	999	NULL
3	2	999	1
4	3	1,000	2
5	4	1,001	3
6	5	1,002	4
7	6	1,003	5
8	7	1,004	6
9	8	1,005	7
10	9	1,006	8
11	10	1,007	9
12	11	1,008	10
13	12	1,009	11
14	13	1,010	12
15	14	1,011	13

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: 73.09 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders

⚡ HASH_JOIN 🔥 84.4% Cost

Latency: 61.66 ms

Rows In/Out: 1

Data Vol: 20 B

🔍 TABLE_SCAN 14.8%

Latency: 10.79 ms

Rows In/Out: 1,000,000

Data Vol: 19.07 MB

📁 memory.main.customers

🔍 TABLE_SCAN 0.9%

Latency: 0.64 ms

Rows In/Out: 999,999

Data Vol: 3.81 MB

📁 memory.main."returns"

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
HASH_JOIN	84.4%	61.66 ms	1	0.00 MB	-	🔥 BOTTLENECK
TABLE_SCAN	14.8%	10.79 ms	1,000,000	19.07 MB	memory.main.customers	Normal
TABLE_SCAN	0.9%	0.64 ms	999,999	3.81 MB	memory.main."returns"	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

SELECT c.customer_id,
       c.customer_name
FROM customers AS c
WHERE NOT EXISTS
    (SELECT 1
     FROM RETURNS AS r
     WHERE r.customer_id = c.customer_id);

SELECT c.customer_id,
       c.customer_name
FROM customers c
WHERE NOT EXISTS
    (SELECT 1
     FROM RETURNS r
     WHERE r.customer_id = c.customer_id);

CREATE TABLE customers (customer_id INT, customer_name VARCHAR);


CREATE TABLE orders (order_id INT, customer_id INT, order_date DATE);


CREATE TABLE RETURNS (return_id INT, order_id INT, customer_id INT);


INSERT INTO customers
VALUES (1, 'Alice Smith'),
       (2, 'Bob Jones'),
       (3, 'Charlie Brown');


INSERT INTO orders
VALUES (101, 1, '2025-01-01'),
       (102, 2, '2025-01-02'),
       (103, 3, '2025-01-03');

-- Alice returned order 101. An anonymous/guest return has customer_id = NULL

INSERT INTO RETURNS
VALUES (1, 101, 1),
       (2, 999, NULL);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (1 rows × 2 cols)

#	customer_id	customer_name
1	999,999	Alice Smith

Expected Golden Output (1 rows × 2 cols)

#	customer_id	customer_name
1	999,999	Alice Smith

← Prev: Zero-Shot Query Intent Cl... ⊞ Back to Benchmark Dashboard Next: Contrastive Triplet Evalu... →