Provider evaluates _ and % as wildcards in table and pipe names

[DataJonni]

Provider Version

0.40.0

Terraform Version

1.2.9

Describe the bug

When there are two (or more) tables or pipes (maybe some other objects too?) which have a similar kind of name with underscores or % in their name, the initial creation of the tables works ok but on the subsequent run the plan wants to replace the second one of them with the details of the other one.

Example with tables:
/SCMTMS/D_SF_DOC
/SCMTMS/D_SFIDOC

When evaluating the plan against state after the initial run, Terraform queries the related object using a syntax like:

SHOW TABLES LIKE '/SCMTMS/D_SF_DOC' IN SCHEMA "DB"."STG". <-- WRONG, this will return both tables; we should only get /SCMTMS/D_SF_DOC

SHOW TABLES LIKE '/SCMTMS/D_SFIDOC' IN SCHEMA "DB"."STG" <-- correct for this table; if we would have a table called /SCMTMS/DASFIDOC , this would also fail.

Below is result from a query taken from query history when the plan is being evaluated against the state for table /SCMTMS/D_SF_DOC:

Below is the result from a query taken from query history when the plan is being evaluated against the state for table /SCMTMS/D_SFIDOC:

Because _ works as a wildcard for any character, the provider takes the first result when evaluating against the state and in this case it is wrong as it happens to be the first one in the list.

Expected behavior

When there are tables with underscores or other wildcard characters like % in their name, they should be treated accordingly and not use these characters in the table name as wildcards.

Code samples and commands

Example snippet, iterator loops through this with local.sf_object_name_evaluation.table_name evaluating to /SCMTMS/D_SF_DOC and /SCMTMS/D_SFIDOC (some info redacted or changed):

resource "snowflake_table" "target_table" {
  database             = var.database
  schema               = var.schema
  name                 = local.sf_object_name_evaluation.table_name
  comment              =  "The staging table for ${local.sf_object_name_evaluation.table_name}"
  column {
    name     = "BODY"
    type     = "VARIANT"
  }
  column {
    name     = "TABLE_NAME"
    type     = "VARCHAR(500)"
  }
}

Example snippet from terraform plan on the after the initial run (some info redacted or changed):

 -- # module.Staging.module.Data["/SCMTMS/D_SF_DOC"].snowflake_table.target_table will be updated in-place
  ~ resource "snowflake_table" "target_table" {
      ~ comment             = "The staging table for /SCMTMS/D_SFIDOC" -> "The staging table for /SCMTMS/D_SF_DOC"
        id                  = "DB|STG|/SCMTMS/D_SF_DOC"
      ~ name                = "/SCMTMS/D_SFIDOC" -> "/SCMTMS/D_SF_DOC"
        # (6 unchanged attributes hidden)

Additional context

Table name cannot be compromised in this case as we are trying to automate the creation hundreds of staging tables using TF.

One option which comes to mind is to change the provider to utilize INFORMATION_SCHEMA.TABLES|PIPES|etc to get the same info as with SHOW TABLES|PIPES|etc.

[mlorek]

you could use INFORMATION_SCHEMA.TABLES for most of the table parameters. however, that table does not contain information about change tracking which is a option you can enable in snowflake_table resource.

[mlorek]

same with INFORMATION_SCHEMA.PIPES, it does not include integration and error integration fields.

[danieldbr]

I have the same issue with the resource snowflake_database that has underscores on its names.

[ian-r-rose]

This same issue affects role grants (and, it seems, most objects):

terraform-provider-snowflake/pkg/resources/role_ownership_grant.go

Line 90 in f5c65ac

stmt := fmt.Sprintf("SHOW ROLES LIKE '%s'", onRoleName)

I suspect the appropriate fix would be to escape all wildcard characters every time this provider executes a command like SHOW <OBJECT> LIKE.

A minimal example which fails for me:

terraform {
  required_providers {
    snowflake = {
      source  = "Snowflake-Labs/snowflake"
      version = "~> 0.61"
      account = "<account-locator>"
    }
  }
  required_version = ">= 1.0"
}

# Snowflake provider for managing grants to roles.
provider "snowflake" {
  account = local.account
  role    = "SECURITYADMIN"
}

# These two roles look the same to the provider!
resource "snowflake_role" "tester" {
  name     = "TESTER"
}
resource "snowflake_role" "test_r" {
  name     = "TEST_R"
}

resource "snowflake_role_grants" "test_r_to_securityadmin" {
  role_name = snowflake_role.test_r.name
  roles     = ["SECURITYADMIN"]
}

resource "snowflake_role_grants" "tester_to_securityadmin" {
  role_name = snowflake_role.tester.name
  roles     = ["SECURITYADMIN"]
}

The above errors with:

snowflake_role.tester: Creating...
snowflake_role.test_r: Creating...
snowflake_role.tester: Creation complete after 1s [id=TESTER]
snowflake_role.test_r: Creation complete after 1s [id=TEST_R]
snowflake_role_grants.tester_to_securityadmin: Creating...
snowflake_role_grants.tester_to_securityadmin: Creation complete after 0s [id=TESTER|SECURITYADMIN|]
╷
│ Error: Provider produced inconsistent final plan
│
│ When expanding the plan for snowflake_role_grants.test_r_to_securityadmin to include new values learned so far during apply, provider "registry.terraform.io/snowflake-labs/snowflake" produced an invalid new
│ value for .role_name: was cty.StringVal("TEST_R"), but now cty.StringVal("TESTER").
│
│ This is a bug in the provider, which should be reported in the provider's own issue tracker.