fix(mysql): type an aggregate under group by by its argument by colinhacks · Pull Request #120 · colinhacks/reylo

colinhacks · 2026-10-24T04:24:26Z

MySQL typed sum, avg, min, max, json_arrayagg and json_objectagg under a GROUP BY as nullable, because the server flags them nullable whatever their argument. Every group of a plain GROUP BY holds at least one row, so sum, avg, min and max are as NULL as their argument there, and the two JSON aggregates are never NULL. Postgres already follows this rule.

Column	Before	After
`sum(p.id)`, `avg(p.id)`, `min(p.title)` with `group by p.user_id`	nullable	non-null
`sum(distinct p.id)`, and `max(p.id)` under `select distinct` or after `having count(*) > 1`	nullable	non-null
`max(p.note)` with `where p.note is not null`, and `max(p.title)` over `users u left join posts p` reduced by `where p.id > 0`	nullable	non-null
`json_objectagg(p.title, p.note)` with `group by`	nullable	non-null
`sum(max(p.id)) over ()` in a grouped query	nullable	non-null
`max(p.published_at)` (a nullable column), and `max(p.title)` over an unreduced `left join`	nullable	nullable
`sum(p.id)` with no `group by`, and with `group by … with rollup`	nullable	nullable
`var_samp`, `stddev_pop`, `group_concat`	nullable	nullable (the server's flag)

The rule. For a plain SELECT with a GROUP BY and no ROLLUP, grouped_probe (engine/mysql/src/mysql_nulls.rs) writes a statement that selects each such aggregate's argument from the same FROM and WHERE, with no GROUP BY, HAVING, DISTINCT, ORDER BY or LIMIT. Those are the rows the aggregate reads before they are grouped. grouped_flags (engine/mysql/src/mysql.rs) prepares it, and the aggregate is non-null where the argument's NOT NULL flag is set, or where the WHERE proves a bare column. The outer joins a strict WHERE reduces are read reduced, as everywhere else. This is the same probe shape as the window rule of #115, which now also reads a grouped aggregate passed as a window's argument.

Where it makes no claim:

No GROUP BY: an empty table gives one row of NULLs.
WITH ROLLUP: left to the server's flags, as Postgres leaves a grouping set. Measured on 8.4, a ROLLUP over no rows returns no row, and its summary rows aggregate the rows themselves, so this could be extended later.
Only a whole select-list item: an expression over an aggregate (sum(x) + 1) keeps the server's flag.
Variance family and group_concat: they keep the server's flag; var_samp and stddev_samp are NULL over one row.
Set operations and wildcards: a select list with a wildcard, and a set operation, are not read.
New oracle case oracle/cases/mysql_grouped_aggregates (20 statements, blessed on MySQL 8.4, empty unchecked.txt), with users without posts, posts without a date or a note, and every edge value.
Other cases' expected.txt lines that lost a ?, each read:
- mysql_booleans: expressions_over_flags max(done) over a BOOLEAN NOT NULL.
- mysql_server_flags: aggregates_grouped sum(small), max(name), avg(small), and max(day) over a DATE NOT NULL that holds zero dates. min(maybe_n) and group_concat stay nullable, and its setup.sql comment names the rule.
- mysql_window_aggregates: grouped_sum.
Docs: the MySQL page's Nullability rules gain an Aggregates entry (captured my_null_aggregates.sql). It replaces the sentence that said only json_arrayagg is non-null under a group by.

Checks: cargo build --workspace --all-targets with no warning, cargo test of every crate, the whole oracle with --strict-coverage (145 cases, 0 failed), engine/build.sh (only the MySQL artifact changes), nub run typecheck, nub run test with both databases (258 passed, 0 skipped), nub run build, and the site's capture and build.

vercel · 2026-10-24T04:24:29Z

The latest updates on your projects. Learn more about Vercel for GitHub.

Project	Deployment	Actions	Updated
reylo	Ready	Preview	Oct 24, 2026 4:30am UTC

Copilot

Copilot was unable to review this pull request because the user who requested the review has insufficient remaining quota.

pullfrog

ℹ️ No engine issues found — one docs sentence overstates the rule.

Reviewed changes

I reviewed the whole PR: the new grouped-aggregate probe in the MySQL engine, its use from prove and window_flags, the new and re-blessed oracle cases, and the docs entry.

grouped_aggregate / grouped_probe — For a plain GROUP BY with no ROLLUP and no wildcard, these select each sum/avg/min/max argument from the same FROM and WHERE. The probe drops GROUP BY, HAVING, DISTINCT, ORDER BY, LIMIT, locks and named windows, and keeps the WITH. json_arrayagg/json_objectagg are marked never null.
grouped_flags — The probe is described against the reduced and materialized query, so a strict WHERE reads its outer joins as inner. Each probe column maps back to its original position, and the WHERE's tested proof counts too. window_flags reuses it, so sum(max(x)) over () follows x.
Derived tables and scalar subqueries — A grouped derived table's claim goes through through_derived's filler rewrite and a fresh describe, so an outer join over it keeps the server's nullable flag. Scalar subqueries stay nullable (scalar_subquery.sql).
Oracle — There is a new mysql_grouped_aggregates case. The ? was dropped in mysql_booleans, mysql_server_flags and mysql_window_aggregates; each dropped ? is a NOT NULL argument under a plain GROUP BY.
Docs — The MySQL page gains an Aggregates rule with a captured example, replacing the old sentence that covered only json_arrayagg.

^{｜ Fix all ➔ ｜ Fix 👍s ➔ ｜ View workflow run ｜ Using claude-opus-5-5 ｜ 𝕏}

pullfrog

✅ No new issues found. The docs sentence now matches the engine.

Reviewed changes

I reviewed the one commit since the last review, a docs fix for the earlier comment.

Named the bit functions in the Aggregates rule. site/content/docs/mysql.mdx now names bit_and, bit_or and bit_xor with count as the aggregates that are not NULL over no rows. This matches oracle/cases/mysql_server_flags/expected.txt, where aggregates_no_rows types d, e and f as non-null.

^{｜ View workflow run ｜ Using claude-opus-5-5 ｜ 𝕏}

colinhacks added 2 commits October 23, 2026 21:24

fix(mysql): type an aggregate under group by by its argument

c1f041b

docs(mysql): state when an aggregate under group by is non-null

72971df

Copilot AI balanced review requested due to automatic review settings October 24, 2026 04:24

Copilot AI reviewed Oct 24, 2026

pullfrog Bot reviewed Oct 24, 2026

View reviewed changes

Comment thread site/content/docs/mysql.mdx Resolved

docs(mysql): name the bit functions as non-null over no rows

c2c5391

pullfrog Bot reviewed Oct 24, 2026

View reviewed changes

colinhacks merged commit 8a272b0 into main Oct 24, 2026
9 checks passed

colinhacks deleted the mysql-grouped-agg branch October 24, 2026 04:33

pullfrog Bot mentioned this pull request Oct 25, 2026

feat(mysql): pin joins, derived tables, with queries and views by keys #108

Merged

Navigation Menu

Repository navigation

fix(mysql): type an aggregate under group by by its argument - #120

fix(mysql): type an aggregate under group by by its argument#120
colinhacks merged 3 commits into
mainfrom
mysql-grouped-agg

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Conversation

Uh oh!

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants