I write this blog post currently sweating in South Vietnam: Even the tap water is too warm here 🤣. There has been a lot of debate lately on LinkedIn whether AI should assist to write posts or not. For me as a non-native speaker it helps a lot. I also profit from restructuring my sometimes unwieldy thoughts 😉. Nevertheless this post is authentic and “hand written”.

This is a story of an accidental conversion in a Data Warehouse load process in SQL Server. It is good practice to follow an ETL scheme: First extracting data from the source system, then transforming and at last writing it to the data warehouse tables. Therefore my extraction layer follows the structure and the data types of the source system. One day I was extremlely puzzled by the following observation:

A tinyint column in my staging table contains NULL, 0, 1 and 2. The source table however contains just NULL, 1 and 2. The question is: Where on earth did that value 0 come from?

After some digging it turned out: I made an mistake. The source system is a bit special and transform-heavy as it stores all kinds of data in varchar columns. The column in my staging table however was integer. This resulted in implicit conversion from varchar to integer: A situation you would like to avoid as a data professional and cast data types explicitly if needed. And – hold your breath – blank characters (like ‘ ‘) convert to 0! At the beginning I was truly surprised! I would have thought that “SELECT CAST(” AS INT)” would result in a conversion error. But it does not and is officially documented.

This behaviour made me curious so I tested various databases on DB Fiddle with the statement above.
Results are:

SQL Server	MariaDB	SQLite	Postgres	Oracle
0	0	0	invalid input syntax for type integer: “”	NULL

Oracle was tested using https://freesql.com/ .

Copilot claims that the SQL standard would define an error for the attempt of casting blank to integer. It turns out that only Postgres seems to follow this standard here.

Lessons learned: If you are a new starter or a seasoned SQL pro like me there is always something new to learn.