I've got a simple table in MariaDB kinda like this:
CREATE TABLE IF NOT EXISTS event_counts_per_group (
group_id INT NOT NULL,
day DATE NOT NULL,
event_count INT NOT NULL DEFAULT 0,
PRIMARY KEY (group_id, day)
);
And I have a query kinda like this:
SELECT SUM(event_count) FROM event_counts_per_group GROUP BY group_id;
But, I'm finding that the column datatype in the returned dataset is a float
in Ignition.
I looked through the MySQL JDBC docs on datatype conversions and it still doesn't make sense to me. I'm assuming that MariaDB must be upgrading the SUM() to a float to avoid overflow, but I can't find anyone else complaining about that online.
Since I know my numbers are smaller, I tried using CAST(SUM(event_count) AS INTEGER)
instead, which changes my results to long
types. That's better but still overkill. MariaDB doesn't seem to allowCAST(x AS MEDIUMINT)
or other smaller integer types.
This isn't causing me any serious issues, but I'm curious if anyone could explain why the original SUM() result was being upgraded to float, and also if there is any way to get the datatype of the column in the Ignition result dataset to come back as int instead of long or float.
Yeah, I could re-cast the whole column after getting it back into Ignition, but I feel like there must be a more efficient way I just can't find online.
If it matters, my actual queries are all being done through system.db.runPrepQuery
with proper ?
parameters, I just showed the plain SQL here for ease of reading.