[Bug]: Case Sensitive snowflake_procedure_sql variables

[jdoldis]

Terraform CLI Version

1.9.8

Terraform Provider Version

0.100.0

Company Name

No response

Terraform Configuration

resource "snowflake_procedure_sql" "proc_create_stream_data_table" {
  name                = "TEST_PROCEDURE"
  database            = "<database>"
  schema              = "<schema>"
  comment             = "Test procedure"
  return_type         = "VARCHAR"
  execute_as          = "CALLER"
  null_input_behavior = "RETURNS NULL ON NULL INPUT"
  arguments {
    arg_name      = "schema_name"
    arg_data_type = "varchar"
  }
  arguments {
    arg_name      = "table_name"
    arg_data_type = "varchar"
  }
  procedure_definition = <<EOT
  DECLARE
    source_table STRING;
    target_table STRING;
  BEGIN
    source_table := 'database.' || schema_name || '.' || table_name;
    RETURN 'Success';
  END;
  EOT
}

Category

category:resource

Object type(s)

No response

Expected Behavior

CALL <database>.<schema>.TEST_PROCEDURE('test', 'test'); returns Success.

Actual Behavior

CALL <database>.<schema>.TEST_PROCEDURE('test', 'test'); gives error Error: invalid identifier 'SCHEMA_NAME' (line 13).

Steps to Reproduce

Create the resource with the above config, and run CALL <database>.<schema>.TEST_PROCEDURE('test', 'test');.

How much impact is this issue causing?

Medium

Logs

No response

Additional Information

It seems the arguments in the procedure signature are defined with double quotes. The create statement from the above configuration -

CREATE OR REPLACE PROCEDURE <database>.<schema>.TEST_PROCEDURE("schema_name" VARCHAR(16777216), "table_name" VARCHAR(16777216))
RETURNS VARCHAR(16777216)
LANGUAGE SQL
STRICT
COMMENT='Test procedure'
EXECUTE AS CALLER
AS '  DECLARE
    source_table STRING;
    target_table STRING;
  BEGIN
    source_table := ''database.'' || schema_name || ''.'' || table_name;
    RETURN ''Success'';
  END;
';

I verified on the console that removing the double quotes from the procedure signature and then calling returns Success as expected.

In addition, it seems quoting schema_name and table_name in the procedure definition of the resource fixes the issue -

resource "snowflake_procedure_sql" "proc_create_stream_data_table" {
  name                = "TEST_PROCEDURE"
  database            = "<database>"
  schema              = "<schema>"
  comment             = "Test procedure"
  return_type         = "VARCHAR"
  execute_as          = "CALLER"
  null_input_behavior = "RETURNS NULL ON NULL INPUT"
  arguments {
    arg_name      = "schema_name"
    arg_data_type = "varchar"
  }
  arguments {
    arg_name      = "table_name"
    arg_data_type = "varchar"
  }
  procedure_definition = <<EOT
  DECLARE
    source_table STRING;
    target_table STRING;
  BEGIN
    source_table := 'database.' || "schema_name" || '.' || "table_name";
    RETURN 'Success';
  END;
  EOT

I don't think this was necessary in previous versions of the provider, as calling the procedure worked previously.

Would you like to implement a fix?

• Yeah, I'll take it 😎

[sfc-gh-jmichalak]

Hi @jdoldis 👋

You are right - this happens because arg_name is quoted (by the provider) and is actually lowercase in this example, but it is not quoted in the definition. The behavior is expected. Please either use uppercase arg_name or quote the arg name in procedure_definition.

I think this is related to all resources with fields like body, definition, etc. We will add a note to the documentation for the relevant resources.

[jdoldis]

Perfect, thanks @sfc-gh-jmichalak !

[sfc-gh-asawicki]

Hey @jdoldis. We have released v1.0.1 on Friday. We added clarifications in the migration guide and the registry docs. Please check if the docs are clearer now.

[jdoldis]

Looks great @sfc-gh-asawicki , will close this issue thanks 🙂