model-writtenrun 1642 - may be wrong, links go to the source messages. Commit-by-commit LLM analysis is in the Commits dialog.
- Kamil Monicz reported that upgrading to PostgreSQL 18 caused an unexpected outer-reference error in a query using CTEs, with NOT MATERIALIZED providing a workaround #1; after Tom Lane requested a self-contained example, Kamil supplied a PostGIS reproducer #2 #3.
- Tom reduced the reproducer to ordinary SQL and traced the failure to the earlier fix for bug #19055 assigning an aggregate to the wrong query level; he noted that a correction would miss that week's releases #4.
- Tom's first patch forced aggregates with nonlocal CTE references to remain at their syntactic query level, while acknowledging uncertainty about the general rule #5.
- Vik Fearing questioned whether SQL prohibited subqueries inside aggregate arguments, then accepted Tom's citation of the restriction, leaving the standard without guidance for this case #6 #7 #8.
- Tom explained how incorrect aggregate levels caused assertion failures, grouping errors, and planner failures, and rejected merely moving aggregates down one level because nested WITH clauses would still fail #9 #10.
- Richard Guo tested the first patch and showed that replacing a physical table or subquery with an equivalent CTE could change aggregate results; Tom explained the level-assignment issue and raised rejecting unsafe cases as an alternative #11 #12 #13.
- Tom replaced level adjustment with an explicit error for CTE references below an aggregate's normal semantic level, preserving the reported query's behavior and changing the bug #19055 test to expect rejection; the error points to the aggregate and names the CTE because CTE references lack source locations #14.
- After no further comments, Tom confirmed landing the second patch as b85397481c, with only base-dependent regression-output adaptations and backpatching through PostgreSQL 14 #15; broader support for rejected cases was deferred until a real-world example could clarify suitable semantics #14.