NULL vs NaN in SQL Mode

rv1 — Fri, 30 Sep 2022 22:28:47 GMT

In SQL Mode | SQL Editor there seems to be no distinction between NULL and NaN.

In some cases it is very misleading as it makes the user to search mistake in the wrong place.

DE/DS mode works as expected:

UPDATE: a bit later I found this article: https://kb.databricks.com/en_US/dbsql/display-null-as-nan

However I believe it is a complete nonsense (and the number of downvotes says for that). First of all, Mr. Adam Pavlacka misinterpreted the problem: not "Null column values display as NaN", it is other way around. Secondly, the behaviour described in the article is not reproducible in the latest versions of Databricks: Spark engine does not interpret NaN as NULL or conversely, it is only UI problem of Table visualization. Maybe the article was actual at the time of writing, but now it is obsolete.

topic NULL vs NaN in SQL Mode in Data Engineering

NULL vs NaN in SQL Mode