Bug: rows: [] in unit test given block emits LIMIT 0

[Benjamin-Knight]

Adapter: dbt-sqlserver 1.10.0-rc1
dbt Core: 1.10.20
SQL Server: Microsoft ODBC Driver 18

Description

When a unit test given block specifies rows: [] for an input, dbt core generates an
empty-fixture CTE of the form:

__dbt__cte__some_ref as (
    -- Fixture for some_ref
    select try_cast(null as varchar(255)) as "col_a"
    limit 0
)

The query fails immediately with:

('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]
Incorrect syntax near 'limit'. (102) (SQLMoreResults)")

Steps to reproduce

1. Define a dbt unit test where at least one given input has rows: [].
2. Run dbt test --resource-type unit_test.

Minimal YAML excerpt:

unit_tests:
  - name: my_test
    model: my_model
    given:
      - input: ref('some_ref')
        rows: []          # <-- triggers the bug
      - input: ref('other_ref')
        rows:
          - col_a: 'foo'
    expect:
      rows:
        - col_a: 'foo'

Expected behaviour

An empty rows: [] block should produce a zero-row CTE using T-SQL-compatible
syntax

[Benjamin-Knight]

Looks like an issue with the expect block as well not accepting [] but for a different reason.

[Benjamin-Knight]

@axellpadilla I've added a pull request for this but I've had to shadow part of dbt-core to get it working as they do not dispatch the part that exports the limit. I do not really like shadowing a core part of dbt-core but I cannot see a way of fixing this otherwise, be good to have another pair of eyes on it.