Spider 2.0-Snow: what makes an instance hard?

Interim findings from an all-pairs execution study of the vxef/spider2-snow-temperature-sweep rollouts — Qwen3 {1.7B, 4B, 8B} × {thinking, no-thinking}, 100 samples per instance per cell, 523 grounded instances. Every distinct generated SQL was quoting-normalized (identifiers quoted, case preserved), compiled with Snowflake EXPLAIN, and every compile survivor was executed and scored against gold (Spider 2's own comparison); the raw as-is SQL got its own EXPLAIN sweep. All records are in the dataset PR and browsable row-by-row in the explorer (quoted-verdict filter + expected-vs-got diffs). July 16, 2026.

1 · pass@k by model and reasoning arm

The topline: mean pass@1 across instances, per cell (each cell = one model size × one reasoning arm, 100 quoting-normalized samples per instance; error bars are 95% bootstrap CIs over instances). Thinking roughly doubles-to-triples accuracy at every size — 4B-think (4.3%) beats 8B-no-think (2.8%), so the reasoning arm is worth more than a size doubling here — and the best cell, 8B-think, still sits at 6.9%. The right chart shows what a sampling budget buys: the expected share of instances solved at k samples (mean unbiased pass@k); at k = 100 the best cell reaches ~22% of instances, and the curves are still rising — sampling is nowhere near exhausted on the solvable set.

2 · The funnel: two walls, not one

Reading each cell's samples left to right: finish → parse (sqlglot) → static grounding → compile (Snowflake EXPLAIN) → ran → matched gold (≡ pass@1). The compile stage is not another static check: EXPLAIN is a dry run through Snowflake's actual query compiler against the live account catalog — parsing in the real dialect, resolving every table/column name under the engine's quoting and case-folding rules, type-checking expressions and function signatures, checking privileges — everything short of dispatching the plan for execution (it runs in the cloud-services layer, no warehouse needed). That makes it strictly stronger than client-side static analysis, and weaker than execution only on runtime, data-dependent failures (division by zero, casts on bad values, timeouts), which the “ran” stage then catches. The “compiles (as-is)” group is the raw SQL's compile rate — formatting alone (unquoted case-sensitive identifiers) kills 97–99% of every cell's raw queries before anything else can matter; the adjacent “compiles (quoted)” group shows the same queries after quoting normalization. What remains of the compile wall is semantic. The bar chart below is a failure-mode census of the 142k distinct failing (instance, SQL) pairs — the union across cells, deduped because identical SQL compiles once; it characterizes the corpus of errors, not any one model: 43% reference a nonexistent column and 29% a nonexistent table — schema-linking failures, not dialect (5%) or syntax (3%). Browse compile failures →

3 · Per-instance pass rates — a consistent frontier

Each cell's instances ranked by pass@1 (that cell's 100 samples; only instances with ≥1 pass drawn — the counts at zero are in the hover). Each point is an instance; click to open it in the explorer. (We deliberately don't plot pass@1 against pass@k for the same pool — both are deterministic functions of the same pass count, so that “relationship” is a parametric curve with no information beyond this ranking.) The solvable sets are nested: 90–100% of every weaker cell's solvable instances are contained in 8B-think's 114 (per-instance pass@1 Spearman 0.83 between 4B-think and 8B-think) — capability growth widens one frontier, it does not unlock different problems. Per-instance whiskers are 95% bootstrap CIs from resampling that instance's own 100 samples with replacement (equivalently c* ~ Binomial(100, c/100)); lower bounds of 0 are truncated at the axis floor, and CIs this near c = 0 are rough by nature.

4 · What predicts hardness (and what doesn't)

Mean pass@k across instances per covariate bin, per cell; error bars are 95% bootstrap CIs resampling instances within bin. The cell's own compile rate is the strongest predictor (schema-linking again; solvable-vs-not AUC 0.86 in the 8B-think cell). Self-inconsistency (distinct-SQL rate) is the best behavioral tell. Question length acts as a threshold, not a slope. Static column-grounding — the flag the dataset ships — is ~uninformative (AUC 0.51, 8B-think). Thinking-token volume predicts nothing for 4B/8B; explicit date/window phrasing drops match-given-compiled from 0.23 to 0.11 (8B-think; same sign in 4B-think). Beyond compiling, misses are far off, not near-misses — a census of the ~30k distinct ran-but-wrong executions (union across cells): 31% have exactly the gold's shape with wrong values, and unit/rounding confusions are absent (1 case of ×100 in 3,355 scalar comparisons).

5 · The tantalizing stratum: compiles often, never matches

177 instances (8B-think) have ≥10 compiled samples and zero matches — the analytic-composition wall. They read like multi-step aggregation with precise windows and exact output contracts. This is the stratum where execution-feedback methods have signal to amplify; the never-compiles stratum needs grounding (constrained decoding over schema identifiers) instead.

6 · Oracle identifier repair: is the cliff about names?

How we estimate this

We use a two-tier oracle repair. Tier 1 is a deterministic, compile-error-driven fuzzy-match fixer that swaps in the schema-correct table/column name, run over a seeded stratified sample of 300 failing (instance, SQL) pairs drawn across all six model×arm cells. Tier 2 escalates the pairs tier‑1 can't confidently fix to an LLM oracle armed with the ground-truth schema and EXPLAIN/execution feedback, piloted on a small, deliberately biased 10-pair probe weighted toward the census's most-repairable-looking class — enough to exercise the protocol, not to estimate a rate. Both tiers are held to the same rule: an AST-isomorphism gate allows only identifier nodes to change, so what's measured is the ceiling of identifier-only repair, nothing else.

In detail. For a seeded stratified sample of 300 failing (instance, SQL) pairs — drawn across the 6 model×arm cells, split into three strata (cliff1: compiles down to the right tables but not fully grounded, exactly the funnel's second wall (section 2); notab: references a table that doesn't exist at all; control: fully grounded per the dataset's own static flags yet still failed some other way, a negative control) — an oracle repair loop replaces bad table/column identifiers with the schema-correct name (ground-truth SHOW COLUMNS inventory, fuzzy-matched, EXPLAIN/execution feedback) and changes nothing else: every edit is gated by an AST-isomorphism check (only identifier nodes may differ from the original parse skeleton). This measures the ceiling of identifier-level intervention — what a schema-constrained decoding scheme could buy — independent of any model's own capability. Tier 1 (a deterministic, compile-error-driven fuzzy-match loop) processed all 300/300 pairs. Tier 2 (an LLM subagent per pair, same identifier-only contract, allowed judgment on tier-1's escalations) was piloted on 10 pairs and deliberately stopped there — drawn to exercise the protocol, overweighting the census's most-repairable-looking class (intable), not to estimate a rate. Every tier-2 number below is a qualitative read on the escalated error corpus, union across cells — never a per-cell rate, and never pooled with tier-1's own per-cell numbers.

What's left after fixing identifiers

Almost nothing passes, and most of what changes is the mirage channel described above. Past the identifier layer, the remaining failures sort into six recurring classes — a parsing-artifact gap, missing joins, Snowflake dialect gaps that need FLATTEN over nested VARIANT/array fields, invented columns the model wished existed, assorted other-structural issues, and the mirage itself (compiles, runs, wrong answer) — each illustrated with worked examples below.

The headline is a decomposition, not a recovery. Of the 300 sampled pairs (50 drawn from each of the 6 model × arm cells — 30 cliff-1, 10 not-table-grounded, 10 fully-grounded control per cell; no pair appears in more than one cell), tier-1 repair passes exactly 1. 13 already compile with zero identifier changes (noop_compiles — nothing for an identifier-only repair to do, so not an identifier bug); 2 compile and run after a fix but don't match gold (ran_no_match, the mirage channel that inflates section 5's tantalizing stratum); 1 compiles and runs then errors at execution; the remaining 283 escalate — tier-1 can't confidently apply a same-skeleton fix (187 escalate_structural, 56 escalate_no_candidate — no fuzzy match anywhere in scope, 30 escalate_ambiguous — multiple similarly-close candidates, 10 escalate_no_progress). 76 of the 187 escalate_structural rows are a parsing artifact, not a structural one: tier-1's error parser deliberately doesn't handle a dotted/qualified identifier (e.g. invalid identifier '"u"."owner_user_id"'), so those land in escalate_structural by construction. That gap doesn't hide easy wins either — the tier-2 pilot specifically probed 2 of the 76 (sf_bq169, sf_ga001), and a third was examined by hand outside the pilot (sf_local356); none passed.

A static census of cliff-1's 22,662 unique failing pairs (referenced-table candidate pools only — a close match in an unjoined table counts as missing-join evidence, not a typo) shows why so little of this converts to pass: 10.8% are intable-close (every bad column has a ≥0.75 fuzzy match in a table the query already references), 10.5% are elsewhere (a close match exists only in a table never joined), 45.4% are nowhere (no close match anywhere in the schema), and 33.2% are mixed. Even intable-close is dominated by coincidentally-close hallucinated columns, not typos: sf_local356's prev_position → position is the clearest case — the model wants each lap's previous position, which is a LAG() window computation, not a stored column. The models hallucinate the schema they wish existed: flattened, pre-joined, pre-aggregated.

Reweighting the sample's tier-1 outcomes back up to each cell's own cliff-1 population (post-stratification over stratum × static_class buckets: sampling weight = population share ÷ sampled count; a handful of small notab/control buckets some cells' draws never hit are excluded from the denominator as a coverage gap rather than guessed — cliff-1 itself always has full coverage, since sample.py's own quota always fills its 4 classes) gives, per cell, where cliff-1's failing mass ends up. Browse the underlying compile failures →

The two views below — unique pairs vs. rollout mass (each pair weighted by how many of that cell's own 100-per-instance samples produced it) — agree closely: pass stays at zero in 5 of 6 cells and reaches only 1.5% (unique-pair) / 1.4% (rollout) in the one cell it doesn't, 4B·think. Converting that into a pass@1 headroom estimate (repaired-pass rollout mass ÷ that cell's total 100-per-instance sample budget) tops out at 0.04 percentage points — extremely rough, since it rests on a single pass drawn from a 10-pair bucket, but nowhere close to the several points needed to explain the funnel in section 1.

Tier-2 pilot (10 of 10 pairs verdicted at build time): 0 PASS, 3 RAN_NO_MATCH, 7 UNFIXABLE (4 other-structural, 2 dialect-needs-flatten, 1 missing-join) — a tally over the escalated error corpus, union across cells, not a per-cell rate (n is far too small for that). The taxonomy below merges these pilot diagnoses with five hand-examined walkthrough pairs into six illustrated failure classes, each linking to the pair's instance in the explorer.

• Dotted-identifier parse gap — 40.6% (76 of 187)
  share of the 187 escalate_structural rows (tier1._parse_error never matches a dotted/qualified identifier, so these land in escalate_structural by construction, not because the problem is structural); 2 of these were probed by the tier-2 pilot (sf_bq169, sf_ga001) and neither passed, and a third (sf_local356, below, examined outside the pilot) turned out to need a LAG window function, not a rename either.
  • sf_local356 (F1) — tier-1's last logged EXPLAIN error is invalid identifier '"l"."prev_position"' -- a dotted/qualified name tier1._parse_error deliberately does not match (tier1.py's module docstring), so this pair lands in escalate_structural by construction; examined by hand it turns out to be the invented-concept case above (LAG needed, not a rename), the same pattern the tier-2 pilot's two dotted probes (sf_bq169, sf_ga001) also hit. explorer →
  • sf_bq169 (MITELMAN) [tier-2 pilot verdict: RAN_NO_MATCH] — repair_cli schema for MITELMAN shows KARYCLONE has columns ChromoMin/ChromoMax (not ChrMin/ChrMax), so renaming k.ChrMin->ChromoMin and k.ChrMax->ChromoMax fixes the compile error, but the query then runs and returns 0 rows (match=0) because the WHERE clause requires the single aliased CYTOCONVERTED row c to satisfy Chr='13' AND Chr='17' AND Chr='11' simultaneously, which needs three self-joins to CYTOCONVERTED (one per chromosome) to express correctly -- a structural change outside identifier-only repair. explorer →
  • sf_ga001 (GA4) [tier-2 pilot verdict: UNFIXABLE/dialect-needs-flatten] — repair_cli schema lists ITEMS as the only item-related column on EVENTS_20201104 (no flat item_id/quantity columns exist to rename to), and a direct probe (SELECT TYPEOF("ITEMS") FROM "EVENTS_20201104") returns ARRAY (each element a JSON object with item_id/quantity/item_name/etc.), so "e1"."ITEMS"."item_id" is a genuine nested-array field access that requires FLATTEN/LATERAL or array indexing, not an identifier rename, and the contract forbids adding FLATTEN. explorer →
• Missing join — 10.5% (2,389 of 22,662)
  proxy: share of cliff1's 22,662 unique failing pairs whose static_class is 'elsewhere' (a close column/table match exists only in a table the query never joined) -- the design doc's own mapping, not a literal unfixable_class count.
  • sf_bq366 (THE_MET) — repair_cli schema for THE_MET confirms VISION_API_DATA has no 'period' column (only annotation fields: labelAnnotations, faceAnnotations, fullTextAnnotation, ..., object_id), while OBJECTS.period exists and is never joined into the query, so the outer SELECT's 'period' reference needs a join to OBJECTS on object_id, not an identifier rename within VISION_API_DATA. explorer →
  • sf_bq089 (COVID19_USA) — repair_cli schema for COVID19_USA confirms ZIP_CODES_2015_5YR (the query's only FROM table) has no state-like column among its ~250 census fields, while CONFIRMED_CASES and DEATHS both carry state/state_fips_code and are never joined in; filtering STATE = 'CA' needs a join to one of those tables, not a rename within ZIP_CODES_2015_5YR. explorer →
  • sf_bq187 (ETHEREUM_BLOCKCHAIN) [tier-2 pilot verdict: UNFIXABLE/missing-join] — TOKEN_TRANSFERS has a `value` column (confirmed via repair_cli schema), so renaming the outer, unqualified "value" to "total_received" (the derived table's only exposed column, since Snowflake names a UNION ALL result set from its first arm's aliases) fixes that error, but the WHERE clause's remaining "total_sent" reference has no valid binding -- the second UNION ALL branch's alias is not exposed outside the subquery -- and remapping it to "total_received" too is rejected by check-ast as a structural change (skeletons differ), so combining per-address received and sent sums requires a JOIN or conditional SUM/CASE that identifier substitution alone cannot supply. explorer →
• Dialect / needs FLATTEN
  no static-census proxy (VARIANT/array field access looks like an ordinary bad-column reference until inspected); examples only.
  • sf_bq366 (THE_MET) — repair_cli schema shows VISION_API_DATA.labelAnnotations is a single nested/repeated annotation column, and the query accesses it with BigQuery's UNNEST(labelAnnotations) AS labelAnnotations plus dotted labelAnnotations.value.string_value STRUCT access -- Snowflake has no UNNEST-over-a-column-name syntax; the same nested access needs LATERAL FLATTEN over a VARIANT, a dialect rewrite, not an identifier substitution. explorer →
  • sf_bq065 (CRYPTO) — repair_cli schema for CRYPTO's ORACLE_REQUESTS lists only decoded_result (plus block_height/block_timestamp/oracle_request_id/oracle_script/reports/request/result) -- no symbols, rates, multiplier, or loop variable i exist as columns at all; the question itself says these come 'from the decoded result', so symbols[i].symbol / rates[i] is per-element array indexing over what is actually one VARIANT column in Snowflake, needing LATERAL FLATTEN over decoded_result, not a rename. explorer →
  • sf_bq070 (IDC) [tier-2 pilot verdict: UNFIXABLE/dialect-needs-flatten] — repair_cli schema for sf_bq070 shows DICOM_ALL has no scalar tissue_type, physical_slide_id/specimen-identifier column anywhere in its 897 columns (nor in linked DICOM_METADATA_CURATED, DICOM_METADATA_CURATED_SERIES_LEVEL, DICOM_PIVOT, TCGA_BIOSPECIMEN_REL9, or TCGA_CLINICAL_REL9); the only source for either value is the nested VARIANT SpecimenDescriptionSequence column (specimen prep steps and anatomic-structure coding nested inside it), which requires LATERAL FLATTEN to reach and cannot be produced by renaming a single identifier, so no identifier-only edit can fix this. explorer →
• Invented concept
  no static-census proxy (a hallucinated derived column can fall in any static_class, including intable-close -- see sf_local356 below); examples only.
  • sf_local356 (F1) — repair_cli schema for F1's LAP_POSITIONS lists driver_id/lap/lap_type/position/race_id -- no prev_position column exists, and it fuzzy-matches to position (ratio just over the 0.75 cutoff, static_class=intable), but the query wants each lap's previous position, which is a LAG(position) OVER (PARTITION BY driver_id, race_id ORDER BY lap) computation, not a stored column; this is a case of intable-close being dominated by hallucinated derived columns rather than genuine near-miss renames. explorer →
  • sf_bq110 (SDOH) — repair_cli schema for SDOH's HUD_PIT_BY_COC stores one Overall_Homeless value per (CoC, Count_Year) row -- there are no per-year pivoted columns -- yet the outer SELECT references Overall_Homeless_2018 / Overall_Homeless_2012 / Year as if the schema were wide-pivoted-by-year, when the two self-joined subqueries it's built from actually expose Overall_Homeless and Year (unprefixed); none of the three referenced names exist anywhere in this db's inventory, including as the subqueries' own output aliases. explorer →
• Other structural
  tier-2 pilot catch-all (verdict_schema.py's UNFIXABLE_CLASSES default); qualitative, n=10-pilot only.
  • sf_bq459 (WORD_VECTORS_US) [tier-2 pilot verdict: UNFIXABLE/other-structural] — repair_cli schema for WORD_VECTORS_US confirms NATURE has id/date/title/body, GLOVE_VECTORS has word/vector, and WORD_FREQUENCIES has word/frequency -- every identifier the query references already resolves correctly, so this is not the tier-1 dotted-identifier gap; the actual failure (positions 233 and 685: unexpected * ) is the malformed operator sequence "frequency" * * - 0.4 (a corrupted power/exponentiation expression, evidently meant as POWER(frequency, -0.4)), which is a non-identifier token and cannot be repaired by renaming/requalifying identifiers alone. explorer →
  • sf_bq457 (LIBRARIES_IO) [tier-2 pilot verdict: UNFIXABLE/other-structural] — orig.sql is truncated mid string-literal inside the dependency_name IN (...) list (file ends '...launchdark' with no closing quote, no closing paren for IN(...), and no closing paren for WHERE); running check-ast on orig vs an identical copy fails at the tokenizer itself with TokenError: Error tokenizing 'rkly', 'launchdarkly', 'launchdarkly', 'launchdar', so the query cannot even be parsed into identifier nodes -- there is no identifier-only substitution (per the repair contract, only column/table/qualifier renames are permitted) that can supply the missing quote, parens, and the additional WHERE predicates on platform/library the question implies (artifact names, library names, platforms, languages) without adding structure. explorer →
  • sf_bq292 (CRYPTO) [tier-2 pilot verdict: UNFIXABLE/other-structural] — DATE_TRUNC(<part>, <expr>) has its two argument roles swapped in content -- the frozen literal argument reads 'BLOCK_TIMESTAMP', which repair_cli explain confirms Snowflake rejects as not a valid date/time component (error 002151), and no identifier-only rename of the second-argument column (verified with MONTH->block_timestamp, which passes check-ast but still fails explain on that literal) can repair a bad literal, since check-ast rejects any edit to that literal as a structural skeleton change. explorer →
  • sf_bq358 (NEW_YORK_CITIBIKE_1) [tier-2 pilot verdict: UNFIXABLE/other-structural] — CITIBIKE_TRIPS.starttime/stoptime are stored as raw NUMBER epoch values (repair_cli run on a probe query returned starttime=1397995463000000, a plain JSON integer, not a quoted timestamp string), and this Snowflake account rejects any numeric argument to TO_DATE at compile time -- confirmed via probe TO_DATE(1397995463) failing with the identical 001007 invalid type error as TO_DATE("starttime") -- so no column rename can satisfy the predicate; CITIBIKE_TRIPS has no other date/timestamp-typed column and the schema has no second trips table to swap to, and fixing it needs a CAST/TO_TIMESTAMP change which the identifier-only contract forbids. explorer →
• Mirage: compiles + runs, never matches — 0.7% (2 of 300)
  tier-1's own ran_no_match outcome, whole 300-pair sample; the tier-2 pilot separately saw 3 more RAN_NO_MATCH verdicts among its escalate_* pairs (qualitative, not added to this share).
  • sf_local019 (WWE) [tier-2 pilot verdict: RAN_NO_MATCH] — repair_cli schema for WWE has no TITLE_IDS table, only BELTS(id,name) as the id/name table MATCHES.title_id references, and BELTS contains 20 rows with NXT in the name (e.g. NXT Championship, Interim NXT Cruiserweight Championship per explore2.sql ILIKE query) but none named exactly NXT, so after the TITLE_IDS to BELTS substitution the query compiles and runs (explain ok, run n_rows=0) but the literal equality name = NXT cannot match any belt name without changing the literal or operator, which is out of scope for identifier-only repair. explorer →
  • sf_bq321 (IDC) [tier-2 pilot verdict: RAN_NO_MATCH] — repair_cli schema for sf_bq321 lists DICOM_ALL.SeriesDescription (not SeriesInstanceUID) as the column holding values like DWI/T2 Weighted Axial, so substituting it is the semantically correct identifier fix and it compiles+runs (match=0), but the WHERE clause ANDs three equality checks against that same single-valued column with mutually exclusive literals, which no row can satisfy simultaneously regardless of which real column is chosen -- the query needs OR/IN, a structural change no identifier substitution can supply. explorer →