search

microsoft / dbt-synapse

dbt adapter for Azure Synapse Dedicated SQL Pools
https://dbt-msft.github.io/dbt-msft-docs/docs/dbt-synapse/overview
MIT License
69 stars 29 forks source link

`get_limit_subquery()` does not work with queries containing CTEs #228

Closed dataders closed 4 months ago

dataders commented 5 months ago

synapse__get_limit_subquery() wraps provided SQL string (sql) into a Subquery. If the subquery has a CTE, the SQL output by synapse__get_limit_subquery will throw the below error because CTEs are not supported within subqueries

('42000', "[42000] [Microsoft]
[ODBC Driver 18 for SQL Server][SQL Server]
Parse error at line: 9, column: 5:
Incorrect syntax near 'ID'. (103010) (SQLExecDirectW)")

This is especially a problem with the dbt Cloud IDE which will, by default, always try to only fetch the first 500 rows when previewing and compiling models.

I thought at first that fabric__get_limit_subquery() would work, but OFFSET is not supported in Synapse.

code in question

https://github.com/microsoft/dbt-synapse/blob/7d414cd122c91d3b45dd4e158afd82152c9695ff/dbt/include/synapse/macros/adapters/show.sql#L16-L22

dataders commented 4 months ago

resolved by: #230