Having "NaN" value for REAL and FLOAT producing severe error.

Attention: DO NOT RUN THIS IN PRODUCTION!!!

That blog post is about the real error in SQL Server.
It was reproduced on SQL Server 2016 CU10 and on SQL Server 2019 RTM.

Database in trouble has a table with FLOAT column. It's Front-End application verifies user's input and inserts the data into that column using TRY_PARSE function.
The developer's intention was that any "Not-a-Numeric" or "Out-of-Range" values will be automatically converted to NULL and it will be for user's discretion to verify and fix these values.

However, one of the application users was very educated and instead of empty space, NULL or any other bad not numeric value the user supplied data with value of "NaN" for empty cells, which simply stands for "Not a Numeric".
That action caused a database corruption!

Here is how to reproduce it.

At first will create a table:

USE tempdb; GO DROP TABLE IF EXISTS tbl_Real_Float_Test; GO CREATE TABLE tbl_Real_Float_Test (ID INT IDENTITY(1,1), R REAL, F FLOAT); GO

Will insert some bad data:

INSERT INTO tbl_Real_Float_Test(R,F) SELECT TRY_PARSE('0' as REAL), TRY_PARSE('NaN' as FLOAT); GO INSERT INTO tbl_Real_Float_Test(R,F) SELECT TRY_PARSE('NaN' as REAL), TRY_PARSE('0' as FLOAT); GO

Now will try to query it:

SELECT R FROM tbl_Real_Float_Test WHERE ID = 1; GO SELECT F FROM tbl_Real_Float_Test WHERE ID = 2; GO SELECT ID FROM tbl_Real_Float_Test WHERE R = 0 OR F = 0; GO

First two queries will return zeroes. The third one will produce an error:

It also will write that error into the ERRORLOG:

Error: 9100, Severity: 23, State: 2.  Possible index corruption detected. Run DBCC CHECKDB.

For the easiest reproduction of the destructive behavior of "NaN" you can run following statement:

DECLARE @r REAL = PARSE('NaN' as REAL); PRINT 'We can Assign "NaN" to a variable, but we cannot see it:'; SELECT @r;

You will get an error of:

An error occurred while executing batch. Error message is: Arithmetic Overflow.

Interestingly enough the TRY_PARSE command allows "Culture" specification, which, in some cases, works very well:

DECLARE @Russian REAL = TRY_PARSE('NaN' as REAL USING 'Ru-RU'); DECLARE @US_Real REAL = TRY_PARSE('NaN' as REAL USING 'en-US'); SELECT Russian_Real = @Russian; SELECT US_Real = @US_Real;

As you can see, the Russian culture treats the TRY_PARSE command correctly and produces "NULL" instead of the error. Besides of Russian it also works for Finnish, Swedish, Arabic, Traditional Chinese and undocumented "sr-Latn-CS".
No any other cultures treat "NaN" as it expected.

If you can reproduce that bug you can help Microsoft to fix it by voting for that bug:  https://feedback.azure.com/forums/908035-sql-server/suggestions/39278515-try-parse-and-parse-produce-an-error-converting-n

Again: DO NOT RUN IT IN PRODUCTION and do not forget to drop your test table!