Based on some testing it looks like you’re correct: sql server and postgres support the “group by 1” syntax but oracle doesn’t. For reference, I used these sites to test that: livesql.oracle.com, rextester.com/l/sql_server_online_compiler, sqlfiddle.com
However, it looks like all three support grouping by the base column references in the complex expression. That is, they’ll accept the following:
- select complex_expression( col1, col2, col3 ), count(*) from t group by col1, col2, col3
I think redshift and netezza are based on posgresql and should also work. I’m not sure about impala. Are there any other DBs that need to be supported? Is there some test environment where it’s easy to confirm that SQL statements work across all the DBs OHDSI uses?
To your other point, I’ve taken an approach of adding support for missing features in the starschema JDBC driver. The patched code is here: https://github.com/myl-google/starschema-bigquery-jdbc/tree/dml . Window functions and CTEs are already supported by the new standard sql dialect in BigQuery. I needed to add temp tables and DDL support but think I have all the required features covered now. Currently, I’m about halfway done working through the achilles script and adding rewrites to SqlRender. I’ve worked around the GROUP BY problem so far, but it would be really nice to find a simpler solution.
Matthew