R-ORD NOT_EQUAL
questions
What are the speed in which attacks are put together of the top 4 teams with the highest build Up Play Speed?
the hint the set supplies: speed in which attacks are put together refers to buildUpPlaySpeed;highest build up play speed refers to MAX(buildUpPlaySpeed)
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.buildUpPlaySpeed FROM Team_Attributes AS t1 INNER JOIN Team AS t2 ON t1.team_api_id = t2.team_api_id ORDER BY t1.buildUpPlaySpeed ASC LIMIT 4sha256:8d975647a753e3980b6c60f43d0c5be69ccc5e1d95812a126b413ff3f7c9034b
SELECT t1.buildUpPlaySpeed FROM Team_Attributes AS t1 INNER JOIN Team AS t2 ON t1.team_api_id = t2.team_api_id ORDER BY t1.buildUpPlaySpeed DESC LIMIT 4sha256:946bfbe80492484efc15469782b3de7863c508f6cf64751f24a08c9969f48a06
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, 4 rows, up to 25 shown per side
| side | buildupplayspeedINTEGER |
|---|---|
| gold | 20 |
| gold | 23 |
| gold | 31 |
| gold | 44 |
from counterexample.json, 4 rows, up to 25 shown per side
| side | buildupplayspeedINTEGER |
|---|---|
| second | 62 |
| second | 70 |
| second | 77 |
| second | 80 |
set(second_rows) == set(gold_rows), REAL cells as Python float as sqlite3 returns them, so Python equality holds 1 == 1.0 == True as the benchmark's own scorer does
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 SQLite's as sqlite3 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, 4 rows
| buildupplayspeedINTEGER |
|---|
| 20 |
| 23 |
| 31 |
| 44 |
from counterexample.json, 4 rows
| buildupplayspeedINTEGER |
|---|
| 80 |
| 77 |
| 70 |
| 62 |
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": "t1.buildUpPlaySpeed",
"column": "Team_Attributes.buildUpPlaySpeed",
"declared_type": "INTEGER",
"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
{
"heuristic": true,
"cut": 4,
"offset": 0,
"distinct_kept": false,
"unbounded_sql": "SELECT t1.buildUpPlaySpeed, t1.buildUpPlaySpeed AS attestql_ordering_key_0 FROM Team_Attributes AS t1 INNER JOIN Team AS t2 ON t1.team_api_id = t2.team_api_id ORDER BY t1.buildUpPlaySpeed ASC",
"unbounded_rows": 10,
"projected_columns": [
"buildupplayspeed"
],
"ordering_key_columns": [
"attestql_ordering_key_0"
],
"ordering_keys": [
{
"key": "t1.buildUpPlaySpeed",
"direction": "asc",
"nulls": "first",
"nulls_first_in_effect": true,
"returned_rows_null_in_this_key": 0,
"fires": false
}
],
"case": null
}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:8d975647a753e3980b6c60f43d0c5be69ccc5e1d95812a126b413ff3f7c9034b",
"baseline_result": {
"columns": [
{
"name": "buildupplayspeed",
"declared_type": "INTEGER"
}
],
"row_count": 4,
"truncated": false,
"rows_shown": 4,
"rows": [
[
{
"type": "int",
"value": 20
}
],
[
{
"type": "int",
"value": 23
}
],
[
{
"type": "int",
"value": 31
}
],
[
{
"type": "int",
"value": 44
}
]
],
"result_hash": "sha256:8d975647a753e3980b6c60f43d0c5be69ccc5e1d95812a126b413ff3f7c9034b"
},
"planner_statistics": {},
"shuffle": {
"seed": "1",
"row_limit": 300000,
"tables": [
"Team_Attributes",
"Team"
],
"tables_not_shuffled": [],
"tables_skipped_for_size": {},
"tables_not_reached_by_a_copy": {
"main.y": "the statement names this table's schema, and a name that states its schema is read from that schema whatever TEMP holds, so the rerun reads this table and not a copy of it"
}
},
"shuffled_copies": {
"run": true,
"verdict": "equal",
"differs": false,
"result_hash": "sha256:8d975647a753e3980b6c60f43d0c5be69ccc5e1d95812a126b413ff3f7c9034b",
"result": {
"columns": [
{
"name": "buildupplayspeed",
"declared_type": "INTEGER"
}
],
"row_count": 4,
"truncated": false,
"rows_shown": 4,
"rows": [
[
{
"type": "int",
"value": 20
}
],
[
{
"type": "int",
"value": 23
}
],
[
{
"type": "int",
"value": 31
}
],
[
{
"type": "int",
"value": 44
}
]
],
"result_hash": "sha256:8d975647a753e3980b6c60f43d0c5be69ccc5e1d95812a126b413ff3f7c9034b"
}
},
"plan_variant": {
"run": false,
"reason": "the plan variant was not asked for"
}
}SELECT t1.buildUpPlaySpeed FROM Team_Attributes AS t1 INNER JOIN Team AS t2 ON t1.team_api_id = t2.team_api_id ORDER BY t1.buildUpPlaySpeed ASC LIMIT 4result_hash sha256:8d975647a753e3980b6c60f43d0c5be69ccc5e1d95812a126b413ff3f7c9034b recomputed from this JSON: match
record_hash sha256:09ecdaeeb5ca4d13b4a8b87f75535eb297b21ef560c44f5da167eb7f04206997 recomputed from this JSON: match
from evidence-gold.json, 4 rows
| buildupplayspeedINTEGER |
|---|
| 20 |
| 23 |
| 31 |
| 44 |
SELECT t1.buildUpPlaySpeed FROM Team_Attributes AS t1 INNER JOIN Team AS t2 ON t1.team_api_id = t2.team_api_id ORDER BY t1.buildUpPlaySpeed DESC LIMIT 4result_hash sha256:946bfbe80492484efc15469782b3de7863c508f6cf64751f24a08c9969f48a06 recomputed from this JSON: match
record_hash sha256:763ec5885948bc0ced02ab7334ee2ee809a4a310d3538ddd4869574ebe3c218a recomputed from this JSON: match
from evidence-second.json, 4 rows
| buildupplayspeedINTEGER |
|---|
| 80 |
| 77 |
| 70 |
| 62 |
re-run this statement read-only against SQLite 3.53.4 | file=/private/tmp/attestql-site-sandbox/demo/fixture.sqlite | size=69632 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 SQLite 3.53.4 | file=/private/tmp/attestql-site-sandbox/demo/fixture.sqlite | size=69632 under the session settings and over the data this record's fixture digest names, and compare the two results under R-ORD