[Bug]: decimal precision for arguments not respected in functions

epmenard-ia commented 1 month ago

Terraform CLI Version

1.9.4

Terraform Provider Version

0.95.0

Terraform Configuration

resource "snowflake_function" "TEST_PARSER_SQL_DECIMAL_FUNC_N" {
  database = local.databases[var.DATABASE]
  schema = snowflake_schema.TEST_PARSER.name
  name = "SQL_DECIMAL_FUNC"
  arguments {
    name = "ZE_NUM"
    type = "NUMBER (10,2)"
  }

  comment = "Example of decimal input for SQL language"

  return_type = "NUMBER (10,2)"
  language = "sql"

  statement = "select ZE_NUM *2"
}

Object type(s)

No response

Expected Behavior

input argument as a number (10,2) as is presented in the plan:

  # snowflake_function.TEST_PARSER_SQL_DECIMAL_FUNC_N will be created
  + resource "snowflake_function" "TEST_PARSER_SQL_DECIMAL_FUNC_N" {
      + comment              = "Example of decimal input for SQL language"
      + database             = "DB_DPT_DEV_STG"
      + fully_qualified_name = (known after apply)
      + id                   = (known after apply)
      + is_secure            = false
      + language             = "sql"
      + name                 = "SQL_DECIMAL_FUNC"
      + null_input_behavior  = "CALLED ON NULL INPUT"
      + return_behavior      = "VOLATILE"
      + return_type          = "NUMBER (10,2)"
      + schema               = "TEST_PARSER"
      + statement            = "select ZE_NUM *2"

      + arguments {
          + name = "ZE_NUM"
          + type = "NUMBER (10,2)"
        }
    }

Actual Behavior

Table created with the input argument as a number with no precision:

CREATE OR REPLACE FUNCTION DB_DPT_DEV_STG.TEST_PARSER.SQL_DECIMAL_FUNC("ZE_NUM" NUMBER(38,0))
RETURNS NUMBER(38,0)
LANGUAGE SQL
COMMENT='Example of decimal input for SQL language'
AS 'select ZE_NUM *2';

Steps to Reproduce

copy configuration
update database and schema
run terraform apply

How much impact is this issue causing?

High

Logs

No response

Additional Information

No response

Would you like to implement a fix?

[ ] Yeah, I'll take it 😎

epmenard-ia commented 1 month ago

Here is a second slightly more complex example.

Proof function creation in Snowflake

CREATE OR REPLACE FUNCTION "DB_DPT_DEV_STG"."TEST_PARSER"."SQL_DECIMAL_FUNC" (ZE_NUM DECIMAL(5,3))
RETURNS TABLE (
    NUM_10_1 NUMBER(10,1),
    NUM_4_2 NUMBER(4,2),
    DEC_10_3 DECIMAL(10,3),
    DEC_3_2 DECIMAL(3,2),
    NUM_ERIC NUMBER,
    INT_ABBR INT,
    NUM_ERIC_14_2 NUMERIC(14,2),
    INT_EGER NUMBER
)
AS 'SELECT 
  ROUND(ZE_NUM,1) AS NUM_10_1,
  ROUND(ZE_NUM,2) AS NUM_4_2,
  ZE_NUM AS DEC_10_3,
  LEAST(9.99,ROUND(ZE_NUM,2)) AS DEC_3_2,
  ZE_NUM AS NUM_ERIC,
  ZE_NUM AS INT_ABBR,
  ROUND(ZE_NUM,2) AS NUM_ERIC_14_2,
  ZE_NUM AS INT_EGER
';

with successful test call

SELECT * FROM TABLE(SQL_DECIMAL_FUNC(14.856))

Scripted in terraform

resource "snowflake_function" "TEST_PARSER_SQL_DECIMAL_FUNC_N" {
  database = local.databases[var.DATABASE]
  schema = snowflake_schema.TEST_PARSER.name
  name = "SQL_DECIMAL_FUNC"
  arguments {
    name = "ZE_NUM"
    type = "DECIMAL(5,3)"
  }

  return_behavior = "IMMUTABLE"
  comment = "Example of decimal input for SQL language"

  return_type = "TABLE (NUM_10_1 NUMBER(10,1), NUM_4_2 NUMBER(4,2), DEC_10_3 DECIMAL(10,3), DEC_3_2 DECIMAL(3,2), NUM_ERIC NUMBER, INT_ABBR INT, NUM_ERIC_14_2 NUMERIC(14,2), INT_EGER NUMBER)"

  language = "sql"

  statement = <<-EOT
    SELECT 
        ROUND(ZE_NUM,1) AS NUM_10_1,
        ROUND(ZE_NUM,2) AS NUM_4_2,
        ZE_NUM AS DEC_10_3,
        LEAST(9.99,ROUND(ZE_NUM,2)) AS DEC_3_2,
        ZE_NUM AS NUM_ERIC,
        ZE_NUM AS INT_ABBR,
        ROUND(ZE_NUM,2) AS NUM_ERIC_14_2,
        ZE_NUM AS INT_EGER
EOT
}

Plan appears ok

  # snowflake_function.TEST_PARSER_SQL_DECIMAL_FUNC_N will be created
  + resource "snowflake_function" "TEST_PARSER_SQL_DECIMAL_FUNC_N" {
      + comment              = "Example of decimal input for SQL language"
      + database             = "DB_DPT_DEV_STG"
      + fully_qualified_name = (known after apply)
      + id                   = (known after apply)
      + is_secure            = false
      + language             = "sql"
      + name                 = "SQL_DECIMAL_FUNC"
      + null_input_behavior  = "CALLED ON NULL INPUT"
      + return_behavior      = "IMMUTABLE"
      + return_type          = "TABLE (NUM_10_1 NUMBER(10,1), NUM_4_2 NUMBER(4,2), DEC_10_3 DECIMAL(10,3), DEC_3_2 DECIMAL(3,2), NUM_ERIC NUMBER, INT_ABBR INT, NUM_ERIC_14_2 NUMERIC(14,2), INT_EGER NUMBER)"
      + schema               = "TEST_PARSER"
      + statement            = <<-EOT
            SELECT 
                ROUND(ZE_NUM,1) AS NUM_10_1,
                ROUND(ZE_NUM,2) AS NUM_4_2,
                ZE_NUM AS DEC_10_3,
                LEAST(9.99,ROUND(ZE_NUM,2)) AS DEC_3_2,
                ZE_NUM AS NUM_ERIC,
                ZE_NUM AS INT_ABBR,
                ROUND(ZE_NUM,2) AS NUM_ERIC_14_2,
                ZE_NUM AS INT_EGER
        EOT

      + arguments {
          + name = "ZE_NUM"
          + type = "DECIMAL(5,3)"
        }
    }

Following terraform apply, table creation shows invalid precisions: NUMBER(38,0)

CREATE OR REPLACE FUNCTION DB_DPT_DEV_STG.TEST_PARSER.SQL_DECIMAL_FUNC("ZE_NUM" NUMBER(38,0))
RETURNS TABLE ("NUM_10_1" NUMBER(38,0), "NUM_4_2" NUMBER(38,0), "DEC_10_3" NUMBER(38,0), "DEC_3_2" NUMBER(38,0), "NUM_ERIC" NUMBER(38,0), "INT_ABBR" NUMBER(38,0), "NUM_ERIC_14_2" NUMBER(38,0), "INT_EGER" NUMBER(38,0))
LANGUAGE SQL
IMMUTABLE
COMMENT='Example of decimal input for SQL language'
AS 'SELECT 
    ROUND(ZE_NUM,1) AS NUM_10_1,
    ROUND(ZE_NUM,2) AS NUM_4_2,
    ZE_NUM AS DEC_10_3,
    LEAST(9.99,ROUND(ZE_NUM,2)) AS DEC_3_2,
    ZE_NUM AS NUM_ERIC,
    ZE_NUM AS INT_ABBR,
    ROUND(ZE_NUM,2) AS NUM_ERIC_14_2,
    ZE_NUM AS INT_EGER
';

Resulting in a failed test

sfc-gh-jmichalak commented 1 month ago

Hi @epmenard-ia 👋

Functions are currently being reworked, we'll take a look at this issue. Note that decimal precision is only present in returns property in DESC FUNCTION, but signature returns arguments without precision.

cc @sfc-gh-asawicki

sfc-gh-asawicki commented 1 month ago

Hey @epmenard-ia. This looks like the same issue as described in https://github.com/Snowflake-Labs/terraform-provider-snowflake/issues/2735. It's a known problem and we will address it in the current rework as @sfc-gh-jmichalak has already written.

Snowflake-Labs / terraform-provider-snowflake