[Bug]: snowflake_procedure_sql cannot have table return_type with number

[deanmorin]

Terraform CLI Version

1.5.7

Terraform Provider Version

1.0.0

Company Name

No response

Terraform Configuration

resource "snowflake_procedure_sql" "test" {
  name        = "TEST"
  database    = "UTIL"
  schema      = "PUBLIC"
  execute_as  = "OWNER"
  return_type = "TABLE(NAME VARCHAR, VALUE NUMBER)"

  procedure_definition = <<EOT
DECLARE
  res RESULTSET DEFAULT (
    select 'a', 1
  );
BEGIN
  return table(res);
END
EOT
}

Category

category:resource

Object type(s)

resource:procedure

Expected Behavior

There should be a way to declare this return type that doesn't cause an error or show a diff each time.

Actual Behavior

Every time you apply it'll show a diff, and if you change the data types to try to avoid this you'll get an error.

Steps to Reproduce

On the first terraform apply the procedure will be created just fine.

If you run terraform apply again it'll want to destroy and recreate the resource:

~ return_type           = "TABLE(NAME VARCHAR(16777216), VALUE NUMBER(38, 0))" -> "TABLE (NAME VARCHAR, VALUE NUMBER)" # forces replacement

If you change the data type to try to avoid this you'll run into an error.

return_type = "TABLE(NAME VARCHAR(16777216), VALUE NUMBER(38, 0))"

Error: number NUMBER(38 could not be parsed, use "NUMBER(precision, scale)" format 
could not parse table column:  0), it should contain the following format `<arg_name> <arg_type>`; parser failure may be connected to the complex argument names

How much impact is this issue causing?

Low

Logs

No response

Additional Information

This wasn't an issue with snowflake_procedure in 0.99.0. That resource did not show a diff when return_type = "TABLE(NAME VARCHAR, VALUE NUMBER)" was used. I'm not sure if it would error with return_type = "TABLE(NAME VARCHAR(16777216), VALUE NUMBER(38, 0))" though.

I added this as a workaround:

  lifecycle {
    ignore_changes = [return_type]
  }

Would you like to implement a fix?

• Yeah, I'll take it 😎

[sfc-gh-jmichalak]

Hi @deanmorin 👋

Thanks for reporting this. We are aware of this issue. The limitations are pointed out in the documentation:

Use of return type TABLE is currently limited. It will be improved in the following versions of the provider which may still affect this resource.

Snowflake is not returning full data type information for arguments which may lead to unexpected plan outputs. Diff suppression for such cases will be improved.

Functions and procedures will be improved in future releases. @sfc-gh-asawicki will be back on Wednesday and will work on it.

[sfc-gh-asawicki]

Hey @deanmorin. As @sfc-gh-jmichalak wrote, I will continue functions and procedures rework, so I will verify it pretty soon.

[PedroMartinSteenstrup]

confirmed on my side too with functions

╷
│ Error: number NUMBER(38 could not be parsed, use "NUMBER(precision, scale)" format
│ could not parse table column:  0), it should contain the following format `<arg_name> <arg_type>`; parser failure may be connected to the complex argument names
│ 
│   with snowflake_function_python.this_python_function["STAGE|REPORTING_CUSTOMER_SUCCESS|PROPHET_CASE_FORECAST(VARCHAR, DATE, NUMBER)"],
│   on main.tf line 10, in resource "snowflake_function_python" "this_python_function":
│   10:   return_type     = each.value.return_type
│ 
╵

when passing

return_type: "TABLE(LANGUAGE VARCHAR(16777216), CREATED_DATE DATE, CASE_COUNT NUMBER(38, 0))"

if I don't put the precision on the number, i will get prompted at every plan

return_type = "TABLE(LANGUAGE VARCHAR(16777216), CREATED_DATE DATE, CASE_COUNT NUMBER(38, 0))" 
-> "TABLE (LANGUAGE VARCHAR(16777216), CREATED_DATE DATE, CASE_COUNT NUMBER)" # forces replacement