[osquery 5.13.1] does not respect ROUND sql

[pacamaster]

This is a tracking issue for osquery issue: https://github.com/osquery/osquery/issues/8301

Fleet version: <!-- Copy this from the "My account" page in the Fleet UI, or run fleetctl --version --> 4.45.0

Web browser and operating system: Current and up-to-date (Chrome Version 121.0.6167.184 (Official Build) (arm64) / Sonoma 14.3.1)

---

💥  Actual behavior

Running query with ROUND returns strange results, osquery shell returns correct data and respects ROUND

SELECT pid, total_size, name, ROUND(((total_size * 1.0) / (1024 * 1024)), 2) AS used FROM processes ORDER BY total_size DESC;

The tagged client reported this issue and I was able to reproduce it in the current Fleet release with our dogfood

🧑‍💻  Steps to reproduce

1. Run a query with ROUND in osqueryi shell
2. get correct results returned
3. Run the same Querry in Fleet, it returns full float numbers along with logging destination and during query report for the saved results

🕯️ More info (optional)

Unsure if this was more of a feature request, but couldn't find the issue filed either for this. With the extra lines of data it takes up a bunch of extra space/resources.

[JoStableford]

Related to a Slack conversation

[sharon-fdm]

Reproduced on my Mac as well

[getvictor]

This appears to be an osquery issue. Here's what I'm seeing on distributed/write:

{
    "queries": {
        "fleet_distributed_query_425": [
            {
                "pid": "658407",
                "total_size": "1215423651840",
                "name": "chrome",
                "used": "1159118.3200000001"
            },
            {
                "pid": "658476",
                "total_size": "1215403827200",
                "name": "chrome",
                "used": "1159099.4099999999"
            },
            {
                "pid": "1116467",
                "total_size": "109725528064",
                "name": "WebKitWebProces",
                "used": "104642.42"
            },
            {
                "pid": "1116436",
                "total_size": "94692233216",
                "name": "gnome-shell-por",
                "used": "90305.550000000003"
            },

[getvictor]

osquery PR: https://github.com/osquery/osquery/pull/8302

[getvictor]

@pacamaster This issue has a workaround -- cast the problematic data as text, like:

SELECT pid, total_size, name, CAST(ROUND(((total_size * 1.0) / (1024 * 1024)), 2) AS text) AS used FROM processes ORDER BY total_size DESC;

[sharon-fdm]

@getvictor I removed the milestone since this is osquery core, and we are not sure about the ETA.

[lucasmrod]

@getvictor

QA notes

I'm still seeing the issue after building osqueryd on macOS with today's commit in master:

commit e4ff72ee0b88e3101ad36cc6328d4634fab7f483 (HEAD -> master, origin/master, origin/HEAD)
Author: Stefano Bonicatti <sxxx@xxx.com>
Date:   Thu Jun 13 20:02:24 2024 +0200

    Correct 5.12.2 changelog (#8348)

commit fae29d081af4998823571e0ecc6d4e0c5b8d52eb
Author: Victor Lyuboslavsky <xxx@xxx.com>
Date:   Mon Jun 10 11:07:52 2024 -0500

    Using `std::setprecision` to get the right precision for double to string conversion. (#8302)

sudo ./osquery/osqueryd --version
osqueryd version 5.12.1-14-ge4ff72ee0-dirty

Query: SELECT pid, total_size, name, ROUND(((total_size * 1.0) / (1024 * 1024)), 2) AS used FROM processes ORDER BY total_size DESC;

[getvictor]

Waiting for latest PR to be merged: https://github.com/osquery/osquery/pull/8355

[lucasmrod]

I've tested https://github.com/osquery/osquery/pull/8355 and it works as expected now: