-
Notifications
You must be signed in to change notification settings - Fork 1.6k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Can't use CTEs in data test with dbt-sqlserver - Database Error #2609
Comments
Thanks for raising this issue here, @camfrout! From some brief searching, it appears that SQLServer doesn't support nested In any case, it sounds like we need to enable different implementations on different databases. We should move this SQL, currently defined as a python string (!), into a Jinja adapter macro that can be implemented differently on different plugins. We should do the same with built-in schema tests (see #2415). In dbt: {% macro get_count_from_test(test) %}
{{ adapter_macro('get_count_from_test', test) }}
{% endmacro %}
{% macro default__get_count_from_test(test) %}
select count(*) from ({{ test.injected_sql }}) sbq
{% endmacro %} In dbt-sqlserver, which doesn't support nested {% macro sqlserver__get_count_from_test(test) %}
create temp table {{ test.name }}__dbt_test_tmp as (
{{ test.injected_sql }}
);
select count(*) from {{ test.name }}__dbt_test_tmp;
{% endmacro %} AddendaRationalizing and reconciling schema + data tests is on the 1.0.0 roadmap. Here's the trade-off we're facing:
I'm leaning toward the latter. The adapter macro above would be a prerequisite for that change as well. Also, we might take this opportunity to change the name of |
Describe the bug
Using CTEs in data tests while using the dbt-sqlserver adapter raises SQL errors. This is due to the fact that data tests are wrapped around a
SELECT COUNT(*)
https://github.com/fishtown-analytics/dbt/blob/3af8a22d1319431115e7394f1235294613d36b35/core/dbt/task/test.py#L35 and SQL Server doesn't allow the CTEs inside other requests (it needs to start with WITH).I has also been reported in the dbt-sqlserver repo here but this seems to be actually due to the different behaviour of data tests compared to schema tests in dbt.
Steps To Reproduce
WITH src AS (SELECT COUNT(*) AS cnt FROM {{ ref('my table') }}) ) SELECT * FROM src
)Expected behavior
The test should run without any SQL error
Screenshots and log output
From dbt.log
System information
Which database are you using dbt with?
The output of
dbt --version
:The operating system you're using:
Mac OS
The output of
python --version
:Python 3.8.3
Additional context
Is there any reason why the data tests are wrapped around a
SELECT COUNT(*)
and could the behavior be changed to the same as schema tests?The text was updated successfully, but these errors were encountered: