Object conversion with JavaScript UDF in Snowflake (floats)

Question

Consider the following JavaScript UDF in Snowflake:

create or replace function PUBLIC.TEST(F FLOAT)
    returns VARIANT
    language JAVASCRIPT
    returns NULL on NULL INPUT
    immutable
    comment = 'Test.'
as
    $$
        try {
            return F;
        } catch (err) {
            return err;
        }
    $$
;

This function receives a FLOAT value and returns it as a VARIANT. For instance,

select PUBLIC.TEST(-73.60738);

returns

-7.360738000000001e+01

Not bad but I must admit I'm a bit annoyed with the exponential notation and the floating-point arithmetic, especially since a similar function returning a float instead returns the value as received. And such a result is not consistant with a simple SQL statement such as

select -73.60738::VARIANT;

which returns

-73.60738

Obviously, such a function is quite useless but I wrote it while trying to figure out the causes of a similar annoyance with a real use case, namely, reversing the order of the vertices of a given geometry in a GEOGRAPHY value. For my real use case, I developed a function (ST_REVERSE) that receives a GEOGRAPHY as a parameter and reverse the order of the vertices of its geometries using simple array operations (I can forward the code if anyone is interested) but I'm somehow disappointed when I see that

select PUBLIC.ST_REVERSE(TO_GEOGRAPHY('LINESTRING (-73.60746 45.52045, -73.60744 45.52050, -73.60738 45.52053, -73.60730 45.52056, -73.60718 45.52058, -73.60708 45.52059)'))

returns

{
  "coordinates": [
    [
      -7.360708000000000e+01,
      4.552059000000000e+01
    ],
    [
      -7.360718000000000e+01,
      4.552058000000000e+01
    ],
    [
      -7.360730000000000e+01,
      4.552056000000000e+01
    ],
    [
      -7.360738000000001e+01,
      4.552053000000000e+01
    ],
    [
      -7.360744000000000e+01,
      4.552050000000000e+01
    ],
    [
      -7.360746000000000e+01,
      4.552045000000000e+01
    ]
  ],
  "type": "LineString"
}

Such behaviour makes testing and validation a tad more challenging for the human eye but, more to the point, it greatly increases the size of data exported as GeoJSON downstream.

Are such results expected? Can anyone provide explanations and/or some workaround?

Greg Pavlik Greg Pavlik · Accepted Answer · 2021-01-15T22:26:03

This is more a workaround for your specific issue rather than a general fix. Your coordinates appear to have 6 decimal places. You can use the JavaScript number.toFixed() function.

create or replace function PUBLIC.TEST(F FLOAT)
    returns VARIANT
    language JAVASCRIPT
    returns NULL on NULL INPUT
    immutable
    comment = 'Test.'
as
    $$
        try {
            return F.toFixed(6);
        } catch (err) {
            return err;
        }
    $$
;

This may create minor problems of its own that you should be able to workaround. For example, the output of the function is double quoted. For your larger function with a JSON of coordinates, that should be fixable. The numbers will also have trailing zeros, which in the JSON should just be ignored by whatever is reading them.

Object conversion with JavaScript UDF in Snowflake (floats)

1 Answers