nosterlu commented 1 month ago

Terraform CLI Version

1.8.5

Terraform Provider Version

0.96.0

Terraform Configuration

# First time below was updated fine without the DC column, with DC column added we get an error.

resource snowflake_view view_test {
  database = "MY_DB"
  schema   = "MY_SCHEMA"
  name     = "MY_VIEW"

  comment = ""

  statement  = <<-SQL
    SELECT
      PART
      ,DC
      ,FORECAST_CUSTOMER
      ,STOCK_BALANCE
      ,ORDER_QUE_BALANCE_CLASS_1
      ,ORDER_QUE_BALANCE_CLASS_2_TO_4
      ,STOCK_TAKING_BALANCE
      ,QUANTITY_ORDERED_NOT_INVOICED
      ,QUANTITY_BACK_ORDER_CL_2_TO_4
      ,QUANTITY_BACK_ORDER_CL_1
      ,SUPPLIER_NUMBER
    FROM
      PROD_PSL_DB.PULS.LDC
SQL
  is_secure  = true
}

Object type(s)

resource:view

Expected Behavior

The view to be re-created with DC column added

Actual Behavior

terraform ran below SQL, but missed adding the DC column in the first part...

CREATE OR REPLACE SECURE VIEW "MY_DB"."MY_SCHEMA"."MY_VIEW ("PART", "FORECAST_CUSTOMER", "STOCK_BALANCE", "ORDER_QUE_BALANCE_CLASS_1", "ORDER_QUE_BALANCE_CLASS_2_TO_4", "STOCK_TAKING_BALANCE", "QUANTITY_ORDERED_NOT_INVOICED", "QUANTITY_BACK_ORDER_CL_2_TO_4", "QUANTITY_BACK_ORDER_CL_1", "SUPPLIER_NUMBER", "YEAR", "MONTH", "DAY") AS SELECT
  PART
  ,DC
  ,FORECAST_CUSTOMER
  ,STOCK_BALANCE
  ,ORDER_QUE_BALANCE_CLASS_1
  ,ORDER_QUE_BALANCE_CLASS_2_TO_4
  ,STOCK_TAKING_BALANCE
  ,QUANTITY_ORDERED_NOT_INVOICED
  ,QUANTITY_BACK_ORDER_CL_2_TO_4
  ,QUANTITY_BACK_ORDER_CL_1
  ,SUPPLIER_NUMBER
  ,YEAR
  ,MONTH
  ,DAY
FROM
  PROD_PSL_DB.PULS.LDC_HISTORY

SQL compilation error: Invalid column definition list

Steps to Reproduce

Update with this

resource snowflake_view view_test {
  database = "MY_DB"
  schema   = "MY_SCHEMA"
  name     = "MY_VIEW"

  comment = ""

  statement  = <<-SQL
    SELECT
      PART
      ,FORECAST_CUSTOMER
      ,STOCK_BALANCE
      ,ORDER_QUE_BALANCE_CLASS_1
      ,ORDER_QUE_BALANCE_CLASS_2_TO_4
      ,STOCK_TAKING_BALANCE
      ,QUANTITY_ORDERED_NOT_INVOICED
      ,QUANTITY_BACK_ORDER_CL_2_TO_4
      ,QUANTITY_BACK_ORDER_CL_1
      ,SUPPLIER_NUMBER
    FROM
      PROD_PSL_DB.PULS.LDC
SQL
  is_secure  = true
}

then with this

resource snowflake_view view_test {
  database = "MY_DB"
  schema   = "MY_SCHEMA"
  name     = "MY_VIEW"

  comment = ""

  statement  = <<-SQL
    SELECT
      PART
      ,DC
      ,FORECAST_CUSTOMER
      ,STOCK_BALANCE
      ,ORDER_QUE_BALANCE_CLASS_1
      ,ORDER_QUE_BALANCE_CLASS_2_TO_4
      ,STOCK_TAKING_BALANCE
      ,QUANTITY_ORDERED_NOT_INVOICED
      ,QUANTITY_BACK_ORDER_CL_2_TO_4
      ,QUANTITY_BACK_ORDER_CL_1
      ,SUPPLIER_NUMBER
    FROM
      PROD_PSL_DB.PULS.LDC
SQL
  is_secure  = true
}

How much impact is this issue causing?

Medium

Logs

No response

Additional Information

Only way to fix is to first comment out the tables and run terraform to destroy, then to un-comment and run terraform to re-create.

Would you like to implement a fix?

[ ] Yeah, I'll take it 😎

sfc-gh-jmichalak commented 1 month ago

Hi @nosterlu 👋

I believe this is the same problem, as in https://github.com/Snowflake-Labs/terraform-provider-snowflake/issues/3073#issuecomment-2392250469. We've released a fix in v0.97 (release, migration guide). Please upgrade to this version.