R-ORD NOT_EQUAL ordering-over-numeric-text
questions
For the driver who set the fastest lap speed, what is his nationality?
the hint the set supplies: the fastest lap speed refers to (MAX) fastestLapSpeed;
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.nationality FROM drivers AS T1 INNER JOIN results AS T2 ON T2.driverId = T1.driverId ORDER BY T2.fastestLapSpeed DESC LIMIT 1sha256:305ac6bb3143bd1e7623e255e56e39e8fd5f6d78abeef74efeb919785a30c3dd
SELECT T1.nationality FROM drivers AS T1 INNER JOIN results AS T2 ON T2.driverId = T1.driverId ORDER BY CAST(T2.fastestLapSpeed AS REAL) DESC LIMIT 1sha256:087054155a08b8125a54fb7239dacc2736758805fd0d93bef261dc0e8fd5c9e2
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 | nationalityTEXT |
|---|---|
| gold | Norwegian |
from counterexample.json, 1 row, up to 25 shown per side
| side | nationalityTEXT |
|---|---|
| second | Peruvian |
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, 1 row
| nationalityTEXT |
|---|
| Norwegian |
from counterexample.json, 1 row
| nationalityTEXT |
|---|
| Peruvian |
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
from smells.json, 1 row
| Norwegian |
{
"heuristic": true,
"keys": [
{
"key": "T2.fastestLapSpeed",
"column": "results.fastestLapSpeed",
"declared_type": "TEXT",
"not_applicable": null,
"census": {
"rows": 7,
"nulls": 2,
"empty_strings": 0,
"non_numeric": 0,
"pattern": "^-?[0-9]+(\\.[0-9]+)?$"
},
"every_value_is_numeric": true,
"cast_sql": "SELECT T1.nationality FROM drivers AS T1 INNER JOIN results AS T2 ON T2.driverId = T1.driverId ORDER BY CAST(T2.fastestLapSpeed AS REAL) DESC LIMIT 1",
"verdict": "not_equal",
"gold_result": {
"columns": [
{
"name": "nationality",
"declared_type": "TEXT"
}
],
"row_count": 1,
"truncated": false,
"rows_shown": 1,
"rows": [
[
{
"type": "str",
"value": "Norwegian"
}
]
],
"result_hash": "sha256:305ac6bb3143bd1e7623e255e56e39e8fd5f6d78abeef74efeb919785a30c3dd"
},
"cast_result": {
"columns": [
{
"name": "nationality",
"declared_type": "TEXT"
}
],
"row_count": 1,
"truncated": false,
"rows_shown": 1,
"rows": [
[
{
"type": "str",
"value": "Peruvian"
}
]
],
"result_hash": "sha256:087054155a08b8125a54fb7239dacc2736758805fd0d93bef261dc0e8fd5c9e2"
}
}
]
}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": 1,
"offset": 0,
"distinct_kept": false,
"unbounded_sql": "SELECT T1.nationality, T2.fastestLapSpeed AS attestql_ordering_key_0 FROM drivers AS T1 INNER JOIN results AS T2 ON T2.driverId = T1.driverId ORDER BY T2.fastestLapSpeed DESC",
"unbounded_rows": 7,
"projected_columns": [
"nationality"
],
"ordering_key_columns": [
"attestql_ordering_key_0"
],
"ordering_keys": [
{
"key": "T2.fastestLapSpeed",
"direction": "desc",
"nulls": "last",
"nulls_first_in_effect": false,
"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:305ac6bb3143bd1e7623e255e56e39e8fd5f6d78abeef74efeb919785a30c3dd",
"baseline_result": {
"columns": [
{
"name": "nationality",
"declared_type": "TEXT"
}
],
"row_count": 1,
"truncated": false,
"rows_shown": 1,
"rows": [
[
{
"type": "str",
"value": "Norwegian"
}
]
],
"result_hash": "sha256:305ac6bb3143bd1e7623e255e56e39e8fd5f6d78abeef74efeb919785a30c3dd"
},
"planner_statistics": {},
"shuffle": {
"seed": "1",
"row_limit": 300000,
"tables": [
"drivers",
"results"
],
"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:305ac6bb3143bd1e7623e255e56e39e8fd5f6d78abeef74efeb919785a30c3dd",
"result": {
"columns": [
{
"name": "nationality",
"declared_type": "TEXT"
}
],
"row_count": 1,
"truncated": false,
"rows_shown": 1,
"rows": [
[
{
"type": "str",
"value": "Norwegian"
}
]
],
"result_hash": "sha256:305ac6bb3143bd1e7623e255e56e39e8fd5f6d78abeef74efeb919785a30c3dd"
}
},
"plan_variant": {
"run": false,
"reason": "the plan variant was not asked for"
}
}SELECT T1.nationality FROM drivers AS T1 INNER JOIN results AS T2 ON T2.driverId = T1.driverId ORDER BY T2.fastestLapSpeed DESC LIMIT 1result_hash sha256:305ac6bb3143bd1e7623e255e56e39e8fd5f6d78abeef74efeb919785a30c3dd recomputed from this JSON: match
record_hash sha256:f50fbc7fb634fdf570b9e374bc9f29b45f7263ed27de160c539e01c887632b8b recomputed from this JSON: match
from evidence-gold.json, 1 row
| nationalityTEXT |
|---|
| Norwegian |
SELECT T1.nationality FROM drivers AS T1 INNER JOIN results AS T2 ON T2.driverId = T1.driverId ORDER BY CAST(T2.fastestLapSpeed AS REAL) DESC LIMIT 1result_hash sha256:087054155a08b8125a54fb7239dacc2736758805fd0d93bef261dc0e8fd5c9e2 recomputed from this JSON: match
record_hash sha256:42c6eed46b250aaebb7ea0a6df2972a5a8c52c1f3ba1791a9854328ad6dc0bb5 recomputed from this JSON: match
from evidence-second.json, 1 row
| nationalityTEXT |
|---|
| Peruvian |
re-run this statement read-only against SQLite 3.53.4 | file=/Users/hoangle/orca/workspaces/attestql/web-ui/build/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=/Users/hoangle/orca/workspaces/attestql/web-ui/build/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