Null column values display as NaN

Problem

You have a table with null values in some columns. When you query the table using a select statement in Databricks, the null values appear as null.

When you query the table using the same select statement in Databricks SQL, the null values appear as NaN.

select * from default.<table-name> where <column-name> is null

Databricks

Query in Databricks returns null

Databricks SQL

Query in Databricks SQL returns NaN

Cause

NaN is short for not a number. This is how null values are displayed in Databricks SQL.

Solution

This is not a problem. Databricks SQL is working as designed.

The representation of null values in Databricks SQL is different from the representation of null values in Databricks, but the data itself is not changed.