MariaDB JBDC type casting question

Ignition
1

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.

1 Like
2

Interesting. Never ran into this before but I am using the MariaDB driver for a MySQL 5.6 and using your table/example I get the same thing, it's coming through as a float, regardless of if I use the group by or not, runPrepQuery vs runQuery vs namedQuery (scalar or not) - all float.

Doing some testing in MySQL Workbench makes me think its not the driver itself though. Check this out if you look at the field types of a regular old SELECT event_count FROM event_counts_per_group; you get what you expect, an INT type

image
image957×78 2.42 KB

But running SELECT SUM(event_count) FROM event_counts_per_group; even though the the result looks like an int, now shows it as a DECIMAL

image
image953×75 2.56 KB

So I think then once it comes back to Ignition and the db is saying hey this is a decimal, boom now we got a float. I think.

3 Likes
3

Looks like you are correct.

Apparently you have to do:

SELECT kind, CAST(SUM(n) AS SIGNED) n 
  FROM table1 
 GROUP BY kind

crazy

3 Likes
4

So my CAST(SUM(x) AS INTEGER) is about as good as I'm going to get. Weird, but still better than passing a table of floats around...

I've never actually used the DECIMAL type before. It's unusual.

1 Like