SQL-FRONTIER Benchmark Deep-Dive / Warehouse & Statistical / task_089_Q89_chasm_trap_fanout_resolution
← Prev: Transactional State Machi... ⊞ All 120 Queries Next: SARGable Temporal Partiti... →
✔ PASS Warehouse & Statistical task_089_Q89_chasm_trap_fanout_resolution Scaled: 1M+ Records Ordered: No

Chasm Trap Resolution: Multi-Table 1:N Aggregation without Fan-Out Inflation

Candidate Latency (1M Rows)
127.44 ms
(1.0x faster than golden)
Golden Latency (1M Rows)
133.52 ms
DuckDB in-memory engine
Underlying Data Volume
1,002,000 rows
22.9 MiB across 3 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

When an order has multiple discounts AND multiple shipment records, joining both child tables directly to the order creates a cross product that multiplies sales figures by 4x. Pre-aggregating each child table in CTEs before joining preserves financial integrity.

⚡ The Frontier Trap (Why LLMs Fail)

Frontier models routinely join all tables before GROUP BY, causing catastrophic silent metric corruption.

Prompt / User Request Given to Model
When an order has multiple discounts AND multiple shipment records, joining both child tables directly to the order creates a cross product that multiplies sales figures by 4x. Pre-aggregating each child table in CTEs before joining preserves financial integrity.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: customer_id, total_gross_sales, total_discount_amount, total_shipping_fees, net_revenue.
Row order does not matter.

2. Underlying Tables, Row Counts & Data Volume

3 Tables (1,002,000 rows • 22.9 MiB)

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

📊 order_discounts 1,000 rows 23.4 KiB Data Volume
discount_id (INTEGER) order_id (INTEGER) amount (DECIMAL(10,2))
Data Preview (showing sample rows from 1,000 total rows):
# discount_id order_id amount
1 1 101 10
2 2 101 5
3 2 101 4.22
4 3 102 4.18
5 4 103 14.4
6 5 104 4.21
7 6 105 4.15
8 7 106 8.35
9 8 107 5.12
10 9 108 8.88
11 10 109 7.55
12 11 110 4.94
13 12 111 9.51
14 13 112 6.84
15 14 113 8.33
📊 order_shipments 1,000 rows 23.4 KiB Data Volume
shipment_id (INTEGER) order_id (INTEGER) fee (DECIMAL(10,2))
Data Preview (showing sample rows from 1,000 total rows):
# shipment_id order_id fee
1 1 101 4
2 2 101 6
3 2 101 7.06
4 3 102 4.77
5 4 103 6.92
6 5 104 3.67
7 6 105 2.14
8 7 106 4.42
9 8 107 6.19
10 9 108 5.16
11 10 109 5.36
12 11 110 5.77
13 12 111 7.15
14 13 112 3.37
15 14 113 6.03
📊 orders 1,000,000 rows 22.9 MiB Data Volume
order_id (INTEGER) customer_id (INTEGER) base_amount (DECIMAL(10,2))
Data Preview (showing sample rows from 1,000,000 total rows):
# order_id customer_id base_amount
1 101 1 100
2 101 1 113.23
3 102 2 94.32
4 103 3 84.23
5 104 4 131.86
6 105 5 100.55
7 106 6 96.21
8 107 7 80.73
9 108 8 112.32
10 109 9 102.4
11 110 10 92.26
12 111 11 75.55
13 112 12 124.92
14 113 13 71.07
15 114 14 104.09

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: 329.81 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
📐 PROJECTION 3.9%
Latency: 12.80 ms
Rows In/Out: 999,999
Data Vol: 64.85 MB
📁 Projections: ['customer_id', 'to...
📐 PROJECTION 2.4%
Latency: 8.01 ms
Rows In/Out: 999,999
Data Vol: 49.59 MB
📁 Projections: ['customer_id', 'to...
📊 HASH_GROUP_BY 🔥 86.5% Cost
Latency: 285.43 ms
Rows In/Out: 999,999
Data Vol: 49.84 MB
⚠️ Heavy Execution Bottleneck (86.5% of query runtime)
📐 PROJECTION 0.1%
Latency: 0.18 ms
Rows In/Out: 1,000,000
Data Vol: 42.20 MB
📁 Projections: ['customer_id', 'ba...
⚡ HASH_JOIN 3.0%
Latency: 10.03 ms
Rows In/Out: 1,000,000
Data Vol: 42.20 MB
⚡ HASH_JOIN 3.3%
Latency: 10.94 ms
Rows In/Out: 1,000,000
Data Vol: 30.64 MB
🔍 TABLE_SCAN 0.7%
Latency: 2.28 ms
Rows In/Out: 1,000,000
Data Vol: 15.26 MB
📁 memory.main.orders
📐 PROJECTION 0.0%
Latency: 0.00 ms
Rows In/Out: 998
Data Vol: 0.02 MB
📁 Projections: ['CAST(#0 AS INTEGE...
📊 PERFECT_HASH_GROUP_BY 0.0%
Latency: 0.03 ms
Rows In/Out: 998
Data Vol: 0.02 MB
📐 PROJECTION 0.0%
Latency: 0.00 ms
Rows In/Out: 1,000
Data Vol: 10,000 B
📁 Projections: ['order_id', 'amoun...
📐 PROJECTION 0.0%
Latency: 0.00 ms
Rows In/Out: 1,000
Data Vol: 10,000 B
📁 Projections: ['CAST(#0 AS SMALLI...
🔍 TABLE_SCAN 0.0%
Latency: 0.02 ms
Rows In/Out: 1,000
Data Vol: 0.01 MB
📁 memory.main.order_discounts
📐 PROJECTION 0.0%
Latency: 0.00 ms
Rows In/Out: 998
Data Vol: 0.02 MB
📁 Projections: ['CAST(#0 AS INTEGE...
📊 PERFECT_HASH_GROUP_BY 0.0%
Latency: 0.05 ms
Rows In/Out: 998
Data Vol: 0.02 MB
📐 PROJECTION 0.0%
Latency: 0.00 ms
Rows In/Out: 1,000
Data Vol: 10,000 B
📁 Projections: ['order_id', 'fee']
📐 PROJECTION 0.0%
Latency: 0.00 ms
Rows In/Out: 1,000
Data Vol: 10,000 B
📁 Projections: ['CAST(#0 AS SMALLI...
🔍 TABLE_SCAN 0.0%
Latency: 0.02 ms
Rows In/Out: 1,000
Data Vol: 0.01 MB
📁 memory.main.order_shipments
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
HASH_GROUP_BY 86.5% 285.43 ms 999,999 49.84 MB - 🔥 BOTTLENECK
PROJECTION 3.9% 12.80 ms 999,999 64.85 MB Projections: ['customer_id', 'total_gross_sales' Normal
HASH_JOIN 3.3% 10.94 ms 1,000,000 30.64 MB - Normal
HASH_JOIN 3.0% 10.03 ms 1,000,000 42.20 MB - Normal
PROJECTION 2.4% 8.01 ms 999,999 49.59 MB Projections: ['customer_id', 'total_gross_sales' Normal
TABLE_SCAN 0.7% 2.28 ms 1,000,000 15.26 MB memory.main.orders Normal
PROJECTION 0.1% 0.18 ms 1,000,000 42.20 MB Projections: ['customer_id', 'base_amount', 'tot Normal
PERFECT_HASH_GROUP_BY 0.0% 0.05 ms 998 0.02 MB - Normal
PERFECT_HASH_GROUP_BY 0.0% 0.03 ms 998 0.02 MB - Normal
TABLE_SCAN 0.0% 0.02 ms 1,000 0.01 MB memory.main.order_discounts Normal
TABLE_SCAN 0.0% 0.02 ms 1,000 0.01 MB memory.main.order_shipments Normal
PROJECTION 0.0% 0.00 ms 1,000 0.01 MB Projections: ['CAST(#0 AS SMALLINT)', '#1'] Normal
PROJECTION 0.0% 0.00 ms 998 0.02 MB Projections: ['CAST(#0 AS INTEGER)', '#1'] Normal
PROJECTION 0.0% 0.00 ms 998 0.02 MB Projections: ['CAST(#0 AS INTEGER)', '#1'] Normal
PROJECTION 0.0% 0.00 ms 1,000 0.01 MB Projections: ['CAST(#0 AS SMALLINT)', '#1'] Normal
PROJECTION 0.0% 0.00 ms 1,000 0.01 MB Projections: ['order_id', 'fee'] Normal
PROJECTION 0.0% 0.00 ms 1,000 0.01 MB Projections: ['order_id', 'amount'] Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted
Candidate SQL Executed by gemini-3.8-flash-low (Runtime on 1M Rows: 127.44 ms) 
WITH customer_orders AS
  (SELECT customer_id,
          order_id,
          base_amount
   FROM orders),
     aggregated_discounts AS
  (SELECT order_id,
          sum(amount) AS total_order_discount
   FROM order_discounts
   GROUP BY order_id),
     aggregated_shipments AS
  (SELECT order_id,
          sum(fee) AS total_order_shipping
   FROM order_shipments
   GROUP BY order_id)
SELECT o.customer_id,
       sum(o.base_amount) AS total_gross_sales,
       coalesce(sum(d.total_order_discount), 0.00) AS total_discount_amount,
       coalesce(sum(s.total_order_shipping), 0.00) AS total_shipping_fees,
       sum(o.base_amount) - coalesce(sum(d.total_order_discount), 0.00) + coalesce(sum(s.total_order_shipping), 0.00) AS net_revenue
FROM customer_orders o
LEFT JOIN aggregated_discounts d ON o.order_id = d.order_id
LEFT JOIN aggregated_shipments s ON o.order_id = s.order_id
GROUP BY o.customer_id;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 133.52 ms) 
-- Correctly aggregate discounts and shipments without fan-out inflation
 WITH agg_discounts AS
  (SELECT order_id,
          sum(amount) AS total_discount
   FROM order_discounts
   GROUP BY order_id),
      agg_shipments AS
  (SELECT order_id,
          sum(fee) AS total_shipping
   FROM order_shipments
   GROUP BY order_id)
SELECT o.customer_id,
       sum(o.base_amount) AS total_gross_sales,
       coalesce(sum(d.total_discount), 0.00) AS total_discount_amount,
       coalesce(sum(s.total_shipping), 0.00) AS total_shipping_fees,
       round(sum(o.base_amount) - coalesce(sum(d.total_discount), 0.00) + coalesce(sum(s.total_shipping), 0.00), 2) AS net_revenue
FROM orders o
LEFT JOIN agg_discounts d ON o.order_id = d.order_id
LEFT JOIN agg_shipments s ON o.order_id = s.order_id
GROUP BY o.customer_id;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE orders (order_id INT, customer_id INT, base_amount decimal(10, 2));


CREATE TABLE order_discounts (discount_id INT, order_id INT, amount decimal(10, 2));


CREATE TABLE order_shipments (shipment_id INT, order_id INT, fee decimal(10, 2));


INSERT INTO orders
VALUES (101, 1, 100.00);


INSERT INTO order_discounts
VALUES (1, 101, 10.00),
       (2, 101, 5.00);


INSERT INTO order_shipments
VALUES (1, 101, 4.00),
       (2, 101, 6.00);

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (999,999 rows × 5 cols)
# customer_id total_gross_sales total_discount_amount total_shipping_fees net_revenue
1 122,895 102.94 0 0 102.94
2 122,897 78.5 0 0 78.5
3 122,997 86.42 0 0 86.42
4 123,044 86.11 0 0 86.11
5 123,069 79.08 0 0 79.08
6 123,088 104.56 0 0 104.56
7 123,148 123.19 0 0 123.19
8 123,237 101.99 0 0 101.99
9 123,319 107.15 0 0 107.15
10 123,336 104.83 0 0 104.83
11 123,400 102.72 0 0 102.72
12 123,414 77.23 0 0 77.23
13 123,480 76.06 0 0 76.06
14 123,511 124.57 0 0 124.57
15 123,557 133.35 0 0 133.35
16 123,612 89.74 0 0 89.74
17 123,615 128.26 0 0 128.26
18 123,665 84.59 0 0 84.59
19 123,678 120.71 0 0 120.71
20 123,701 90.99 0 0 90.99
21 123,772 98.55 0 0 98.55
22 123,780 90.57 0 0 90.57
23 123,783 88.39 0 0 88.39
24 123,808 103.09 0 0 103.09
25 123,847 49.53 0 0 49.53
Showing first 25 of 999,999 total rows.
Expected Golden Output (999,999 rows × 5 cols)
# customer_id total_gross_sales total_discount_amount total_shipping_fees net_revenue
1 368,650 102.82 0 0 102.82
2 368,666 120.45 0 0 120.45
3 368,679 89.75 0 0 89.75
4 368,720 72.76 0 0 72.76
5 368,736 116.1 0 0 116.1
6 368,846 97.54 0 0 97.54
7 368,866 97.43 0 0 97.43
8 368,884 93.15 0 0 93.15
9 368,914 72.33 0 0 72.33
10 369,035 75.79 0 0 75.79
11 369,046 100.41 0 0 100.41
12 369,057 113.25 0 0 113.25
13 369,060 119.56 0 0 119.56
14 369,122 69.86 0 0 69.86
15 369,123 101.19 0 0 101.19
16 369,142 112.52 0 0 112.52
17 369,172 95.41 0 0 95.41
18 369,173 78.12 0 0 78.12
19 369,188 86.82 0 0 86.82
20 369,255 104.2 0 0 104.2
21 369,256 73.34 0 0 73.34
22 369,364 120.34 0 0 120.34
23 369,393 121.07 0 0 121.07
24 369,454 122.51 0 0 122.51
25 369,500 105.56 0 0 105.56
Showing first 25 of 999,999 total rows.
← Prev: Transactional State Machi... ⊞ Back to Benchmark Dashboard Next: SARGable Temporal Partiti... →