[Bug]: Function with multiline statement adds \n and fails

Terraform CLI Version

1.9.4

Terraform Provider Version

0.95.0

Terraform Configuration

resource "snowflake_function" "TEST_PARSER_VOLATILE_FUNC" {
  database = local.databases[var.DATABASE]
  schema = snowflake_schema.TEST_PARSER.name
  name = "VOLATILE_FUNC"

  arguments {
    name = "D"
    type = "DATE"
  }

  return_type = "TABLE (\"EVENT_DATE\" DATE, \"CITY\" VARCHAR(16777216), \"TEMPERATURE\" NUMBER(38,0))"
  language = "sql"

  statement = <<-EOT
AS '
SELECT d, ''New York'', 65.0
UNION ALL
SELECT d, ''Los Angeles'', 69.0
';
EOT
}

Object type(s)

resource:function

Expected Behavior


  # snowflake_function.TEST_PARSER_VOLATILE_FUNC will be created
  + resource "snowflake_function" "TEST_PARSER_VOLATILE_FUNC" {
      + comment              = "user-defined function"
      + database             = "DB_DPT_DEV_STG"
      + fully_qualified_name = (known after apply)
      + id                   = (known after apply)
      + is_secure            = false
      + language             = "sql"
      + name                 = "VOLATILE_FUNC"
      + null_input_behavior  = "CALLED ON NULL INPUT"

      + return_type          = "TABLE (\"EVENT_DATE\" DATE, \"CITY\" VARCHAR(16777216), \"TEMPERATURE\" NUMBER(38,0))"
      + schema               = "TEST_PARSER"
      + statement            = <<-EOT
            AS '
            SELECT d, ''New York'', 65.0
            UNION ALL
            SELECT d, ''Los Angeles'', 69.0
            ';
        EOT

      + arguments {
          + name = "D"
          + type = "DATE"
        }
    }

Actual Behavior

provider adds a return_behavior defaulted to VOLATILE.


  # snowflake_function.TEST_PARSER_VOLATILE_FUNC will be created
  + resource "snowflake_function" "TEST_PARSER_VOLATILE_FUNC" {
      + comment              = "user-defined function"
      + database             = "DB_DPT_DEV_STG"
      + fully_qualified_name = (known after apply)
      + id                   = (known after apply)
      + is_secure            = false
      + language             = "sql"
      + name                 = "VOLATILE_FUNC"
      + null_input_behavior  = "CALLED ON NULL INPUT"
      + return_behavior      = "VOLATILE"
      + return_type          = "TABLE (\"EVENT_DATE\" DATE, \"CITY\" VARCHAR(16777216), \"TEMPERATURE\" NUMBER(38,0))"
      + schema               = "TEST_PARSER"
      + statement            = <<-EOT
            AS '
            SELECT d, ''New York'', 65.0
            UNION ALL
            SELECT d, ''Los Angeles'', 69.0
            ';
        EOT

      + arguments {
          + name = "D"
          + type = "DATE"
        }
    }

Apply fails

│ Error: 001003 (42000): SQL compilation error:
│ syntax error line 1 at position 86 unexpected 'VOLATILE'.
│ 
│   with snowflake_function.TEST_PARSER_VOLATILE_FUNC,
│   on test_parser.functions.volatile_func.tf line 1, in resource "snowflake_function" "TEST_PARSER_VOLATILE_FUNC":
│    1: resource "snowflake_function" "TEST_PARSER_VOLATILE_FUNC" {
│ 
╵

Steps to Reproduce

Copy Configuration
update database and schema params
run apply

How much impact is this issue causing?

Medium

Logs

No response

Additional Information

No response

Would you like to implement a fix?

[ ] Yeah, I'll take it 😎

Hi @epmenard-ia 👋

You closed this issue. Is it still relevant? Functions are currently being reworked, so we'll take a look at this issue then.

Also, as @sfc-gh-jcieslak noticed, your configuration has null_input_behavior, which is not supported for SQL functions (docs).

cc @sfc-gh-asawicki

Hi, turns out extensive testing changed the source of the issue so I closed it. I’ll open a new one regarding the actual problem, thanks for the follow up!

From: Jakub Michalak @.> Sent: September 19, 2024 4:08 AM To: Snowflake-Labs/terraform-provider-snowflake @.> Cc: Ménard, Eric Pierre @.>; Mention @.> Subject: Re: [Snowflake-Labs/terraform-provider-snowflake] [Bug]: Function with multiline statement adds \n and fails (Issue #3089)

!!Attention COURRIEL EXTERNE/EXTERNAL EMAIL

Hi @epmenard-iahttps://urldefense.com/v3/__https:/github.com/epmenard-ia__;!!C_xIDw!swjlGWMRvb3pkC3UyOlB9ezus6Uejzdo2mJqBJ1oAdPMbRYURWU7xGQgTWS_6cTCnWcw5Y4EOuMG89S_yC5792CAMhzWAA$ 👋

You closed this issue. Is it still relevant? Functions are currently being reworked, so we'll take a look at this issue then.

cc @sfc-gh-asawickihttps://urldefense.com/v3/__https:/github.com/sfc-gh-asawicki__;!!C_xIDw!swjlGWMRvb3pkC3UyOlB9ezus6Uejzdo2mJqBJ1oAdPMbRYURWU7xGQgTWS_6cTCnWcw5Y4EOuMG89S_yC5792C7ewicsA$

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https:/github.com/Snowflake-Labs/terraform-provider-snowflake/issues/3089*issuecomment-2360280336__;Iw!!C_xIDw!swjlGWMRvb3pkC3UyOlB9ezus6Uejzdo2mJqBJ1oAdPMbRYURWU7xGQgTWS_6cTCnWcw5Y4EOuMG89S_yC5792AYPKnr7A$, or unsubscribehttps://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/BLMSRXXEXFQZCEZRC4RPEPDZXKA6PAVCNFSM6AAAAABOOG75TOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGNRQGI4DAMZTGY__;!!C_xIDw!swjlGWMRvb3pkC3UyOlB9ezus6Uejzdo2mJqBJ1oAdPMbRYURWU7xGQgTWS_6cTCnWcw5Y4EOuMG89S_yC5792AvCz7P1w$. You are receiving this because you were mentioned.Message ID: @.**@.>>

Snowflake-Labs / terraform-provider-snowflake