Specification of data type properties (string length, number precision/scale) for function return schema property are not retained and always proposed as changes

[dlawrences]

Provider Version

0.40.0

Terraform Version

1.1.6

Describe the bug

Data type properties such as the length of strings or the precision/scale of numbers set for UDF return-type properties are not retained after apply. The changes to the datatype of a property propagate correctly to the database (i.e., they have the intended effect when using the function), however Terraform will propose the same changes in the next plan again.

Expected behavior

Terraform to not propose such changes in the immediate plan if they have been applied successfully.

Code samples and commands

Define a function like:

resource "snowflake_function" "function" {
    comment = "Function using tabular return type with enriched data type and normal"
    database = "VMX_INT"
    name = "SOME_FUNCTION"
    return_type = "TABLE (FIRST_FUNCTION_OUTPUT_COL NUMBER, SECOND_FUNCTION_OUTPUT_COL NUMBER(38, 10))"
    schema = "RDV"
    statement = <<-EOT
        SELECT 1234.56 AS FIRST_FUNCTION_OUTPUT_COL, 1234.56 AS SECOND_FUNCTION_OUTPUT_COL
    EOT
}

Plan the change:

Apply it:

Re-run the plan immediately:

Additional context

At any point after the first plan apply or the apply of any subsequent plan, the function works as expected:

SELECT *
FROM TABLE ( RDV.SOME_FUNCTION() );

Results:

FIRST_FUNCTION_OUTPUT_COL	SECOND_FUNCTION_OUTPUT_COL
1235	1234.5600000000

However, it very much seems that Snowflake doesn't present the correct return-type as well:

DESCRIBE FUNCTION RDV.SOME_FUNCTION();

property	value
signature	()
returns	TABLE (FIRST_FUNCTION_OUTPUT_COL NUMBER, SECOND_FUNCTION_OUTPUT_COL NUMBER)
language	SQL
body	SELECT 1234.56 AS FIRST_FUNCTION_OUTPUT_COL, 1234.56 AS SECOND_FUNCTION_OUTPUT_COL

SHOW FUNCTIONS LIKE 'SOME_FUNCTION' IN SCHEMA RDV;

created_on	name	schema_name	is_builtin	is_aggregate	is_ansi	min_num_arguments	max_num_arguments	arguments	description	catalog_name	is_table_function	valid_for_clustering	is_secure	is_external_function	language
2022-09-08 13:29:08.928 -07:00	SOME_FUNCTION	RDV	N	N	N	0	0	SOME_FUNCTION() RETURN TABLE (FIRST_FUNCTION_OUTPUT_COL NUMBER, SECOND_FUNCTION_OUTPUT_COL NUMBER)	Function using tabular return type with enriched data type and normal	VMX_INT	Y	N	N	N	SQL

All this leads me to think that whatever the Terraform Provider can get from Snowflake while running the ReadFunction isn't enough since Snowflake doesn't seem to retain that additional datatype metadata.

Can you confirm this is a problem caused by Snowflake? If so, perhaps you can help with getting the right ticket to Snowflake Core?

Thanks!

[sfc-gh-asawicki]

Hey @dlawrences.

In v0.100.0, we have introduced new function resources. Handling the data types for both attributes and return types was greatly improved (with some Snowflake-related limitations). Please check the docs (e.g. https://registry.terraform.io/providers/Snowflake-Labs/snowflake/latest/docs/resources/function_sql) and let us know.

[sfc-gh-asawicki]

Closing due to inactivity.