v0.4.1 - Database Error when using expect_no_rows

[oscarrobinson]

First off, thank you for making this library, it is absolutely brilliant!

I believe I've found a bug with the new expect_no_rows macro.

Whenever I try and use the expect_no_rows assertion I get a database error due to a syntax error in the generated SQL.

This is occuring running against BigQuery on the latest version of dbt-unit-testing (v0.4.1).

I can reproduce with a minimal example:

# models/staging/seed_demo_bugs.sql
{{
    config(materialized='table')
}}

SELECT
    1 AS bug_id
    , 'something' AS info

# models/staging/demo_bugs.sql
{{
    config(materialized='table')
}}
SELECT * FROM {{ dbt_unit_testing.ref('seed_demo_bugs') }} WHERE bug_id = 1

# tests/unittests/staging/test_demo_bugs.sql

{{ config(tags=['unit-test']) }}


{% call dbt_unit_testing.test('demo_bugs', 'should not include bugs with id other than 1') %}
  {% call dbt_unit_testing.mock_ref('seed_demo_bugs', options={"input_format": "csv"}) %}
    bug_id | info
         2 | 'some info'
  {% endcall %}

  {% call dbt_unit_testing.expect_no_rows() %}
  {% endcall %}
{% endcall %}

Running the following:

dbt test --select "tests/unittests/staging/test_demo_bugs.sql"

Gives the following output:

16:31:20  Running with dbt=1.7.4
16:31:20  Registered adapter: bigquery=1.7.2
16:31:20  Found 9 models, 68 tests, 1 operation, 2 sources, 0 exposures, 0 metrics, 642 macros, 0 groups, 0 semantic models
16:31:20  
16:31:22  Concurrency: 4 threads (target='test')
16:31:22  
16:31:22  1 of 1 START test test_demo_bugs ............................................... [RUN]
16:31:23  BigQuery adapter: https://console.cloud.google.com/<REDACTED>
16:31:24  1 of 1 ERROR test_demo_bugs .................................................... [ERROR in 1.25s]
16:31:24  
16:31:24  Finished running 1 test, 1 hook in 0 hours 0 minutes and 3.26 seconds (3.26s).
16:31:24  
16:31:24  Completed with 1 error and 0 warnings:
16:31:24  
16:31:24    Database Error in test test_demo_bugs (tests/unittests/staging/test_demo_bugs.sql)
  Syntax error: Unexpected end of statement at [1:163]
16:31:24  
16:31:24  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

After running build:

dbt build --target test --select "tests/unittests/staging/test_demo_bugs.sql"     

I can see the following output in the target directory:

# target/compiled/<project_name>/tests/unittests/staging/test_demo_bugs.sql




  
  


  
    
      select 1 as a from (select 1) as t where false
    
  

# target/run/<project_name>/tests/unittests/staging/test_demo_bugs.sql
select
      count(*) as failures,
      count(*) != 0 as should_warn,
      count(*) != 0 as should_error
    from (
      



  
  


  
    
      select 1 as a from (select 1) as t where false
    
  

      
    ) dbt_internal_test

Hopefully this is enough info for the bug to be fixed or for you to tell me what I'm doing wrong.

[psousa50]

Hi @oscarrobinson, please check v0.4.11 to see if it fixed your issue.

Thank you 👍

[oscarrobinson]

Thank you for your prompt reply @psousa50

I've retried with v0.4.11. Unfortunately I now have a new error. With the same setup as before running:

dbt test --select "tests/unittests/staging/test_demo_bugs.sql"

Now outputs:

12:18:22  Running with dbt=1.7.4
12:18:22  Registered adapter: bigquery=1.7.2
12:18:22  Unable to do partial parsing because config vars, config profile, or config target have changed
12:18:22  Unable to do partial parsing because env vars used in profiles.yml have changed
12:18:24  Found 5 models, 64 tests, 1 operation, 2 sources, 0 exposures, 0 metrics, 642 macros, 0 groups, 0 semantic models
12:18:25  Concurrency: 4 threads (target='dev')
12:18:25  
12:18:25  1 of 1 START test test_demo_bugs ............................................... [RUN]
12:18:25  1 of 1 ERROR test_demo_bugs .................................................... [ERROR in 0.08s]
12:18:25  
12:18:25  Finished running 1 test, 1 hook in 0 hours 0 minutes and 1.87 seconds (1.87s).
12:18:25  
12:18:25  Completed with 1 error and 0 warnings:
12:18:25  
12:18:25    Compilation Error in test test_demo_bugs (tests/unittests/staging/test_demo_bugs.sql)
  'dict object' has no attribute 'input_values'
  
  > in macro build_input_values_sql (macros/input_parsing.sql)
  > called by macro build_mocks_and_expectations (macros/tests.sql)
  > called by macro build_configuration_and_test_queries (macros/tests.sql)
  > called by macro test (macros/tests.sql)
  > called by test test_demo_bugs (tests/unittests/staging/test_demo_bugs.sql)
12:18:25  
12:18:25  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

[psousa50]

@oscarrobinson, please try this version and let me know if it worked:

  - git: "https://github.com/EqualExperts/dbt-unit-testing"
    revision: 49b53e9a1c299f5941d2c3242576857538cc363c

Just replace your v0.4.11 version with the one above and run dbt deps

Thanks

[oscarrobinson]

That works 😄 Thank you!

[psousa50]

Great! I'll create a release 0.4.12 with this.

Thank you again for your support!