I've encountered an issue with SQL Server when using an updatable CTE when combining a view with a derived column and a table using system versioning.

It causes a stack dump and disconnects the session with the error:

Msg 596 Level 21 State 1 Line 0
Cannot continue the execution because the session is in the kill state.
Msg 0 Level 20 State 0 Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

I've spent some time getting to the bottom of the cause and am able to reproduce the error on any version of SQL Server.

My query is quite complex however I've boiled it down to the following few requirements:

• Create two tables, one will be the target of an update, the other a source of data.
• Create a view on the table containing source data.
• The view must include a derived column eg select 0 as columnName
• The table to update must have system versioning on
• Define a CTE to select columns from the view and join to the target table
• Update the CTE to set column in target table to the value of the derived column in view
• BOOM

If the derived column in the view is replaced with a physical column, or system versioning is disabled, the update works.

It's reproducable and I can demonstrate it with this simple DB<>Fiddle

I'm looking to try and find a workaround. My actual situation is using the updatable CTE to select top N rows from the view of a staging table in order to batch-update a target table (avoiding lock escalation) with the staging table containing 500k - 1m+ rows.