[CT-1024] [Bug] Nonexistent column in yml file causes "can't execute an empty query"

ddresslerlegalplans commented 2 years ago

Is this a new bug in dbt-core?

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

Current Behavior

When I run dbt run on table.sql and table.yml has a column and a test defined on a nonexistent column I get this error:

"can't execute an empty query"

Expected Behavior

An error message stating "Non-existent column in table.yml. Please remove the column "column1" from the yml file. "

Steps To Reproduce

with a table1.sql file with a simple select and a few ctes and with a table1.yml file with a non-existent column in table1.sql final select

run dbt run --select table1.sql

Relevant log output

22:46:04  Database Error in model table1 (models/mart/tbl/table1.sql)
22:46:04    can't execute an empty query
22:46:04    compiled SQL at target/run/mlp_dbt/models/tbl/fct/table1.sql

Environment

- OS: docker python:3.9-slim
- Python: python:3.9-slim
- dbt:1.2

Which database adapter are you using with dbt?

redshift

Additional Context

No response

jtcohen6 commented 2 years ago

Thanks for opening @ddresslerlegalplans! I'm pretty sure this bug is related to persist_docs. Could you confirm by checking to see if the persist_docs config is enabled someone for your model / your project?

Reproduction case

Set up a model as described:

-- models/table1.sql
select 1 as id

version: 2

models:
  - name: table1
    columns:
      - name: does_not_exist

dbt run succeeds just fine.

Now update the yaml config to switch on column-level persist_docs:

version: 2

models:
  - name: table1
    config:
      persist_docs:
        columns: true
    columns:
      - name: does_not_exist
        description: "This column does not exist"

$ dbt run
...
14:10:18  Completed with 1 error and 0 warnings:
14:10:18
14:10:18  Database Error in model table1 (models/table1.sql)
14:10:18    can't execute an empty query
14:10:18    compiled Code at target/run/my_gcpython_project/models/table1.sql

Explanation

The bug (not new in v1.2): https://github.com/dbt-labs/dbt-core/blob/4d6208be6464a48a078dd8000ae6c29d8d7a42ce/core/dbt/include/global_project/macros/adapters/persist_docs.sql#L30-L32

https://github.com/dbt-labs/dbt-core/blob/4d6208be6464a48a078dd8000ae6c29d8d7a42ce/plugins/postgres/dbt/include/postgres/macros/adapters.sql#L197-L204

If there are no legitimate columns to be commenting on, alter_column_comment returns an empty query.

(Redshift has its own slight modifications, but they're functionally identical to the ones in dbt-core + dbt-postgres.)

Resolution

I think the fix here might look like:

Explicit returning from alter_column_comment — either a list of statements, or None if there are no statements being templated
Inside persist_docs macro, check to see if return value from alter_column_comment is not None / not empty, before passing it into run_query

That shouldn't be too too tricky. It should also have a test case. (Unfortunately, our tests for persist_docs haven't yet been converted to use the new testing framework.) Labelling this one help_wanted accordingly.

ddresslerlegalplans commented 2 years ago

@jtcohen6 thanks for the quick reply and digging into this. I can confirm we are using persist_doc:

models:
  my_project:
    +persist_docs:
      relation: true
      columns: true

ddresslerlegalplans commented 2 years ago

Is it just a matter of doing this?:

somewhat new to jinja

github-actions[bot] commented 1 year ago

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

github-actions[bot] commented 1 year ago

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

jeremyyeo commented 10 months ago

Reopening on behalf of customer :)

On Redshift - the error message is:

23:13:43    Runtime Error in model foo (models/foo.sql)
  Tried to run invalid SQL: /* {"app": "dbt", "dbt_version": "1.6.9", "profile_name": "all", "target_name": "rs", "node_id": "model.my_dbt_project.foo"} */

     on model.my_dbt_project.foo
23:13:43

dbt-labs / dbt-core