R-ORD 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 event.event_name
FROM event
JOIN budget ON event.event_id = budget.link_to_event
JOIN expense ON budget.budget_id = expense.link_to_budget
ORDER BY expense.cost ASC
LIMIT 1sha256:56406324bb087daab39eca2d80eb40a285f0ba16585dc9ae96e7e4c82cf0b7c5
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, 0 rows, up to 25 shown per side
| side | event_nametext |
|---|---|
| no rows |
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
from counterexample.json, 1 row
| event_nametext |
|---|
| November Speaker |
from counterexample.json, 1 row
| event_nametext |
|---|
| November Speaker |
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:e638015810383435c92d8f355bb5ed28890ff2600502b2aca08c791559007156 recomputed from this JSON: match
from evidence-gold.json, 1 row
| event_nametext |
|---|
| November Speaker |
SELECT event.event_name
FROM event
JOIN budget ON event.event_id = budget.link_to_event
JOIN expense ON budget.budget_id = expense.link_to_budget
ORDER BY expense.cost ASC
LIMIT 1result_hash sha256:56406324bb087daab39eca2d80eb40a285f0ba16585dc9ae96e7e4c82cf0b7c5 recomputed from this JSON: match
record_hash sha256:7fb7e6b1a346701b0c28f0262962676d5ee0f265cc27ee64a5e3a88ffb93f40a recomputed from this JSON: match
from evidence-second.json, 1 row
| event_nametext |
|---|
| November Speaker |
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