SQL real precision incorrect

[getvictor]

Bug report

What operating system and version are you using?

MacOS 14.4 arm64

+---------+--------+----------+
| version | build  | platform |
+---------+--------+----------+
| 14.4    | 23E214 | darwin   |
+---------+--------+----------+

What version of osquery are you using?

5.11.0 (master)

What steps did you take to reproduce the issue?

Distributed queries return seemingly wrong results due to numerical precision issues when converting numbers to strings.

For example, the query: SELECT ROUND(3.531, 2) as num; returns "distributed_query_433":[{"num":"3.5299999999999998"}]

The correct result can be achieved by using a cast: SELECT CAST(ROUND(3.531, 2) as text) as num; returns "distributed_query_435":[{"num":"3.53"}]

This issue is not directly seen in osqueryi but can be simulated like:

SELECT printf("%.16f", ROUND(3.531, 2)) as num;
+--------------------+
| num                |
+--------------------+
| 3.5299999999999990 |
+--------------------+

Root cause

SQLite REAL type only stores 15 significant digits, so trying to represent a number with more than 16 digits will cause this issue.

In osquery core, the conversion from double to string should limit precision to 16 digits (15 significant + 1 non-significant)