Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[Regression] Materialized view with multiple indexes errors #85

Closed
2 tasks done
bdewilde opened this issue May 3, 2024 · 3 comments · Fixed by #97, #104, dbt-labs/dbt-core#10194 or #105
Closed
2 tasks done

[Regression] Materialized view with multiple indexes errors #85

bdewilde opened this issue May 3, 2024 · 3 comments · Fixed by #97, #104, dbt-labs/dbt-core#10194 or #105
Assignees
Labels
backport 1.7.latest backport 1.8.latest bug Something isn't working High Severity bug with significant impact that should be resolved in a reasonable timeframe materialized_views regression

Comments

@bdewilde
Copy link

bdewilde commented May 3, 2024

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

Hi! Upon updating to dbt v1.7.14, all models materialized as "materialized_view" and including more than one index in its config fail with a syntax error. This did not happen in v1.7.13. The error looks like this:

15:36:06    Database Error in model int_test (models/intermediate/int_test.sql)
  syntax error at or near "create"
  LINE 13:   (foo)create  index if not exists

It looks like the multiple "create index" statements are being concatenated together incorrectly. I'm wondering if this is related.

Expected Behavior

I would expect materialized views with multiple configured indexes to build and run without error.

Steps To Reproduce

Model (int_test.sql):

{{
    config(
        materialized="materialized_view",
        indexes=[
            {"columns": ["foo"], "type": "btree"},
            {"columns": ["bar"], "type": "btree"},
        ],
    )
}}

SELECT 1 AS foo, 2 AS bar

Command: dbt run --select int_test
Output:

15:41:31  1 of 1 START sql materialized_view model intermediate.int_test ............. [RUN]
15:41:31  1 of 1 ERROR creating sql materialized_view model intermediate.int_test .... [ERROR in 0.06s]
15:41:31
15:41:31  Finished running 1 materialized_view model in 0 hours 0 minutes and 0.37 seconds (0.37s).
15:41:31
15:41:31  Completed with 1 error and 0 warnings:
15:41:31
15:41:31    Database Error in model int_test (models/intermediate/int_test.sql)
  syntax error at or near "create"
  LINE 13:   (foo)create  index if not exists
                  ^
  compiled Code at /opt/wg/.meltano/transformers/dbt/target/run/db/models/intermediate/int_test.sql
15:41:31
15:41:31  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Removing one or both of those indexes works just fine:

{{ config(materialized="materialized_view") }}

SELECT 1 AS foo, 2 AS bar

=>

15:43:57  1 of 1 START sql materialized_view model intermediate.int_test ............. [RUN]
15:43:57  1 of 1 OK created sql materialized_view model intermediate.int_test ........ [SELECT 1 in 0.17s]
15:43:57
15:43:57  Finished running 1 materialized_view model in 0 hours 0 minutes and 0.39 seconds (0.39s).
15:43:57
15:43:57  Completed successfully
15:43:57
15:43:57  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

Relevant log output

No response

Environment

- OS: Ubuntu 20.04
- Python: 3.9.19
- dbt: 1.7.14

Which database adapter are you using with dbt?

postgres

Additional Context

No response

@bdewilde bdewilde added bug Something isn't working triage labels May 3, 2024
@dbeatty10 dbeatty10 transferred this issue from dbt-labs/dbt-core May 3, 2024
@dbeatty10 dbeatty10 changed the title [Bug] Materialized view with multiple indexes errors [Regression] Materialized view with multiple indexes errors May 3, 2024
@jtcohen6
Copy link
Contributor

jtcohen6 commented May 7, 2024

@bdewilde Thanks for the report!

I can confirm that this change likely caused the regression, because adding that ; back does seem to restore the previous behavior.

Following the reproduction case:

    create materialized view if not exists "jerco"."dbt_jcohen"."int_test" as 

SELECT 1 AS foo, 2 AS bar;

    create  index if not exists
  "13008d29068d3f93577844cc147dd809"
  on "jerco"."dbt_jcohen"."int_test" using btree
  (foo);create  index if not exists
  "d9594d6bdc8ad05fa90f86119dd485f3"
  on "jerco"."dbt_jcohen"."int_test" using btree
  (bar);

I don't know if adding it back in exactly the same spot is right way to resolve this, versus somewhere else in the MV creation/update flow. Either way, we should:

  1. Restore support for MVs with multiple indexes
  2. Add a test to ensure it doesn't regress again

@jtcohen6 jtcohen6 added High Severity bug with significant impact that should be resolved in a reasonable timeframe and removed triage labels May 7, 2024
@VersusFacit
Copy link
Contributor

Note we have no tests for verifying this behavior one way or another in existence currently. The multiple index thing should cover this ; case.

@mikealfare
Copy link
Contributor

Re-opening for backports

@mikealfare mikealfare reopened this May 21, 2024
@mikealfare mikealfare self-assigned this May 21, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment