Paul White digs into a nasty bug:
A more accurate description of the issue would be:
This bug can cause wrong results, incorrect error messages, and statement failure when:
– A data-modification statement requires Halloween Protection.
– That protection is provided by a Parallel Eager Spool.
– The spool is on the probe side of a Batch Mode Hash Join.This issue affects Azure SQL Database and SQL Server 2014 to 2019 inclusive.
Read on for a repro and Paul’s thoughts. As of March 2021, this is an active problem, so it’s worth keeping an eye on in your environment. I’d wager, though, that this probably doesn’t pop up on its own very frequently.