SQL-FRONTIER Benchmark Deep-Dive / Vector & Embeddings / task_042_Q42_negative_prompting_vector_repulsion
← Prev: Latent Factor Matrix Fact... ⊞ All 120 Queries Next: Document-Level Aggregatio... →
✔ PASS Vector & Embeddings task_042_Q42_negative_prompting_vector_repulsion Scaled: 1M+ Records Ordered: Yes

Vector Repulsion Scoring (Positive vs Negative Query Guidance)

Candidate Latency (1M Rows)
262.37 ms
(1.0x slower than golden)
Golden Latency (1M Rows)
256.42 ms
DuckDB in-memory engine
Underlying Data Volume
1,000,000 rows
30.5 MiB across 1 table
Evaluated Model
gemini-3.8-flash-low
Gen Latency: 12.2s
✔ 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

In image generation and semantic search, users supply positive and negative prompts (e.g. 'cyberpunk city' NOT 'blurry / low quality'). The score boosts positive similarity and penalizes negative similarity.

⚡ The Frontier Trap (Why LLMs Fail)

Filtering out negatives with boolean NOT drops items that have even slight overlap with negative words.

Prompt / User Request Given to Model
In image generation and semantic search, users supply positive and negative prompts (e.g. 'cyberpunk city' NOT 'blurry / low quality'). The score boosts positive similarity and penalizes negative similarity.

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

2. Underlying Tables, Row Counts & Data Volume

1 Tables (1,000,000 rows • 30.5 MiB)

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

📊 generative_assets 1,000,000 rows 30.5 MiB Data Volume
asset_id (INTEGER) name (VARCHAR) vec (FLOAT[2])
Data Preview (showing sample rows from 1,000,000 total rows):
# asset_id name vec
1 1 Sharp Neon Skyline [0.9 0.1]
2 2 Blurry Neon Streetscape [0.8 0.7]
3 3 Rustic Wooden Cabin [0. 0.9]
4 3 Sharp Neon Skyline [0.9156 0.0837]
5 4 Blurry Neon Streetscape [0.8852 0.1337]
6 5 Rustic Wooden Cabin [0.8744 0.1242]
7 6 Sharp Neon Skyline [0.9135 0.0987]
8 7 Blurry Neon Streetscape [0.8887 0.1472]
9 8 Rustic Wooden Cabin [0.8848 0.0686]
10 9 Sharp Neon Skyline [0.8603 0.0646]
11 10 Blurry Neon Streetscape [0.7757 0.0407]
12 11 Rustic Wooden Cabin [0.8216 0.1476]
13 12 Sharp Neon Skyline [0.8509 0.0848]
14 13 Blurry Neon Streetscape [0.762 0.1005]
15 14 Rustic Wooden Cabin [0.8194 0.0965]

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: 297.62 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
⏳ ORDER_BY 🔥 56.2% Cost
Latency: 167.37 ms
Rows In/Out: 1,000,000
Data Vol: 30.52 MB
⚠️ Heavy Execution Bottleneck (56.2% of query runtime)
📐 PROJECTION 🔥 28.5% Cost
Latency: 84.94 ms
Rows In/Out: 1,000,000
Data Vol: 30.52 MB
📁 Projections: ['asset_id', 'name'...
⚠️ Heavy Execution Bottleneck (28.5% of query runtime)
📐 PROJECTION 11.7%
Latency: 34.96 ms
Rows In/Out: 1,000,000
Data Vol: 26.70 MB
📁 Projections: ['asset_id', 'name'...
🔍 TABLE_SCAN 3.5%
Latency: 10.35 ms
Rows In/Out: 1,000,000
Data Vol: 26.70 MB
📁 memory.main.generative_assets
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
ORDER_BY 56.2% 167.37 ms 1,000,000 30.52 MB - 🔥 BOTTLENECK
PROJECTION 28.5% 84.94 ms 1,000,000 30.52 MB Projections: ['asset_id', 'name', 'pos_sim', 'ne 🔥 BOTTLENECK
PROJECTION 11.7% 34.96 ms 1,000,000 26.70 MB Projections: ['asset_id', 'name', 'array_cosine_ Normal
TABLE_SCAN 3.5% 10.35 ms 1,000,000 26.70 MB memory.main.generative_assets 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: 262.37 ms) 
-- Positive query: [1.0, 0.0] (cyberpunk neon), Negative query: [0.0, 1.0] (blurry/rustic)
-- Net Score = Cosine(vec, pos) - (0.5 * Cosine(vec, neg))

SELECT asset_id,
       name,
       round(array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]), 4) AS pos_sim,
       round(array_cosine_similarity(vec, [0.0, 1.0]::FLOAT[2]), 4) AS neg_sim,
       round(array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]) - (0.50 * array_cosine_similarity(vec, [0.0, 1.0]::FLOAT[2])), 4) AS net_guided_score
FROM generative_assets
ORDER BY net_guided_score DESC;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 256.42 ms) 
-- Positive query: [1.0, 0.0] (cyberpunk neon), Negative query: [0.0, 1.0] (blurry/rustic)
 -- Net Score = Cosine(vec, pos) - (0.5 * Cosine(vec, neg))

SELECT asset_id,
       name,
       round(array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]), 4) AS pos_sim,
       round(array_cosine_similarity(vec, [0.0, 1.0]::FLOAT[2]), 4) AS neg_sim,
       round(array_cosine_similarity(vec, [1.0, 0.0]::FLOAT[2]) - (0.50 * array_cosine_similarity(vec, [0.0, 1.0]::FLOAT[2])), 4) AS net_guided_score
FROM generative_assets
ORDER BY net_guided_score DESC;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE generative_assets (asset_id INT, name VARCHAR, vec FLOAT[2]);


INSERT INTO generative_assets
VALUES (1, 'Sharp Neon Skyline', [0.9, 0.1]::FLOAT[2]),
       (2, 'Blurry Neon Streetscape', [0.8, 0.7]::FLOAT[2]),
       (3, 'Rustic Wooden Cabin', [0.0, 0.9]::FLOAT[2]);

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (1,000,000 rows × 5 cols)
# asset_id name pos_sim neg_sim net_guided_score
1 720,289 Blurry Neon Streetscape 0.993 -0.1185 1.0522
2 724,362 Sharp Neon Skyline 0.9937 -0.1121 1.0497
3 962,196 Sharp Neon Skyline 0.9947 -0.1028 1.0461
4 630,022 Blurry Neon Streetscape 0.9949 -0.1009 1.0454
5 666,507 Sharp Neon Skyline 0.9951 -0.0992 1.0447
6 879,949 Blurry Neon Streetscape 0.9951 -0.099 1.0446
7 411,414 Sharp Neon Skyline 0.9955 -0.0945 1.0428
8 106,762 Blurry Neon Streetscape 0.9961 -0.0885 1.0403
9 986,598 Sharp Neon Skyline 0.9962 -0.0873 1.0398
10 594,405 Sharp Neon Skyline 0.9962 -0.0867 1.0396
11 936,960 Sharp Neon Skyline 0.9963 -0.0854 1.0391
12 387,353 Rustic Wooden Cabin 0.9964 -0.0845 1.0387
13 415,980 Sharp Neon Skyline 0.9964 -0.0844 1.0386
14 161,587 Blurry Neon Streetscape 0.9965 -0.084 1.0385
15 673,990 Blurry Neon Streetscape 0.9966 -0.0829 1.038
16 877,559 Rustic Wooden Cabin 0.9966 -0.0819 1.0376
17 476,090 Rustic Wooden Cabin 0.9967 -0.0815 1.0374
18 827,401 Blurry Neon Streetscape 0.9967 -0.0815 1.0374
19 961,973 Rustic Wooden Cabin 0.9968 -0.0802 1.0369
20 832,104 Sharp Neon Skyline 0.9968 -0.0796 1.0366
21 765,940 Blurry Neon Streetscape 0.9969 -0.0784 1.0361
22 520,098 Sharp Neon Skyline 0.997 -0.0775 1.0358
23 804,547 Blurry Neon Streetscape 0.997 -0.0768 1.0355
24 830,632 Blurry Neon Streetscape 0.9971 -0.0767 1.0354
25 226,508 Rustic Wooden Cabin 0.9971 -0.0763 1.0352
Showing first 25 of 1,000,000 total rows.
Expected Golden Output (1,000,000 rows × 5 cols)
# asset_id name pos_sim neg_sim net_guided_score
1 720,289 Blurry Neon Streetscape 0.993 -0.1185 1.0522
2 724,362 Sharp Neon Skyline 0.9937 -0.1121 1.0497
3 962,196 Sharp Neon Skyline 0.9947 -0.1028 1.0461
4 630,022 Blurry Neon Streetscape 0.9949 -0.1009 1.0454
5 666,507 Sharp Neon Skyline 0.9951 -0.0992 1.0447
6 879,949 Blurry Neon Streetscape 0.9951 -0.099 1.0446
7 411,414 Sharp Neon Skyline 0.9955 -0.0945 1.0428
8 106,762 Blurry Neon Streetscape 0.9961 -0.0885 1.0403
9 986,598 Sharp Neon Skyline 0.9962 -0.0873 1.0398
10 594,405 Sharp Neon Skyline 0.9962 -0.0867 1.0396
11 936,960 Sharp Neon Skyline 0.9963 -0.0854 1.0391
12 387,353 Rustic Wooden Cabin 0.9964 -0.0845 1.0387
13 415,980 Sharp Neon Skyline 0.9964 -0.0844 1.0386
14 161,587 Blurry Neon Streetscape 0.9965 -0.084 1.0385
15 673,990 Blurry Neon Streetscape 0.9966 -0.0829 1.038
16 877,559 Rustic Wooden Cabin 0.9966 -0.0819 1.0376
17 827,401 Blurry Neon Streetscape 0.9967 -0.0815 1.0374
18 476,090 Rustic Wooden Cabin 0.9967 -0.0815 1.0374
19 961,973 Rustic Wooden Cabin 0.9968 -0.0802 1.0369
20 832,104 Sharp Neon Skyline 0.9968 -0.0796 1.0366
21 765,940 Blurry Neon Streetscape 0.9969 -0.0784 1.0361
22 520,098 Sharp Neon Skyline 0.997 -0.0775 1.0358
23 804,547 Blurry Neon Streetscape 0.997 -0.0768 1.0355
24 830,632 Blurry Neon Streetscape 0.9971 -0.0767 1.0354
25 226,508 Rustic Wooden Cabin 0.9971 -0.0763 1.0352
Showing first 25 of 1,000,000 total rows.
← Prev: Latent Factor Matrix Fact... ⊞ Back to Benchmark Dashboard Next: Document-Level Aggregatio... →