[Bug] --empty flag is not working with nested columns in spectrum

[gekas93]

Is this a new bug?

• I believe this is a new bug
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

Running dbt run --empty , we get a spectrum nested query error.

Database Error in model base_***** (models/base/*****/base_*****.sql)
  Spectrum nested query error

The compiled code is the following:

create  table
    "DATABASE"."SCHEMA"."base_*****__dbt_tmp"
  as (
    select *
from
    (select * from "DATABASE"."SCHEMA"."TABLE" where false limit 0)
  );

If we remove the limit 0 or we remove the subquery having something like this:

  create  table
    "DATABASE"."SCHEMA"."base_*****__dbt_tmp"
  as (
    select * from "DATABASE"."SCHEMA"."TABLE" where false limit 0
  );

The query works on redshift.

Expected Behavior

dbt run --empty works on redshift spectrum.

Steps To Reproduce

1. To have some spectrum source with array column.
2. Create some model with select * to that source.
3. Run dbt run --empty

Relevant log output

No response

Environment

- OS: Mac
- Python:3.9.18
- dbt-core:1.8.1
- dbt-redshift:1.8.1

Additional Context

We figured out that the code that generates this line of code is in relation.py file.

 def render_limited(self) -> str:
        rendered = self.render()
        if self.limit is None:
            return rendered
        elif self.limit == 0:
            return f"(select * from {rendered} where false limit 0){self._render_limited_alias()}"
        else:
            return f"(select * from {rendered} limit {self.limit}){self._render_limited_alias()}"

If we remove limit 0, it works.

[colin-rogers-dbt]

This is going to require a dbt-redshift specific solution as currently base adapter (nor redshift adapter) are aware of whether a source is a spectrum table

[colin-rogers-dbt]

Likely addressing this will require users to configure their spectrum table references as sources and for dbt-redshift to check if a source is a spectrum table before rendering. We will spike out evaluating if/how we can support this