get_limit_subquery() does not work with queries containing CTEs

[dataders]

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

dbt-synapse/dbt/include/synapse/macros/adapters/show.sql

Lines 16 to 22 in 7d414cd

	{# Synapse doesnt support ANSI LIMIT clause #}
	{% macro synapse__get_limit_subquery_sql(sql, limit) %}
	select top {{ limit }} *
	from (
	{{ sql }}
	) as model_limit_subq
	{% endmacro %}

[dataders]

resolved by: #230