[Bug] Model reference and AS with empty run fails

[JBExcoffier]

Is this a new bug in dbt-core?

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

Current Behavior

I have a simple model that fails to create using the --empty flag in a run when there is a reference to another (simple) model and a AS statement.

Expected Behavior

Using the --empty flag in the run command should not have any impact in this case.

Steps To Reproduce

bug.sql file in models dir :

SELECT sometable.column FROM {{ref("somemodel")}} AS sometable

that depends on the somemodel.sql file in models dir :

SELECT column FROM table

dbt run --empty produces the following error :

I think that the problem comes from the auto generation of the underlying SQL request in the target/run directory :

  create view "db"."schema_dev"."bug__dbt_tmp"
    
    
  as (
    SELECT sometable.column FROM (select * from "db"."schema_dev"."somemodel" where false limit 0) _dbt_limit_subq_somemodel AS sometable
  );

Indeed an automatic table name is added (_dbt_limit_subq_somemodel). And as there is already a AS statement, it makes the whole request fails.

The bug is no more presen when a full run (i.e. without the --empty flag) is performed, as it creates a correct request as follow (target/run) :

  create view "db"."schema_dev"."bug__dbt_tmp"
    
    
  as (
    SELECT sometable.column FROM "db"."schema_dev"."somemodel" AS sometable
  );

Moreover, the problem is not present, with or without the --empty flag, when there is no model dependence. For example as with the following model named nobug.sql :

SELECT sometable.column FROM film AS sometable

It produces a correct request file in the target/run dir (same file generated with or without the --empty flag) :

  create view "db"."schema_dev"."nobug__dbt_tmp"
    
    
  as (
    SELECT sometable.column FROM table AS sometable
  );

Relevant log output

No response

Environment

- OS:Ubuntu 22.04
- Python:3.10.3
- dbt:1.8.5

Which database adapter are you using with dbt?

postgres

Additional Context

It seems to fail with either a ref or a source dependence.

[dbeatty10]

Thanks for opening this @JBExcoffier !

This appears related to dbt-labs/dbt-adapters#124 and dbt-labs/dbt-adapters#179.

Transferring to the dbt-postgres repo for further triage.

[canutog]

Hi, I opened an issue #184, that I thought it was related to the new microbatch release.

But it seems this was present in previous versions. I didn't find this issue before creating a new one, sorry about that.
So just FYI, issue #184 is related to this.