{"id":"table-groupby-check","version":"1.0.0","description":"Check claimed GROUP BY rows (group keys and aggregate values) over JSON rows; returns the first wrong group or value.","supported_operations":["verify group by result rows","check per group totals claimed by an agent","validate pivot summary counts per category","check grouped sum and count per key in json table","are these group by aggregates correct","verify subtotals by customer and month","בדיקת סיכום לפי קבוצות בטבלה"],"unsupported_operations":["producing the grouped table (this only checks a claimed one)","HAVING filters, ROLLUP, CUBE or GROUPING SETS","grouping by computed expressions or numeric equality of \"1\" and 1","floating-point cells or tolerance comparisons"],"semantics":["Implements ISO/IEC 9075-2:2023 (SQL:2023) set functions and grouping, r58 JSON-rows profile 1.0.0; every result carries reference_version.","Groups are formed by the exact typed tuple of the group_by cells; an absent column is null and all nulls fall in one group (SQL grouping treats nulls as not distinct). 1, \"1\" and true are different keys; strings are compared by exact code units with no normalization.","Set functions per group follow table-aggregate-check: count_star, count, and null-ignoring sum, avg, min, max over safe integers and decimal strings as exact rationals; a group whose cells are all null has sum/avg/min/max null. An empty table has no groups.","row_order is required: first_seen (order of first appearance in rows), key_ascending (null < false < true < integers by value < strings by Unicode code point, compared column by column) or unordered.","Comparison order: for each claimed row i in order: duplicate_group (key equals an earlier claimed key), unexpected_group (no such group), group_order_mismatch (ordered modes: the computed group at position i differs), then aggregate_mismatch for each value j in order; finally missing_group for the first computed group (in first-seen order for unordered) that was not claimed.","Claimed values are null or integer, p/q or decimal strings compared by exact value; computed values are reduced \"n\" or \"n/d\". All rows, claims and numeric columns are validated before any comparison."],"limits":{"max_input_bytes":1048576,"max_rows":10000,"max_columns_per_row":64,"max_group_columns":8,"max_aggregates":16,"max_claimed_rows":10000,"max_numeric_chars":64,"max_value_chars":128},"pricing":{"status":"unpriced","charge_usd":null},"input_schema":{"type":"object","additionalProperties":false,"required":["rows","group_by","aggregates","row_order","claimed_rows"],"properties":{"rows":{"type":"array","maxItems":10000,"items":{"type":"object","maxProperties":64,"additionalProperties":{"type":["null","boolean","integer","string"]}}},"group_by":{"type":"array","minItems":1,"maxItems":8,"items":{"type":"string","minLength":1}},"aggregates":{"type":"array","maxItems":16,"items":{"type":"object","additionalProperties":false,"required":["function"],"properties":{"function":{"type":"string","enum":["count_star","count","sum","avg","min","max"]},"column":{"type":"string","minLength":1}}}},"row_order":{"type":"string","enum":["first_seen","key_ascending","unordered"]},"claimed_rows":{"type":"array","maxItems":10000,"items":{"type":"object","additionalProperties":false,"required":["key","values"],"properties":{"key":{"type":"array","items":{"type":["null","boolean","integer","string"]}},"values":{"type":"array","items":{"type":["string","null"],"maxLength":128}}}}}}},"output_schema":{"type":"object","additionalProperties":false,"required":["valid","reason","first_discrepancy","reference_version"],"properties":{"valid":{"type":"boolean"},"reason":{"type":["string","null"],"enum":[null,"duplicate_group","unexpected_group","group_order_mismatch","aggregate_mismatch","missing_group"]},"first_discrepancy":{"type":["object","null"],"additionalProperties":false,"required":["row_index","group_key","expected_group_key","aggregate_index","function","column","claimed","computed"],"properties":{"row_index":{"type":["integer","null"],"minimum":0},"group_key":{"type":["array","null"],"items":{"type":["null","boolean","integer","string"]}},"expected_group_key":{"type":["array","null"],"items":{"type":["null","boolean","integer","string"]}},"aggregate_index":{"type":["integer","null"],"minimum":0},"function":{"type":["string","null"]},"column":{"type":["string","null"]},"claimed":{"type":["string","null"]},"computed":{"type":["string","null"]}}},"reference_version":{"type":"string","const":"ISO/IEC 9075-2:2023 (SQL:2023) set functions and grouping, r58 JSON-rows profile 1.0.0"}}},"examples":[{"input":{"rows":[{"city":"San Francisco","temp_lo":46},{"city":"San Francisco","temp_lo":43},{"city":"Hayward","temp_lo":37}],"group_by":["city"],"aggregates":[{"function":"count_star"},{"function":"max","column":"temp_lo"}],"row_order":"key_ascending","claimed_rows":[{"key":["Hayward"],"values":["1","37"]},{"key":["San Francisco"],"values":["2","46"]}]},"output":{"valid":true,"reason":null,"first_discrepancy":null,"reference_version":"ISO/IEC 9075-2:2023 (SQL:2023) set functions and grouping, r58 JSON-rows profile 1.0.0"}},{"input":{"rows":[{"region":"north","sales":"10.5"},{"region":null,"sales":"4"},{"sales":"1.5"},{"region":"north","sales":"2"}],"group_by":["region"],"aggregates":[{"function":"sum","column":"sales"}],"row_order":"unordered","claimed_rows":[{"key":["north"],"values":["12.5"]},{"key":[null],"values":["4"]}]},"output":{"valid":false,"reason":"aggregate_mismatch","first_discrepancy":{"row_index":1,"group_key":[null],"expected_group_key":null,"aggregate_index":0,"function":"sum","column":"sales","claimed":"4","computed":"11/2"},"reference_version":"ISO/IEC 9075-2:2023 (SQL:2023) set functions and grouping, r58 JSON-rows profile 1.0.0"}}],"execute_url":"/v1/tools/table-groupby-check/versions/1.0.0/execute"}