Tests are failing with malformed SQL

[danakee]

When I try to run tests (even on a freshly created dbt project), they consistently fail. When I "dbt test --debug" I see malformed SQL. See generated code below:

  USE [SimulationsAnalytics];
  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'dbo')
  BEGIN
    EXEC('CREATE SCHEMA [dbo]')
  END

  EXEC('create view
    [dbo.testview_10535]
   as
-- Custom not_null test macro
WITH not_null AS (
    SELECT
        EDWCreatedDatetime
    FROM
        "SimulationsAnalytics"."dbo"."DimProject"
    WHERE
        EDWCreatedDatetime is null
)

SELECT COUNT(*) FROM not_null

;')
  select

    count(*) as failures,
    case when count(*) != 0
      then 'true' else 'false' end as should_warn,
    case when count(*) != 0
      then 'true' else 'false' end as should_error
  from (
    select * from
    [dbo.testview_10535]

  ) dbt_internal_test;

  EXEC('drop view
    [dbo.testview_10535]
  ;')

Note the sql object reference [dbo.testview_10535] should be [dbo].[testview_10535].

[cody-scott]

Can you share a sample of your model and any configs. I'm surprised to see it rendering with [ as it should default to " around the identifiers.

Can you also confirm your version is 1.8.4

[danakee]

@cody-scott
I found what I think is a bug in the tests.sql and unit_tests.sql files. I overwrote the macros in my project and it is working now.

Here is my updated code:

test.sql:

{% macro sqlserver__get_test_sql(main_sql, fail_calc, warn_if, error_if, limit) -%}

  -- Create target schema if it does not
  USE [{{ target.database }}];
  IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = '{{ target.schema }}')
  BEGIN
    EXEC('CREATE SCHEMA [{{ target.schema }}]')
  END

  {% set testview %}
    [{{ target.schema }}].[testview_{{ range(1300, 19000) | random }}]
  {% endset %}

  {% set sql = main_sql.replace("'", "''")%}
  EXEC('create view {{testview}} as {{ sql }};')
  select
    {{ "top (" ~ limit ~ ')' if limit != none }}
    {{ fail_calc }} as failures,
    case when {{ fail_calc }} {{ warn_if }}
      then 'true' else 'false' end as should_warn,
    case when {{ fail_calc }} {{ error_if }}
      then 'true' else 'false' end as should_error
  from (
    select * from {{testview}}
  ) dbt_internal_test;

  EXEC('drop view {{testview}};')

{%- endmacro %}
`

unit_tests.sql:
`
{% macro sqlserver__get_unit_test_sql(main_sql, expected_fixture_sql, expected_column_names) -%}

USE [{{ target.database }}];
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = '{{ target.schema }}')
BEGIN
EXEC('CREATE SCHEMA [{{ target.schema }}]')
END

{% set test_view %}
    [{{ target.schema }}].[testview_{{ range(1300, 19000) | random }}]
{% endset %}
{% set test_sql = main_sql.replace("'", "''")%}
EXEC('create view {{test_view}} as {{ test_sql }};')

{% set expected_view %}
    [{{ target.schema }}.expectedview_{{ range(1300, 19000) | random }}]
{% endset %}
{% set expected_sql = expected_fixture_sql.replace("'", "''")%}
EXEC('create view {{expected_view}} as {{ expected_sql }};')

-- Build actual result given inputs
{% set unittest_sql %}
with dbt_internal_unit_test_actual as (
  select
    {% for expected_column_name in expected_column_names %}{{expected_column_name}}{% if not loop.last -%},{% endif %}{%- endfor -%}, {{ dbt.string_literal("actual") }} as {{ adapter.quote("actual_or_expected") }}
  from
    {{ test_view }}
),
-- Build expected result
dbt_internal_unit_test_expected as (
  select
    {% for expected_column_name in expected_column_names %}{{expected_column_name}}{% if not loop.last -%}, {% endif %}{%- endfor -%}, {{ dbt.string_literal("expected") }} as {{ adapter.quote("actual_or_expected") }}
  from
    {{ expected_view }}
)
-- Union actual and expected results
select * from dbt_internal_unit_test_actual
union all
select * from dbt_internal_unit_test_expected
{% endset %}

EXEC('{{- escape_single_quotes(unittest_sql) -}}')

EXEC('drop view {{test_view}};')
EXEC('drop view {{expected_view}};')

{%- endmacro %}

[danakee]

@cody-scott - Yes, it is 1.8.4

[cody-scott]

ahh, i see it now. This line and this line

If you want to try this branch, let me know if that fixes it. https://github.com/dbt-msft/dbt-sqlserver/tree/566-tests-are-failing-with-malformed-sql

[danakee]

@cody-scott This is what I changed in both files:

in test.sql my proposed change is on line 11 [{{ target.schema }}.testview_{{ range(1300, 19000) | random }}] to [{{ target.schema }}].[testview_{{ range(1300, 19000) | random }}]

in unit_tests.sql my proposed change is on line 10 [{{ target.schema }}.testview_{{ range(1300, 19000) | random }}] to [{{ target.schema }}].[testview_{{ range(1300, 19000) | random }}]

in unit_tests.sql my proposed change is on line 16 [{{ target.schema }}.expectedview_{{ range(1300, 19000) | random }}] to [{{ target.schema }}].[expectedview_{{ range(1300, 19000) | random }}]