Decimal Values are Converted to Integer Values

Today, I have learnt that Snowflake will convert decimal values to integer values if all trailings are zeros. Please see below examples:

SELECT 1000.0, typeof(1000.0), 1000.1, typeof(1000.1);

It returns:

1000 
INTEGER 
1000.1 
DECIMAL

The same applies to PARSE_JSON:

SELECT PARSE_JSON( '{"key": 1000.0}' );

It returns:

{ "key": 1000 }

Instead of:

{ "key": 1000.0 }

After researching with our team, it turned out that it was expected behaviour. In fact, Snowflake has been behaving this way for a long time. However, for the PARSE_JSON, it was recently updated for consistency. It was announced at 4.39 Release Notes back in November 2020. Please refer to section under:

JSON Data: Changes to the Handling of Numeric Values with Trailing Zeros

I have compared with MySQL and PostgreSQL, they however, preserve the decimal type in above scenarios. See my test below:

MySQL:

mysql> select 1000.0;
+--------+
| 1000.0 |
+--------+
| 1000.0 |
+--------+

mysql> SELECT JSON_EXTRACT('{"name": 1000.0}', '$.name');
+--------------------------------------------+
| JSON_EXTRACT('{"name": 1000.0}', '$.name') |
+--------------------------------------------+
| 1000.0                                     |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_TYPE('1000.0');
+---------------------+
| JSON_TYPE('1000.0') |
+---------------------+
| DOUBLE              |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_TYPE('1000');
+-------------------+
| JSON_TYPE('1000') |
+-------------------+
| INTEGER           |
+-------------------+
1 row in set (0.00 sec)

PostgreSQL

postgres=# select 1000.0;
 ?column?
----------
   1000.0
(1 row)

postgres=# select to_json('{"why are we loosing the decimal ?": 1000.0}'::text);
                     to_json
--------------------------------------------------
 "{\"why are we loosing the decimal ?\": 1000.0}"
(1 row)

So look out for those differences.