Units Tests not working as expected

[cody-scott]

Hi,
first of all, thanks a lot for picking up on dbt-sqlserver @cody-scott and everyone else contributing!!

We tested our project with rc2 and everything works fine for now.

One thing I noticed now, when playing around with the unit test feature, which we have not used before, I get an error.

I oversimplify here, but the result is the same for more complex tests.

Model to be tested, called mawi_unit_test

select 
    ik
    ,standort_id
from
    {{ ref('mawi_psta_beschaffung') }}

Unit test file

unit_tests:
  - name: ut1 # this is the unique name of the test
    model: mawi_unit_test # name of the model I'm unit testing
    given: # the mock data for your inputs
      - input: ref('mawi_psta_beschaffung')
        rows:
          - {ik: 10}
    expect: # the expected output given the inputs above
      rows:
          - {ik: 10}

Result when running the test:

[0m16:50:59.525562 [debug] [Thread-1 (]: fabric adapter: Database error: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. (319); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near ')'. (102)")

SQL generated for the unit test that causes the error:

-- Build actual result given inputs
with dbt_internal_unit_test_actual as (
  select
    ik, 'actual' as "actual_or_expected"
  from (
    with __dbt__cte__mawi_psta_beschaffung as (
-- Fixture for mawi_psta_beschaffung
select 
    try_cast(10 as int)
 as ik, try_cast(null as int) as standort_id, try_cast(null as nvarchar) as fall, try_cast(null as int) as jahr, try_cast(null as int) as monat, try_cast(null as int) as jahr_monat, try_cast(null as uniqueidentifier) as project_id, try_cast(null as nvarchar) as kst_nummer, try_cast(null as nvarchar) as kst_bezeichnung, try_cast(null as nvarchar) as ka_nummer, try_cast(null as nvarchar) as ka_bezeichnung, try_cast(null as nvarchar) as artikel_nummer, try_cast(null as nvarchar) as artikel_bezeichnung, try_cast(null as nvarchar) as bezug_bezeichnung, try_cast(null as nvarchar) as mengeneinheit, try_cast(null as nvarchar) as warengruppe_bezeichnung, try_cast(null as nvarchar) as lieferantenartikel_nummer, try_cast(null as nvarchar) as lieferant_bezeichnung, try_cast(null as nvarchar) as eclasscodedname, try_cast(null as nvarchar) as pzn, try_cast(null as numeric(20,6)) as menge, try_cast(null as numeric(20,6)) as wert, try_cast(null as nvarchar) as waehrung, try_cast(null as int) as dq_qualifier, try_cast(null as uniqueidentifier) as row_id, try_cast(null as uniqueidentifier) as batch_id, try_cast(null as datetime2) as batch_created_on, try_cast(null as datetime2) as created_on
) select 
    ik
    ,standort_id
from
    __dbt__cte__mawi_psta_beschaffung
  ) _dbt_internal_unit_test_actual
),
-- Build expected result
dbt_internal_unit_test_expected as (
  select
    ik, 'expected' as "actual_or_expected"
  from (
    select 
    try_cast(10 as int)
 as ik
  ) _dbt_internal_unit_test_expected
)
-- Union actual and expected results
select * from dbt_internal_unit_test_actual
union all
select * from dbt_internal_unit_test_expected

Looking at the SQL, it is easy to fix, the internal WITH clause is not allowed, so one could rewrite it as a derived table for example like this:

-- Build actual result given inputs
with dbt_internal_unit_test_actual as (
  select
    ik, 'actual' as "actual_or_expected"
  from (
     select 
    ik
    ,standort_id
from
  (
-- Fixture for mawi_psta_beschaffung
select 
    try_cast(10 as int)
 as ik, try_cast(null as int) as standort_id, try_cast(null as nvarchar) as fall, try_cast(null as int) as jahr, try_cast(null as int) as monat, try_cast(null as int) as jahr_monat, try_cast(null as uniqueidentifier) as project_id, try_cast(null as nvarchar) as kst_nummer, try_cast(null as nvarchar) as kst_bezeichnung, try_cast(null as nvarchar) as ka_nummer, try_cast(null as nvarchar) as ka_bezeichnung, try_cast(null as nvarchar) as artikel_nummer, try_cast(null as nvarchar) as artikel_bezeichnung, try_cast(null as nvarchar) as bezug_bezeichnung, try_cast(null as nvarchar) as mengeneinheit, try_cast(null as nvarchar) as warengruppe_bezeichnung, try_cast(null as nvarchar) as lieferantenartikel_nummer, try_cast(null as nvarchar) as lieferant_bezeichnung, try_cast(null as nvarchar) as eclasscodedname, try_cast(null as nvarchar) as pzn, try_cast(null as numeric(20,6)) as menge, try_cast(null as numeric(20,6)) as wert, try_cast(null as nvarchar) as waehrung, try_cast(null as int) as dq_qualifier, try_cast(null as uniqueidentifier) as row_id, try_cast(null as uniqueidentifier) as batch_id, try_cast(null as datetime2) as batch_created_on, try_cast(null as datetime2) as created_on
)  __dbt__cte__mawi_psta_beschaffung
  ) _dbt_internal_unit_test_actual
),
-- Build expected result
dbt_internal_unit_test_expected as (
  select
    ik, 'expected' as "actual_or_expected"
  from (
    select 
    try_cast(10 as int)
 as ik
  ) _dbt_internal_unit_test_expected
)
-- Union actual and expected results
select * from dbt_internal_unit_test_actual
union all
select * from dbt_internal_unit_test_expected

Unfortunately, I don't know enough about the inner workings of dbt, so I can't help in changing the code for dbt-sqlserver.

Originally posted by @Mira-Roland in #516 (comment)

[cody-scott]

Problem seems to be the nested CTE (which is not supported in sql server). Potential fix is to proxy the query to a temporary table or view, then drop the artifact afterwards.