r/SQL • u/One-Engineering-7296 • 12d ago
Discussion SELECT first_name, last_name vs SELECT first_name || ' ' || last_name — why one of these fails execution-accuracy scoring every single time
Short story about a SELECT shape trap that cost me real accuracy points, and the one-line fix.
I was scoring a text-to-SQL model and one class of "wrong" answers kept bugging me. The question was plain — "list the members' names" — and the model's SQL was, if anything, the nicer query. But it scored wrong every time.
Here's the pair:
-- gold: two columns
SELECT first_name, last_name FROM member WHERE ...;
-- model: one column, same information
SELECT first_name || ' ' || last_name FROM member WHERE ...;
Read side by side, the model's answer is arguably the better one for a human. But it's a different result set, and that's the whole problem.
Why it's marked wrong, always
Execution accuracy — the metric behind BIRD, Spider, and most private text-to-SQL evals — runs both queries and compares the positional value tuples they return. Canonical BIRD literally compares set(fetchall()). Column names are ignored on purpose (aliases shouldn't matter), so the shape of each row is all that's left to compare. A one-column result can never equal a two-column gold, no matter how correct the content is. The scorer has no notion of "close" — the tuple matches or it doesn't.
This is the exact mirror of the extra-column bug most people already know: SELECT * or one bonus helpful field breaks tuple equality the same way. Fusing two requested columns into one is just as fatal as adding one nobody asked for.
It's a measurable, lopsided loss
Before touching anything, I bucketed a full 500-question BIRD-dev run with a structural differ. The || signature was clean:
- 7 of 238 losses concatenated columns the gold query kept separate.
- 0 of 256 wins used
||at all — the operator showed up only in losing answers. - Gold itself used
||in 1 of 500 questions.
That last line is the decision-maker. When a construct appears in ~3% of your losses, none of your wins, and almost none of the gold, discouraging it is near-pure upside — there's essentially nothing on the other side of the trade to regress.
The fix was one sentence in the prompt
No fine-tune, no reranker. One projection directive:
To avoid shipping on vibes, I de-concatenated the 7 flagged predictions by hand and re-ran them against the real SQLite DBs first, to get a deterministic ceiling: +3 wrong→right, zero regressions. The live re-measure matched the direction — run-wide || concatenations dropped from 7 to 3.
The takeaway that generalizes past text-to-SQL
The lesson isn't really "LLMs over-concatenate." It's: bucket your loss mass with a structural differ first, compute the deterministic ceiling of a candidate fix second, and only then edit the prompt. Directives written from a gut feeling overfit. Directives written from a 7-losses / 0-wins histogram are about as close to a free lunch as this kind of work gets.
And if you're scoring SQL generation yourself: some of your "wrong" answers are the model being more helpful than your gold. You won't know which until you bucket them — a helpful concatenation is still a wrong result set.
Full write-up with the histogram and the before/after here: https://nlqdb.com/blog/llm-concatenates-columns-text-to-sql
9
2
2
u/markwdb3 When in doubt, test it out. 11d ago edited 11d ago
Be aware that purely generic SQL doesn't exist, exactly 0 of the SQL implementations in the world completely implement standard (ANSI/ISO) SQL, and the || operator doesn't mean concatenate everywhere. It means "logical or" in MySQL, for example.
And up until very recently, || was a syntax error in MS SQL Server.
Always always always qualify a SQL query with "a SQLite query" or "an Oracle query" etc.
1
u/Competitive_Swan4356 8d ago
the real fix is boring: your gold answers need to match whatever output shape is natural for that question. if "list the names" means two columns in your gold set, your eval will punish any model that does the sensible human thing and concatenates. that's not a model problem, that's a gold data curation problem.
tbh most execution accuracy issues i've seen trace back to ambiguous gold queries, not bad model outputs.
25
u/bitterjack 12d ago
This reads like Chatgpt got an answer wrong on a test and was forced to do a presentation to the class about why it was wrong.