Object's float property rounded for queries following the one creating the property

CrateDB
1

Hi all,
happy new year, just hoping it will be better than 2020 …
I’m still running crate 2.3.11 (I know, it’s old) and I’ve just stumbled on a weird behavior : When I’m setting an object’s property which is a Float to a specific value, it’s rounded. If I insert it into a different property name, then it’s not rounded anymore.
The weird thing is that both properties have the same underlying type (as shown by "
show create table ).

CREATE TABLE IF NOT EXISTS "sc"."indicators" (
   "date_created" TIMESTAMP,
   "date_processed" TIMESTAMP,
   "id" STRING,
   "index1_monthly" FLOAT,
   "indicator_source_id" INTEGER NOT NULL,
   "indicators" OBJECT (DYNAMIC) AS (
      "battery" FLOAT,
      "battery_low" LONG,
      "battery_state" LONG,
      "cfg_switch_error" LONG,
      "co2level" FLOAT,
      "co2level_invalid" LONG,
      "co2level_max" FLOAT,
      "co2level_mean" FLOAT,
      "conf_done" LONG,
      "conf_err_state" LONG,
      "contact_state" FLOAT,
      "disposable" FLOAT,
      "distance" FLOAT,
      "distance_low_alert_end" LONG,
      "distance_low_alert_start" LONG,
      "frameCounter" LONG,
      "humidity" FLOAT,
      "humidity_invalid" LONG,
      "hw_error" LONG,
      "index" FLOAT,
      "index1" FLOAT,
      "index1_daily" FLOAT,
      "index1_hourly" FLOAT,
      "index1_live" FLOAT,
      "index1_monthly" FLOAT,
      "index1_raw" LONG,
      "index1_type" LONG,
      "index2" FLOAT,
      "index2_daily" FLOAT,
      "index2_hourly" FLOAT,
      "index2_live" FLOAT,
      "index2_monthly" FLOAT,
      "index2_type" LONG,
      "index3" FLOAT,
      "index4" FLOAT,
      "index5" FLOAT,
      "index_daily" FLOAT,
      "index_hourly" FLOAT,
      "index_live" FLOAT,
      "index_monthly" FLOAT,
      "izair_src" LONG,
      "iziair" LONG,
      "low_battery" LONG,
      "luminosity" LONG,
      "max_flow1" FLOAT,
      "max_flow2" FLOAT,
      "min_flow1" FLOAT,
      "min_flow2" FLOAT,
      "motion_count" LONG,
      "parking_state" LONG,
      "peak_sound_level" FLOAT,
      "pressure" FLOAT,
      "raw_index" FLOAT,
      "raw_index1" LONG,
      "raw_index2" LONG,
      "raw_max_flow1" FLOAT,
      "raw_max_flow2" FLOAT,
      "raw_min_flow1" FLOAT,
      "raw_min_flow2" FLOAT,
      "sensor_reset_cause" LONG,
      "sn" LONG,
      "snr" LONG,
      "sound_level" FLOAT,
      "temperature" FLOAT,
      "temperature_ext" FLOAT,
      "temperature_high_alert_end" LONG,
      "temperature_high_alert_start" FLOAT,
      "temperature_int" FLOAT,
      "temperature_invalid" LONG,
      "temperature_low_alert_end" FLOAT,
      "temperature_low_alert_start" LONG,
      "temperature_max" FLOAT,
      "temperature_mean" FLOAT,
      "triggered" LONG,
      "usb_off" LONG,
      "user_triggered" LONG,
      "voclevel" FLOAT,
      "voltage" FLOAT,
      "voltage_idle" FLOAT,
      "wirecut" LONG
   ),
   "message_id" STRING,
   "month" TIMESTAMP GENERATED ALWAYS AS date_trunc('month', "date_created"),
   "sensor_id" STRING,
   PRIMARY KEY ("id", "month")
)
CLUSTERED INTO 4 SHARDS
PARTITIONED BY ("month");

Then if I perform the following insert :

For example : If I insert my raw (properties index1, index2, index3 having already been created before for other raws, index4 being a “new” property):
insert into sc.indicators (date_created, date_processed, id, indicator_source_id ,indicators, message_id, sensor_id) values (1610176002521, 1610370232881, 'test-id, 0, {“index1”=28670.1892"index2"=28670.1892,“index3”=28670.1892,“index4”=28670.1892,“index_live”=0,“raw_index”=28670188}, ‘msg-test’, ‘s-test’);

index1, index2, index3 will have the value 28670.19 while index4 will have the value 28670.1892
Now if I delete the raw and create it again, index4 will have the value 28670.19.
From now on, for every raw, the property “index4” will also be truncated like the others … The value is correct only for the first insert, after it’s truncated !

Any hints ? known bug ?
Thanks

2

@echarlus
Hi,

Sorry for the late response, we are quite busy in the beginning of the year.
Any reasons, why you don’t want to update to a later version?

How are you checking which values are actually stored?
It seems to me like a precision problem :confused:

I just tested it with 4.3.4

Using FLOATs

CREATE TABLE IF NOT EXISTS  t1 (
    num FLOAT,
    obj OBJECT(DYNAMIC) AS (
        num1 FLOAT,
        num2 FLOAT,
        num3 FLOAT,
        num4 FLOAT
    )
);
INSERT INTO t1 (num,obj) VALUES (28670.1892, '{"num1":28670.1892,"num2":28670.1892,"num3":28670.1892,"num4":28670.1892}');
SELECT num,
       obj,
       obj['num1'],
       obj['num2'],
       obj['num3'],
       obj['num4']
FROM t1;
-- All values 28670.19

Using DOUBLE PRECISION


CREATE TABLE IF NOT EXISTS  t2 (
    num DOUBLE PRECISION,
    obj OBJECT(DYNAMIC) AS (
        num1 DOUBLE PRECISION,
        num2 DOUBLE PRECISION,
        num3 DOUBLE PRECISION,
        num4 DOUBLE PRECISION
    )
);
INSERT INTO t2 (num,obj) VALUES (28670.1892, '{"num1":28670.1892,"num2":28670.1892,"num3":28670.1892,"num4":28670.1892}');
SELECT num,
       obj,
       obj['num1'],
       obj['num2'],
       obj['num3'],
       obj['num4']
FROM t2;
-- All values 28670.1892
3

28670.19 is btw the “correct” / expected FLOAT Value

public class Main {
    public static void main(String[] args) {
        double num_double = 28670.1892;
        float num_float = (float)28670.1892;
        System.out.println("num_double: " + num_double);
        System.out.println("num_float: " + num_float);
    }
}

output

num_double: 28670.1892
num_float: 28670.19

Process finished with exit code 0
4

Hi,
thanks for the reply and the explanations, I see that Float have only 2 decimals which I had not noticed.
However, this does not explain why, when I query the first time, I’m getting a number with 4 decimals while when I delete & re-insert I then get float with 2 decimals … Results should be consistents…

5

Hi,

Floats are following IEEE 754 and have a precision of ~ 7 digits.
That being said floating point values should not be used for equality operations anyway (i.e. I would consider it fine that the returned values are within specification)
As for the reason, I am not 100% certain, but it could be that the original string values is cached somewhere.

However I also have a hard time to reproduce your mentioned problem with a newer version of CrateDB.

i.e.

DROP TABLE t1;
CREATE TABLE IF NOT EXISTS  t1 (
    num FLOAT,
    obj OBJECT(DYNAMIC) AS (
        num1 FLOAT,
        num2 FLOAT,
        num3 FLOAT
    )
);
INSERT INTO t1 (num,obj) VALUES (28670.1892, '{"num1":28670.1892,"num2":28670.1892,"num3":28670.1892}');

SELECT num,
       obj,
       obj['num1'],
       obj['num2'],
       obj['num3']
FROM t1;
-- all values 28670.19

then insert new row:

INSERT INTO t1 (num,obj) VALUES (28670.1892, '{"num1":28670.1892,"num2":28670.1892,"num3":28670.1892,"num4":28670.1892}');

SELECT num,
       obj,
       obj['num1'],
       obj['num2'],
       obj['num3'],
       obj['num4']
FROM t1;
-- all values 28670.19

works as expected

6

Thanks for the update. As you see on my end, the result is not conform to the IEEE 754 spec since when I’m storing 28670.1892 I’m reading 28670.19 … so the value is truncated to 2 decimal digits.
When I have time I’ll try with newer crate version and see if I can reproduce

7

As you see on my end, the result is not conform to the IEEE 754

Actually it is. Also this is nothing CrateDB specific.

28670.1892
d: 2 | 8 | 6 | 7 | 0 | 1 | 8 | 9 | 2
n: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

For single precision floats the fraction is 23 bits (i.e. a value between 0 and 2^23 (== 8388608)
=> ~7 digits precision (6 are safe)

image
image1204×162 21.8 KB

source: https://upload.wikimedia.org/wikipedia/commons/d/d2/Float_example.svg

8

Could you maybe run the above post code examples within the cluster and see if you can recreate the issue?