Provider tries to set column collation when changing numeric types.

[MattMcFahn]

Terraform CLI and Provider Versions

Terraform v1.5.7
on darwin_arm64

Provider version: 0.87.0

Terraform Configuration

# variables.tf
variable "snowflake_account_id" {
  type = string
}
variable "snowflake_role" {
  type = string
}
variable "snowflake_user" {
  type = string
}
variable "password" {
  type = string
}

# terraform.tf
terraform {
  required_providers {
    snowflake = {
      source  = "Snowflake-Labs/snowflake"
      version = "0.87.0"
    }
  }
}

provider "snowflake" {
  account       = var.snowflake_account_id
  role          = var.snowflake_role
  user          = var.snowflake_user
  password      = var.password
}

# database.tf
resource "snowflake_database" "db" {
  name = "SOME_DB"
}

resource "snowflake_schema" "schema" {
  name = "SOME_SCHEMA"
  database = snowflake_database.db.name
}

resource "snowflake_table" "table" {
  name = "SOME_TABLE"
  schema = snowflake_schema.schema.name
  database = snowflake_database.db.name

  column {
    name    = "SOME_COLUMN"
    type    = "NUMBER(38,0)"
  }
}

Expected Behavior

Apply the configuration as-is to create a new database, schema and table are created in the relevant snowflake account.

The table has one column, SOME_COLUMN, which has type NUMBER(38,0).

Then change the column type to e.g. NUMBER(36,0), and re-apply.

Expectation:

• The column's data type is modified appropriately.

Actual Behavior

The column fails to modify with the following message:

Steps to Reproduce

1. terraform apply
2. Change NUMBER(38,0) to NUMBER(36,0)
3. Re run terraform apply

How much impact is this issue causing?

Medium

Logs

No response

Additional Information

Introduced in the upgrade from 0.86.0 to 0.87.0, by #2496. We also can't use 0.86.0 due to this issue: #2535

[sfc-gh-jcieslak]

Hey, @MattMcFahn 👋 Thanks for reporting the issue.
I'll take a look at it this week.

[MattMcFahn]

Thanks @sfc-gh-jcieslak ! I'll try and find some time to look myself also.

I think it might also be related to where analogous types have been converted internally. E.g. when we're specifying type as NUMBER, for example, this gets converted to NUMBER(38,0), and we then get the collation issue.

We've been able to work around this by identifying what the conversion will be and specifying that in the first instance - e.g. using NUMBER(38,0) rather than NUMBER, FLOAT rather than REAL. Given we've got the workaround I've moved the impact down.

[sfc-gh-jcieslak]

Number != Number(38,0) issue comes from the fact that Snowflake doesn't return the same value we're specifying. E.g. when you create a column with the type TEXT Snowflake in the SHOW command will return some kind of VARCHAR(...) (same for Number turning into Number(38,0)). Collation issue may come from this pr (#2496) where we added collation, specifically, you can look into the UpdateTable function which handles state Updates (e.g. NUMBER(38,0) to NUMBER(36,0) type change).

[sfc-gh-jcieslak]

Hey, the fix was recently released. Please see if the issue has been resolved in the latest version of the provider. Thanks :)