Linting `union_relation` with SQLFluff gives an empty select

[martinshjung]

Describe the bug

I think this is similar to the resolved issue with dbt_utils.star() here: https://github.com/dbt-labs/dbt-utils/issues/605. I have a model using union_relations, that references tables that haven't been built yet. I want to be able to run SQLFluff and lint my model, but I get Found unparsable section: 'select'.

Steps to reproduce

1. Create two upstream models:

   # model1.sql
   SELECT 1 AS model1_column

# model2.sql
SELECT 1 AS model2_column

2. Create the model that uses union_relation. Important you pass source_column_name=None, to trigger the parse error with SQLFluff:

   model3.sql
   {{
   dbt_utils.union_relations(
       relations=[
           ref('model1'),
           ref('model2'),
       ],
       source_column_name=None
   )
   }}

3. Without running any of these dbt models, run sqlfluff lint against model3.sql.

Expected results

I'd expect the compiled SQL to have * in the SELECT statement, similar to how it's handled with the dbt_utils.star macro.

Actual results

I don't get any text after the SELECT keyword, resulting in a parse error with SQLFluff.

Screenshots and log output

I get:

L:   1 | P:   1 |  PRS | Line 4, Position 13: Found unparsable section: 'select'
L:   1 | P:   1 |  PRS | Line 17, Position 13: Found unparsable section: 'select'

with the compiled SQL looking like this:

        (
            select

            from `test-warehouse`.`test`.`model1`

        )

        union all

        (
            select

            from `test-warehouse`.`test`.`model2`

        )

System information

The contents of your packages.yml file:

packages:
  - git: https://github.com/dbt-labs/dbt-codegen.git
    revision: e24e5dc5daca47ba350415a59790d6b741be246b
  - package: calogica/dbt_expectations
    version: 0.8.5
  - package: dbt-labs/dbt_project_evaluator
    version: 0.6.2
  - package: dbt-labs/dbt_utils
    version: 1.1.1

Which database are you using dbt with?

• [ ] postgres
• [ ] redshift
• [x] bigquery
• [ ] snowflake
• [ ] other (specify: ____)

The output of dbt --version:

Core:
  - installed: 1.5.1
  - latest:    1.6.1 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - bigquery: 1.5.1 - Update available!

  At least one plugin is out of date or incompatible with dbt-core.
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Additional context

Using sqlfluff version 2.3.1, with the same version sqlfluff-templater-dbt.

Are you interested in contributing the fix?

A snippet like this in the default macro, maybe?

/* No columns from any of the relations.
   This star is only output during dbt compile, and exists to keep SQLFluff happy. */
{% if dbt_command == 'compile' and ordered_column_names|length == 0 %}
    *
{% endif %}

Not sure what integration tests would be appropriate, since it's for flags.WHICH == compile.

[github-actions[bot]]

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]]

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.