Float numbers change after insert

[dmi3x3]

Running a query to automatically create a table:

user@computer:~$ curl -sX POST http://manticore-server:9308/products/_create/ -d '{   "title": "Yellow Bag with Tassel",   "price": 19.85 }'
{"error":"Rejecting mapping update to [products] as the final mapping would have more than 1 type: [_doc, _create]"}

the query fails, but the table is created.

add entries to the table:

user@computer:~$ curl -sX POST http://manticore-server:9308/products/_doc/ -d '{   "title": "Yellow Bag with Tassel",   "price": 19.85 }'
{"_id":5407109313183350785,"_index":"products","_primary_term":1,"_seq_no":0,"_shards":{"failed":0,"successful":1,"total":1},"_type":"_doc","_version":1,"result":"created"}

user@computer:~$ curl -sX POST http://manticore-server:9308/products/_doc/ -d '{   "title": "Green Bag with Tassel",   "price": 19.55, prod_type: "local" }'
{"_id":5407109313183350788,"_index":"products","_primary_term":1,"_seq_no":0,"_shards":{"failed":0,"successful":1,"total":1},"_type":"_doc","_version":1,"result":"created"}

user@computer:~$ curl -sX POST http://manticore-server:9308/products/_doc/ -d '{   "title": "Blue Bag with Tassel",   "price": 19.45 }'
{"_id":5407109313183350792,"_index":"products","_primary_term":1,"_seq_no":0,"_shards":{"failed":0,"successful":1,"total":1},"_type":"_doc","_version":1,"result":"created"}

while viewing the table, found that some float numbers are less than or greater than 0.000001

elib@znanium-web:~> mysql -h manticore-server -P9306 -e "select * from products;"
+---------------------+------------------------+-----------+
| id                  | title                  | price     |
+---------------------+------------------------+-----------+
| 5407109313183350786 | Red Bag with Tassel    | 19.750000 |
| 5407109313183350789 | Green Bag with Tassel  | 19.549999 |
| 5407109313183350790 | Black Bag with Tassel  | 19.549999 |
| 5407109313183350791 | White Bag with Tassel  | 19.950001 |
| 5407109313183350792 | Blue Bag with Tassel   | 19.450001 |
| 5407109313183350785 | Yellow Bag with Tassel | 19.850000 |
+---------------------+------------------------+-----------+

Please explain, why this is happening?

Manticoresearch run in docker-compose with minimal config

root@031b7143c3c2:/var/lib/manticore# cat /etc/manticoresearch/manticore.conf
searchd {
    listen = 127.0.0.1:9312
    listen = 127.0.0.1:9306:mysql
    listen = 127.0.0.1:9308:http
    log = /var/log/manticore/searchd.log
    query_log = /var/log/manticore/query.log
    pid_file = /var/run/manticore/searchd.pid
    data_dir = /var/lib/manticore
}

[tomatolog]

seems floating point precision, not sure what is wrong in data your provided

[sanikolaev]

Reproduced on Linux x86_64 and on macos ARM64:

MRE:

snikolaev@dev2:~$ mysql -P9306 -h0 -e "drop table if exists t; create table t(f float); insert into t(f) values(19.45); select * from t"
+---------------------+-----------+
| id                  | f         |
+---------------------+-----------+
| 5839536120173428737 | 19.450001 |
+---------------------+-----------+

[sanikolaev]

the query fails, but the table is created.

Created a bug report about it #2623

[sanikolaev]

@tomatolog is correct. Float numbers themselves don't change after being inserted; it's just that they can be displayed with different precision. For example, in MySQL, you can format the display using the format() function:

mysql> drop table if exists t; create table t(f float); insert into t(f) values(19.45); select f, format(f, 6) from t;
Query OK, 0 rows affected (0.13 sec)

Query OK, 0 rows affected (0.39 sec)

Query OK, 1 row affected (0.01 sec)

+-------+--------------+
| f     | format(f, 6) |
+-------+--------------+
| 19.45 | 19.450001    |
+-------+--------------+
1 row in set (0.00 sec)

In Manticore there's no format() or a similar function, so by default you see higher precision, but the numbers are the same in both Mysql and Manticore.

[dmi3x3]

Thank you for your answers.
But I still do not understand why I inserted a specific number "values(19.45)", but I see 19.450001 in the table, how can I write logs (aka elastic) to such a table? What will their analysis show? If a random small number is added to the inserted data.
Or when inserted into json, can you specify the format of the number?

[sanikolaev]

why I inserted a specific number "values(19.45)", but I see 19.450001 in the table

The float values 19.45 and 19.450001 are essentially the same internally:

What will their analysis show?

It depends entirely on the query, but in theory, since the values are the same internally, there shouldn't be any difference in the query results. The only change might be how the numbers are displayed.

Or when inserted into json, can you specify the format of the number?

You don't need to specify the format when inserting the numbers. You may want to format them when displaying, but as I said:

In Manticore there's no format() or a similar function