q37

R-ORD GOLD-ONLY arbitrary-cut

california_schools · mini_dev_pg-00000-of-00001 from https://huggingface.co/datasets/birdsql/bird_mini_dev/resolve/f65faf4ae3b638c1fa6df1d3370c8d92c8366301/data/mini_dev_pg-00000-of-00001.json (commit f65faf4a, downloaded 2026-09-08)

The question

What is the complete address of the school with the lowest excellence rate? Indicate the Street, City, Zip and State.

the hint the set supplies: Execellence Rate = NumGE1500 / NumTstTakr; complete address has Street, City, State, Zip code

This question was audited without a prediction beside it, so there is nothing to compare the gold with. The probes below read the gold alone.

The statements

gold

SELECT T2.Street, T2.City, T2.State, T2.Zip FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode ORDER BY CAST(T1.NumGE1500 AS REAL) / NULLIF(T1.NumTstTakr, 0) ASC NULLS FIRST LIMIT 1
t1.numge1500::real / (NULLIF(t1.numtsttakr, 0))
ascending

sha256:fa1aa0fe0a319e2b75749b90fac6a44f32dc33a545113a0e07d5965e61ac148c

The results

gold, 1 row

from evidence-gold.json, 1 row

streettext citytext statetext ziptext
2125 Jefferson Avenue Berkeley CA 94703-1414

The probes

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.

ordering-over-numeric-text not applicable

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

what it measured 
{
  "heuristic": true,
  "reason": "no ORDER BY key resolves to a text column",
  "keys": [
    {
      "key": "t1.numge1500::real / (NULLIF(t1.numtsttakr, 0))",
      "not_applicable": "the key is an expression and not a column"
    }
  ]
}

arbitrary-cut fired

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, 10 rows

2800 Nicolaus Road, Suite 100 Lincoln CA 95648-1757 NULL
6402 Linda Vista Road San Diego CA 92111-7320 NULL
2601 Rosamond Boulevard Rosamond CA 93560-7688 NULL
4431 Mount Herbert Avenue San Diego CA 92117-4728 NULL
10170 Huennekens Street San Diego CA 92121-2964 NULL
4100 Normal Street, Annex 14 San Diego CA 92103 NULL
3939 Conde Street San Diego CA 92110-3012 NULL
3773 El Cajon Boulevard San Diego CA 92105 NULL
1144 West Lacey Boulevard Hanford CA 93230-5956 NULL
418 West 8th Street Hanford CA 93230-4536 NULL
what it measured 
{
  "heuristic": true,
  "cut": 1,
  "offset": 0,
  "distinct_kept": false,
  "unbounded_sql": "SELECT t2.street, t2.city, t2.state, t2.zip, t1.numge1500::real / (NULLIF(t1.numtsttakr, 0)) AS attestql_ordering_key_0 FROM satscores t1 JOIN schools t2 ON t1.cds = t2.cdscode ORDER BY t1.numge1500::real / (NULLIF(t1.numtsttakr, 0)) ASC NULLS FIRST",
  "unbounded_rows": 2269,
  "projected_columns": [
    "street",
    "city",
    "state",
    "zip"
  ],
  "ordering_key_columns": [
    "attestql_ordering_key_0"
  ],
  "ordering_keys": [
    {
      "key": "t1.numge1500::real / (NULLIF(t1.numtsttakr, 0))",
      "direction": "asc",
      "nulls": "first",
      "nulls_first_in_effect": true,
      "returned_rows_null_in_this_key": 1,
      "fires": true
    }
  ],
  "returned_rows_with_a_null_key": [
    [
      {
        "type": "str",
        "value": "2800 Nicolaus Road, Suite 100"
      },
      {
        "type": "str",
        "value": "Lincoln"
      },
      {
        "type": "str",
        "value": "CA"
      },
      {
        "type": "str",
        "value": "95648-1757"
      }
    ]
  ],
  "tied_at_the_cut": {
    "positions": [
      0,
      1,
      2,
      3,
      4,
      5,
      6,
      7,
      8,
      9
    ],
    "tied_rows": 596,
    "distinct_projected_answers": 561,
    "rows": [
      [
        {
          "type": "str",
          "value": "2800 Nicolaus Road, Suite 100"
        },
        {
          "type": "str",
          "value": "Lincoln"
        },
        {
          "type": "str",
          "value": "CA"
        },
        {
          "type": "str",
          "value": "95648-1757"
        }
      ],
      [
        {
          "type": "str",
          "value": "6402 Linda Vista Road"
        },
        {
          "type": "str",
          "value": "San Diego"
        },
        {
          "type": "str",
          "value": "CA"
        },
        {
          "type": "str",
          "value": "92111-7320"
        }
      ],
      [
        {
          "type": "str",
          "value": "2601 Rosamond Boulevard"
        },
        {
          "type": "str",
          "value": "Rosamond"
        },
        {
          "type": "str",
          "value": "CA"
        },
        {
          "type": "str",
          "value": "93560-7688"
        }
      ],
      [
        {
          "type": "str",
          "value": "4431 Mount Herbert Avenue"
        },
        {
          "type": "str",
          "value": "San Diego"
        },
        {
          "type": "str",
          "value": "CA"
        },
        {
          "type": "str",
          "value": "92117-4728"
        }
      ],
      [
        {
          "type": "str",
          "value": "10170 Huennekens Street"
        },
        {
          "type": "str",
          "value": "San Diego"
        },
        {
          "type": "str",
          "value": "CA"
        },
        {
          "type": "str",
          "value": "92121-2964"
        }
      ],
      [
        {
          "type": "str",
          "value": "4100 Normal Street, Annex 14"
        },
        {
          "type": "str",
          "value": "San Diego"
        },
        {
          "type": "str",
          "value": "CA"
        },
        {
          "type": "str",
          "value": "92103"
        }
      ],
      [
        {
          "type": "str",
          "value": "3939 Conde Street"
        },
        {
          "type": "str",
          "value": "San Diego"
        },
        {
          "type": "str",
          "value": "CA"
        },
        {
          "type": "str",
          "value": "92110-3012"
        }
      ],
      [
        {
          "type": "str",
          "value": "3773 El Cajon Boulevard"
        },
        {
          "type": "str",
          "value": "San Diego"
        },
        {
          "type": "str",
          "value": "CA"
        },
        {
          "type": "str",
          "value": "92105"
        }
      ],
      [
        {
          "type": "str",
          "value": "1144 West Lacey Boulevard"
        },
        {
          "type": "str",
          "value": "Hanford"
        },
        {
          "type": "str",
          "value": "CA"
        },
        {
          "type": "str",
          "value": "93230-5956"
        }
      ],
      [
        {
          "type": "str",
          "value": "418 West 8th Street"
        },
        {
          "type": "str",
          "value": "Hanford"
        },
        {
          "type": "str",
          "value": "CA"
        },
        {
          "type": "str",
          "value": "93230-4536"
        }
      ]
    ]
  },
  "case": "tie-at-the-cut, null-first"
}

not-a-function-of-the-data quiet

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

what it measured 
{
  "heuristic": true,
  "rule": "R-ORD",
  "baseline_result_hash": "sha256:fa1aa0fe0a319e2b75749b90fac6a44f32dc33a545113a0e07d5965e61ac148c",
  "baseline_result": {
    "columns": [
      {
        "name": "street",
        "declared_type": "text"
      },
      {
        "name": "city",
        "declared_type": "text"
      },
      {
        "name": "state",
        "declared_type": "text"
      },
      {
        "name": "zip",
        "declared_type": "text"
      }
    ],
    "row_count": 1,
    "truncated": false,
    "rows_shown": 1,
    "rows": [
      [
        {
          "type": "str",
          "value": "2125 Jefferson Avenue"
        },
        {
          "type": "str",
          "value": "Berkeley"
        },
        {
          "type": "str",
          "value": "CA"
        },
        {
          "type": "str",
          "value": "94703-1414"
        }
      ]
    ],
    "result_hash": "sha256:fa1aa0fe0a319e2b75749b90fac6a44f32dc33a545113a0e07d5965e61ac148c"
  },
  "planner_statistics": {
    "satscores": {
      "last_analyze": null,
      "last_autoanalyze": "2026-09-08 05:19:51.562503+00",
      "n_mod_since_analyze": 0
    },
    "schools": {
      "last_analyze": null,
      "last_autoanalyze": "2026-09-08 05:19:52.240861+00",
      "n_mod_since_analyze": 0
    }
  },
  "shuffle": {
    "seed": "1",
    "row_limit": 300000,
    "tables": [
      "satscores",
      "schools"
    ],
    "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:fa1aa0fe0a319e2b75749b90fac6a44f32dc33a545113a0e07d5965e61ac148c",
    "result": {
      "columns": [
        {
          "name": "street",
          "declared_type": "text"
        },
        {
          "name": "city",
          "declared_type": "text"
        },
        {
          "name": "state",
          "declared_type": "text"
        },
        {
          "name": "zip",
          "declared_type": "text"
        }
      ],
      "row_count": 1,
      "truncated": false,
      "rows_shown": 1,
      "rows": [
        [
          {
            "type": "str",
            "value": "2125 Jefferson Avenue"
          },
          {
            "type": "str",
            "value": "Berkeley"
          },
          {
            "type": "str",
            "value": "CA"
          },
          {
            "type": "str",
            "value": "94703-1414"
          }
        ]
      ],
      "result_hash": "sha256:fa1aa0fe0a319e2b75749b90fac6a44f32dc33a545113a0e07d5965e61ac148c"
    }
  },
  "plan_variant": {
    "run": false,
    "reason": "the plan variant was not asked for"
  }
}

The evidence records

gold: evidence-gold.json

SELECT T2.Street, T2.City, T2.State, T2.Zip FROM satscores AS T1 INNER JOIN schools AS T2 ON T1.cds = T2.CDSCode ORDER BY CAST(T1.NumGE1500 AS REAL) / NULLIF(T1.NumTstTakr, 0) ASC NULLS FIRST LIMIT 1
statement read from
data/questions/mini_dev_pg-00000-of-00001.json
digest
sha256:7fa740ef9225389cff6c34432120e8325d0ca3008d73db1ae38731234bc10da7
origin
https://huggingface.co/datasets/birdsql/bird_mini_dev/resolve/f65faf4ae3b638c1fa6df1d3370c8d92c8366301/data/mini_dev_pg-00000-of-00001.json (commit f65faf4a, downloaded 2026-09-08), 2026-01-18T08:44:25Z

result_hash sha256:fa1aa0fe0a319e2b75749b90fac6a44f32dc33a545113a0e07d5965e61ac148c recomputed from this JSON: match

record_hash sha256:06ddd0a252017f249d93d50e73fe6b69177a8baf077b68514f1eab4f5e84996f recomputed from this JSON: match

the result this record holds, 1 row

from evidence-gold.json, 1 row

streettext citytext statetext ziptext
2125 Jefferson Avenue Berkeley CA 94703-1414
what ran, and where
run
audit-ed50a84a-c286-4cbb-ac89-ab3111058ad5
executed at
2026-09-08T05:20:03.217794+00:00
data as of
2026-09-08T05:18:53.188748+00:00
backend at checkout
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
backend that answered
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
database role
auditor
replay rule
R-ORD
question set version
sha256:7fa740ef9225389cff6c34432120e8325d0ca3008d73db1ae38731234bc10da7
validator
audit:libpg_query-parse
checks run
parses_as_exactly_one_statement, the_one_statement_is_a_select, no_placeholder_without_a_bound_parameter
statement timeout
30000 ms
rows
1 row
the session it ran under
engine
postgresql
time_zone
Etc/UTC
date_style
ISO, MDY
interval_style
postgres
extra_float_digits
1
database_collation
en_US.utf8
work_mem
4096
hash_mem_multiplier
2

recorded beside them

statement_timeout
0
search_path
"$user", public
server_version
16.15 (Debian 16.15-1.pgdg13+2)
server_version_num
160015
transaction_read_only
on
max_parallel_workers_per_gather
2
server_encoding
UTF8
datlocprovider
c
daticulocale
datcollversion
2.41
the rendering and the data
version
attestql/audit/2
numeric_scale
6
timestamp_format
%Y-%m-%dT%H:%M:%S.%fZ
timezone
UTC
null_rendering
NULL
encoding
utf-8
schema digest
sha256:d1edf2aece252560ac7c0d113d10898ecea02108c72f871549792071611036f9
source file sha256
sha256:31b1da211849d24a57c9af7636da46a5b82fc8a3ca1542bb3ebd8775e9a31cec
rows in public.satscores
2269
rows in public.schools
17686

Running these again

gold

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

This question's run

run
audit-ed50a84a-c286-4cbb-ac89-ab3111058ad5
server
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
question set
mini_dev_pg-00000-of-00001 from https://huggingface.co/datasets/birdsql/bird_mini_dev/resolve/f65faf4ae3b638c1fa6df1d3370c8d92c8366301/data/mini_dev_pg-00000-of-00001.json (commit f65faf4a, downloaded 2026-09-08)
replay rule
R-ORD

the run this question belongs to

The JSON this page was rendered from