R-SET GOLD-ONLY float-aggregate-order
debit_card_specializing · 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
Who is the top spending customer and how much is the average price per single item purchased by this customer? What currency was being used?
the hint the set supplies: average price per single item = Total(price) / Total(amount)
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.
SELECT T2.CustomerID, SUM(T2.Price / NULLIF(T2.Amount, 0)), T1.Currency FROM customers AS T1 INNER JOIN transactions_1k AS T2 ON T1.CustomerID = T2.CustomerID WHERE T2.CustomerID = (SELECT CustomerID FROM yearmonth ORDER BY Consumption DESC NULLS LAST LIMIT 1) GROUP BY T2.CustomerID, T1.Currencythis statement states no ordering of its own
sha256:8fef12f188ec1b51bd959b2f141e478d9cf5b3492dc09e7852a39fc8f874e72b
from evidence-gold.json, 1 row
| customeridint8 | sumfloat8 | currencytext |
|---|---|---|
| 12459 | 203.85607635517042 | CZK |
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
the statement states no top level ORDER BY
{
"heuristic": true,
"reason": "the statement states no top level ORDER BY"
}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
the statement states no LIMIT
{
"heuristic": true,
"reason": "the statement states no LIMIT"
}this statement aggregates floating point numbers, so its last digits depend on the order the rows were summed in; the values agree to six significant digits
from smells.json, 1 row
| 12459 | 203.85607635517044 | CZK |
{
"heuristic": true,
"rule": "R-SET",
"baseline_result_hash": "sha256:8fef12f188ec1b51bd959b2f141e478d9cf5b3492dc09e7852a39fc8f874e72b",
"baseline_result": {
"columns": [
{
"name": "customerid",
"declared_type": "int8"
},
{
"name": "sum",
"declared_type": "float8"
},
{
"name": "currency",
"declared_type": "text"
}
],
"row_count": 1,
"truncated": false,
"rows_shown": 1,
"rows": [
[
{
"type": "int",
"value": 12459
},
{
"type": "dec",
"value": "203.85607635517042"
},
{
"type": "str",
"value": "CZK"
}
]
],
"result_hash": "sha256:8fef12f188ec1b51bd959b2f141e478d9cf5b3492dc09e7852a39fc8f874e72b"
},
"planner_statistics": {
"customers": {
"last_analyze": null,
"last_autoanalyze": null,
"n_mod_since_analyze": 32461
},
"transactions_1k": {
"last_analyze": null,
"last_autoanalyze": null,
"n_mod_since_analyze": 1000
},
"yearmonth": {
"last_analyze": null,
"last_autoanalyze": null,
"n_mod_since_analyze": 383282
}
},
"shuffle": {
"seed": "1",
"row_limit": 300000,
"tables": [
"customers",
"transactions_1k",
"yearmonth"
],
"tables_not_shuffled": [
"yearmonth"
],
"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": "not_equal",
"differs": true,
"result_hash": "sha256:8fef12f188ec1b51bd959b2f141e478d9cf5b3492dc09e7852a39fc8f874e72b",
"result": {
"columns": [
{
"name": "customerid",
"declared_type": "int8"
},
{
"name": "sum",
"declared_type": "float8"
},
{
"name": "currency",
"declared_type": "text"
}
],
"row_count": 1,
"truncated": false,
"rows_shown": 1,
"rows": [
[
{
"type": "int",
"value": 12459
},
{
"type": "dec",
"value": "203.85607635517044"
},
{
"type": "str",
"value": "CZK"
}
]
],
"result_hash": "sha256:8fef12f188ec1b51bd959b2f141e478d9cf5b3492dc09e7852a39fc8f874e72b"
}
},
"plan_variant": {
"run": false,
"reason": "the plan variant was not asked for"
},
"float_cells": [
{
"row": 0,
"column": "sum",
"declared_type": "float8",
"baseline": "203.85607635517042",
"rerun": "203.85607635517044"
}
],
"significant_digits": 6
}SELECT T2.CustomerID, SUM(T2.Price / NULLIF(T2.Amount, 0)), T1.Currency FROM customers AS T1 INNER JOIN transactions_1k AS T2 ON T1.CustomerID = T2.CustomerID WHERE T2.CustomerID = (SELECT CustomerID FROM yearmonth ORDER BY Consumption DESC NULLS LAST LIMIT 1) GROUP BY T2.CustomerID, T1.Currencyresult_hash sha256:8fef12f188ec1b51bd959b2f141e478d9cf5b3492dc09e7852a39fc8f874e72b recomputed from this JSON: match
record_hash sha256:8a5528145a72b2fed6714e9a3b5c1c2f441bffd1701d8034921d9b85ee4458b2 recomputed from this JSON: match
from evidence-gold.json, 1 row
| customeridint8 | sumfloat8 | currencytext |
|---|---|---|
| 12459 | 203.85607635517042 | CZK |
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-SET