R-ORD NOT_EQUAL arbitrary-cut
student_club · mini_dev_postgresql from https://bird-bench.oss-cn-beijing.aliyuncs.com/minidev.zip (sha256 cc48ba16838204e4e214512030cb572eeb5f7bcdd999bae4b9b6ff12ec13b92f, downloaded 2026-09-07), member minidev/MINIDEV/mini_dev_postgresql.json
Which event has the lowest cost?
the hint the set supplies: event refers to event_name; lowest cost means MIN(cost)
NOT_EQUAL states that these two statements disagree on this data under this rule. It does not state which of them is wrong.
SELECT T1.event_name FROM event AS T1 INNER JOIN budget AS T2 ON T1.event_id = T2.link_to_event INNER JOIN expense AS T3 ON T2.budget_id = T3.link_to_budget ORDER BY T3.cost NULLS FIRST LIMIT 1sha256:56406324bb087daab39eca2d80eb40a285f0ba16585dc9ae96e7e4c82cf0b7c5
SELECT e.event_name
FROM event e
JOIN expense ex ON e.event_id = ex.link_to_budget
JOIN budget b ON ex.link_to_budget = b.link_to_event
GROUP BY e.event_name
ORDER BY MIN(ex.cost) LIMIT 1;sha256:ed4550f614990c6f3567159ff69c95b39b2f6c54f23ee3acb1884d04f6992f8b
The marked tokens are where the two texts differ. Two statements that differ everywhere can return the same rows, and two that differ in one token can return other rows; the verdict above is read off the results.
from counterexample.json, 1 row, up to 25 shown per side
| side | event_nametext |
|---|---|
| gold | November Speaker |
from counterexample.json, 0 rows, up to 25 shown per side
| side | event_nametext |
|---|---|
| no rows |
set(second_rows) == set(gold_rows), float4/float8 cells as Python float as psycopg2 returns them, numeric as Decimal
https://github.com/bird-bench/mini_dev/blob/main/evaluation/evaluation_ex.py
result_eq: equal row counts and equal column counts, each row unordered as a quick rejection, then the two equal as a list when the gold text holds ORDER BY and as a multiset otherwise, under some permutation of the columns; DISTINCT is not stripped and re-executed, and the cells are PostgreSQL's as psycopg2 returns them
ruiqi-zhong/test-suite-sql-eval, exec_eval.py, result_eq, at commit 48cb78ec: https://github.com/ruiqi-zhong/test-suite-sql-eval/blob/48cb78ecf7f610620206283846c76751b18a1326/exec_eval.py
The class states what makes these two results unequal under this rule, read off the two results and nothing else. It does not state which of the two statements is wrong.
from counterexample.json, 1 row
| event_nametext |
|---|
| November Speaker |
from counterexample.json, 0 rows
| event_nametext |
|---|
| no rows |
A smell is a mechanical reason to read this gold statement again. It is a heuristic: it does not state that the statement is wrong, and a maintainer decides.
this statement orders by a text column holding only numbers, and ordering it as a number gives a different answer, so the gold may be sorting 9.5 above 10
no ORDER BY key resolves to a text column
{
"heuristic": true,
"reason": "no ORDER BY key resolves to a text column",
"keys": [
{
"key": "t3.cost",
"column": "expense.cost",
"declared_type": "real",
"not_applicable": "the column is not declared as text"
}
]
}this statement cuts its result at a LIMIT that does not decide which rows come back, so a different but equally correct statement can return other rows and score zero
from smells.json, 3 rows
| September Speaker | 6 |
| November Speaker | 6 |
| October Speaker | 6 |
{
"heuristic": true,
"cut": 1,
"offset": 0,
"distinct_kept": false,
"unbounded_sql": "SELECT t1.event_name, t3.cost AS attestql_ordering_key_0 FROM event t1 JOIN budget t2 ON t1.event_id = t2.link_to_event JOIN expense t3 ON t2.budget_id = t3.link_to_budget ORDER BY t3.cost NULLS FIRST",
"unbounded_rows": 32,
"projected_columns": [
"event_name"
],
"ordering_key_columns": [
"attestql_ordering_key_0"
],
"ordering_keys": [
{
"key": "t3.cost",
"direction": "asc",
"nulls": "first",
"nulls_first_in_effect": true,
"returned_rows_null_in_this_key": 0,
"fires": false
}
],
"tied_at_the_cut": {
"positions": [
0,
1,
2
],
"tied_rows": 3,
"distinct_projected_answers": 3,
"rows": [
[
{
"type": "str",
"value": "September Speaker"
}
],
[
{
"type": "str",
"value": "November Speaker"
}
],
[
{
"type": "str",
"value": "October Speaker"
}
]
]
},
"case": "tie-at-the-cut"
}rerun over the same rows in another physical order this statement gives another answer, so its result depends on how the rows are stored and not only on the data
{
"heuristic": true,
"rule": "R-ORD",
"baseline_result_hash": "sha256:56406324bb087daab39eca2d80eb40a285f0ba16585dc9ae96e7e4c82cf0b7c5",
"baseline_result": {
"columns": [
{
"name": "event_name",
"declared_type": "text"
}
],
"row_count": 1,
"truncated": false,
"rows_shown": 1,
"rows": [
[
{
"type": "str",
"value": "November Speaker"
}
]
],
"result_hash": "sha256:56406324bb087daab39eca2d80eb40a285f0ba16585dc9ae96e7e4c82cf0b7c5"
},
"planner_statistics": {
"expense": {
"last_analyze": null,
"last_autoanalyze": null,
"n_mod_since_analyze": 32
},
"event": {
"last_analyze": null,
"last_autoanalyze": null,
"n_mod_since_analyze": 42
},
"budget": {
"last_analyze": null,
"last_autoanalyze": null,
"n_mod_since_analyze": 52
}
},
"shuffle": {
"seed": "1",
"row_limit": 300000,
"tables": [
"expense",
"event",
"budget"
],
"tables_not_shuffled": [],
"tables_skipped_for_size": {
"laptimes": 400524,
"legalities": 427907,
"posthistory": 303155,
"trans": 1056320,
"yearmonth": 383282
},
"tables_not_reached_by_a_copy": {}
},
"shuffled_copies": {
"run": true,
"verdict": "equal",
"differs": false,
"result_hash": "sha256:56406324bb087daab39eca2d80eb40a285f0ba16585dc9ae96e7e4c82cf0b7c5",
"result": {
"columns": [
{
"name": "event_name",
"declared_type": "text"
}
],
"row_count": 1,
"truncated": false,
"rows_shown": 1,
"rows": [
[
{
"type": "str",
"value": "November Speaker"
}
]
],
"result_hash": "sha256:56406324bb087daab39eca2d80eb40a285f0ba16585dc9ae96e7e4c82cf0b7c5"
}
},
"plan_variant": {
"run": false,
"reason": "the plan variant was not asked for"
}
}SELECT T1.event_name FROM event AS T1 INNER JOIN budget AS T2 ON T1.event_id = T2.link_to_event INNER JOIN expense AS T3 ON T2.budget_id = T3.link_to_budget ORDER BY T3.cost NULLS FIRST LIMIT 1result_hash sha256:56406324bb087daab39eca2d80eb40a285f0ba16585dc9ae96e7e4c82cf0b7c5 recomputed from this JSON: match
record_hash sha256:fb8295833bfea2500ef0f7bc9cb7cc7fb7a34781177576f306afb20e9349e402 recomputed from this JSON: match
from evidence-gold.json, 1 row
| event_nametext |
|---|
| November Speaker |
SELECT e.event_name
FROM event e
JOIN expense ex ON e.event_id = ex.link_to_budget
JOIN budget b ON ex.link_to_budget = b.link_to_event
GROUP BY e.event_name
ORDER BY MIN(ex.cost) LIMIT 1;result_hash sha256:ed4550f614990c6f3567159ff69c95b39b2f6c54f23ee3acb1884d04f6992f8b recomputed from this JSON: match
record_hash sha256:dc35e74f8ad1dbb7c4c9d584ed9961041391f99ec5d85a5f31e239cbccca7688 recomputed from this JSON: match
from evidence-second.json, 0 rows
| event_nametext |
|---|
| no rows |
re-run this statement read-only against PostgreSQL 16.15 (Debian 16.15-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit | server=172.17.0.2/32:5432 | database=bird under the session settings and over the data this record's fixture digest names, and compare the two results under R-ORD
re-run this statement read-only against PostgreSQL 16.15 (Debian 16.15-1.pgdg13+2) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit | server=172.17.0.2/32:5432 | database=bird under the session settings and over the data this record's fixture digest names, and compare the two results under R-ORD