terraform-provider-azurerm: azurerm_mssql_database long_term_retention_policy requires week_of_year and never re-applys clean (reopen #9067)

reopening parts of #9067 the docs say each part is optional, but if i only supply weekly_retention i get an error on apply:

 Error: issuing create/update request for Sql Server "usos1sql01-inmp-test" (Database "usos1dbo01-inmp-test") Long Term Retention Policies (Resource Group "usos1rgp01-inmp-test"): sql.BackupLongTermRetentionPoliciesClient#CreateOrUpdate: Failure sending request: StatusCode=0 -- Original Error: Code="LongTermRetentionMissingWeekOfYear" Message="WeekOfYear is required to be set between 1 and 52 in order to set yearly retention."  

again since the docs say optional, i expect not to have to set it, but to work around i put week_of_year = 1 and it applies, but then the very next apply the diff shows:

      ~ long_term_retention_policy {
          ~ week_of_year      = 0 -> 1
            # (3 unchanged attributes hidden)
        }

trying to set week_of_year = 0 leads to this error

Error: expected long_term_retention_policy.0.week_of_year to be in the range (1 - 52), got 0

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave “+1” or “me too” comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment

Terraform (and AzureRM Provider) Version

❯ tf -v Terraform v1.0.4 on windows_amd64

• provider registry.terraform.io/hashicorp/azuread v1.6.0
• provider registry.terraform.io/hashicorp/azurerm v2.72.0
• provider registry.terraform.io/hashicorp/null v3.1.0
• provider registry.terraform.io/microsoft/azuredevops v0.1.6

Affected Resource(s)

• azurerm_mssql_database

Terraform Configuration Files

resource "azurerm_mssql_database" "example" {
  name            = "example"
  server_id       = azurerm_mssql_server.mgmt.id
  elastic_pool_id = azurerm_mssql_elasticpool.mgmt.id
  max_size_gb     = 100

  threat_detection_policy {
    state                      = "Enabled"
    email_account_admins       = "Enabled"
    use_server_default         = "Enabled"
    storage_endpoint           = var.storage_account_blob_endpoint
    storage_account_access_key = var.storage_account_access_key
  }

  short_term_retention_policy {
    retention_days = 35
  }

  long_term_retention_policy {
    weekly_retention = "P45D"
    # this shouldn't be needed, but get's you past the first error
    # week_of_year = 1
  }
}

Debug Output

Panic Output

Expected Behaviour

• Can set weekly retention WITHOUT setting week_in_year
• Setting week_in_year to 1 is reflected in state and subsequent runs don’t affect

Actual Behaviour

• week_in_year is required
• rerunning with week_in_year = 1 always shows a difference in plan

Steps to Reproduce

1. terraform apply

Important Factoids

References

• #9067