... for dialects with SqlConformance.isHavingAlias=false
Very, very similar to
Reproducing test case in RelToSqlConverter:
In that one, the pattern was Project/Filter/Aggregate, here it is Filter/Aggregate/Project. In 3593, the project created a new alias, which got added to the same SELECT clause and caused the ambiguity. Here, the aggregate creates an alias, but the filter will write a HAVING clause using the aliases from before the Aggregate, and that will cause the SQL engine to think that the filter is on the aggregate field, rather than on the underlying field.
Note that this is less an absurdly unlikely occurrence than it might seem because when Calcite's default aliasing kicks in and everything gets the name "$f6", "$f4", etc, so chances of a collision are higher if you have multiply nested selects with default aliases.
- force a subselect, as was done for 3593.
- Force the expression in the HAVING to be fully aliased by table (works at least in BigQuery, where I tested)
- Write the HAVING expression in terms of the aliases from the aggregate, rather than what's coming from the aggregate (also works on BigQuery)