Best shown with the shell of a test in RelToSqlConverter test.
The following SQL on BigQuery
produces one result, because the having filter applies to the product id before subtraction, of course.
Running it through the machinery in that test (`sql(query).withBigQuery().ok(expected)`) translates it to:
This changes the meaning of the query: now the HAVING is on the after-subtraction product_id and you get no results, rather than the one result.
Note that this is not true in HyperSQL, as it has different semantics around the HAVING namespace.
But the meaning is preserved: both return a row.
I'm not enough of a SQL standards expert to know which one is being more compliant, but presumably both would have to be supported via some sort of flag?
I think the fix would be to force the subselect on dialects such as BigQuery that have this behavior. Probably something that looks a lot like https://github.com/apache/calcite/blob/3530daaa8cad43aad6845b6c79e4bc1ca0e72f5f/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java#L1043-L1047
The test, of course, looks like pretty silly SQL no one would ever write, but the point is this is what's generated when you have