To clarify why despite having a handful of fixes for this issue that it is still considered an issue for us...

The actual update statement that is being executed is usually dynamically generated, which is why this example uses dyanamic SQL to execute the update statement.

The reason dynamic SQL is being used is because the real dbo.Foo in our database has dozens of columns and is referenced by 20 indexed views. In order to prevent every single one of those indexed views from being pulled into the execution plan to perform maintenance, we use dynamic SQL to generate the update statement including only columns which changed. If a columns value did not change, then that column is not included in the generated update statement. Which means any indexed views that are referencing that column will not be triggered to perform maintenance updates.

Is this an architecture that probably needs refactoring? Yes.
Is that going to happen any time soon? No.

That said...Not all findings of this issue are the result of a dynamically generated update statement. We have found many instances where non-dynamically generated statements are resulting in the same bad plan. It's just that when using dynamic SQL it is very easy to reproduce this issue. I have yet to find a reliable method for reproducing this issue without using dynamic SQL.

SO, what all this means is...we can't simply rely on a query level hint, option, tweak, etc. Because there are dozens of versions of this same query peppered all across the database and app code.

And we can't rely on Query Store, because new versions of this query pop up all the time. We'd have to be monitoring query store constantly in order to detect every single instance of any queries that have this bad plan.

So, the solution either ends up needing to be A) major refactoring of a fairly central piece of our database code. Or B) see if Microsoft can flag this as a bug and fix it at the optimizer level.

Good plan on left, bad plan on right:

You can see where the calculator failure results in a selectivity of 1, which then impacts the cardinality to go from ~3.5 rows to 5,000,000 rows, which is the entire table.

Useful PowerShell snippet to cleanup line breaks and scientific notation values in query plans and optimizer debugging exports:

(gcb -Raw) `
    -replace '\d(\.\d+)?e[-+]\d\d',
             { ([decimal]$_.Value).ToString('0.000###########') } `
    -replace "[`n`r]+",
             "`r`n" | scb