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

[epmenard-ia]

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"
}

Category

category:resource

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

1. copy configuration
2. update database and schema
3. 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]

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]

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]

Hey @epmenard-ia. This looks like the same issue as described in #2735. It's a known problem and we will address it in the current rework as @sfc-gh-jmichalak has already written.

[sfc-gh-asawicki]

Hey @epmenard-ia.

In v0.100.0, we have introduced new function resources. The handling of data types was greatly improved. PLease check it out and let us know if this solves your issue.

[sfc-gh-asawicki]

Closing due to inactivity. Please create a new issue if the problem persists in the newest provider version.