SQL cast operator not working properly

[my_community2]

please have a look at the attached screenshot

Three strings converted to float, each resulting in the same number.

22015683.000000000000000000 => 22015684

22015684.000000000000000000 => 22015684

22015685.000000000000000000 => 22015684

[MartinB · Accepted solution]

Hi @Maciej G​ ,

I guess, this has something to do with the data type FLOAT and its precision.

Floats are only an approximation with a given precision. Either you should consider using date type DOUBLE (double precision compared to FLOAT) - or, if you know upfront the number of digits before and after the decimal separator of the values to be expected use DECIMAL data type.

%%sql
WITH test_setup AS (
  SELECT '22015683.000000000000000000' AS `string`
  UNION ALL
  SELECT '22015684.000000000000000000' AS `string`
  UNION ALL
  SELECT '22015685.000000000000000000' AS `string`
)
SELECT
    `string`
  , cast(`string` AS float)            AS `float`
  , cast(`string` AS double)           AS `double`
  , cast(`string` AS decimal(26,18))   AS `decimal`
  , cast(`string` AS integer)          AS `integer`
FROM
  test_setup