UDF Retrun datatype is not being applied correctly for the NUMBER with scale

[syed3477]

Terraform CLI and Provider Versions

Terraform v1.7.5

• provider registry.terraform.io/scalr/scalr v1.6.0
• provider registry.terraform.io/snowflake-labs/snowflake v0.88.0

Terraform Configuration

resource "snowflake_function" "test_function_number" {
  provider = snowflake.sysadmin
  database = var.database_name
  schema   = var.schema_name
  name     = "TEST_FUNCTION_NUMBER"
  comment             = "Test Table function"
  return_type         = "TABLE (NUM1 NUMBER, NUM2 NUMBER(10,2))"
  
 statement           = <<EOT
    SELECT 12,13.4
  EOT
}

Expected Behavior

When running this function, it should give

Actual Behavior

Steps to Reproduce

resource "snowflake_function" "test_function_number" {
provider = snowflake.sysadmin
database = var.database_name
schema = var.schema_name
name = "TEST_FUNCTION_NUMBER"
comment = "Test Table function"
return_type = "TABLE (NUM1 NUMBER, NUM2 NUMBER(10,2))"

statement = <<EOT
SELECT 12,13.4
EOT
}

1. terraform apply

select * from table(Test_function_number());

How much impact is this issue causing?

High

Logs

https://gist.github.com/syed3477/6e59d4e88db8dd1f0aca789faeac0d62

Additional Information

This is being applied to Snowflake

CREATE OR REPLACE FUNCTION TF_DEV_SUT_DB.GTD_RPT.TEST_FUNCTION_NUMBER()
RETURNS TABLE ("NUM1" NUMBER(38,0), "NUM2" NUMBER(38,0))
LANGUAGE SQL
COMMENT='Test Table function'
AS ' SELECT 12,13.4
';

This is working correctly in the version = "0.46.0"

If any one has any workaround, please share. Thanks!

[sfc-gh-asawicki]

Hey @syed3477. Thanks for reaching out to us.

I will try to reproduce the issue this week and I will get back to you.

[syed3477]

Hello, I'm just checking in to see if there are any plans to address this bug in an upcoming release. Any updates would be greatly appreciated. Thank you!

[sfc-gh-asawicki]

Hey @syed3477. I will let you know today.

[sfc-gh-asawicki]

Hey @syed3477.

I have just checked what's causing the incorrect behavior. The source of the problem is complex and we can't fix this easily. We will address this with the upcoming functions/procedure rework as part of https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/ROADMAP.md#preparing-essential-ga-objects-for-the-provider-v1 (also: https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/v1-preparations/ESSENTIAL_GA_OBJECTS.MD).

The current workarounds (not ideal) are either:

• create this as float

CREATE OR REPLACE FUNCTION TEST_FUNCTION_NUMBER()
RETURNS TABLE ("NUM1" NUMBER(38,0), "NUM2" FLOAT)
LANGUAGE SQL
COMMENT='Test Table function'
AS ' SELECT 12,13.4::FLOAT';

• use https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest/docs/resources/unsafe_execute

[sfc-gh-asawicki]

Hey @syed3477.

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.