As demonstrated in DB GroupBy and DB Reader.knwf (73.9 KB) , the DB GroupBy node shows strange results when using SUM.
SUM of total population for the first group is negative! There is no negative population in the table.
The output column type for SUM is Integer but it’s Double for TOTAL.
The other issue here is that -on Windows- when we read data using the DB Reader node, the output is String. The format would be normal if we use SUM (and the strange values are corrected), but using TOTAL results in scientific notation (in a string column). On Mac the output in both cases is Double.
well I have taken a closer look and reason for negative number occurrence is when sum is higher than maximum integer value in KNIME (integer overflow is it called?). To overcome that you can set input mapping of integers to Long --> Number (Long) (or just specific column that you will sum). However this still doesn’t overcome String type output after GroupBy node (possibly a bug or maybe not ). Reason behind scientific notation for doubles is probably expected as you get same when converting big doubles to string with Number To String node.
Anyways someone will have to take a deeper look into it and come back to this topic. Attaching workflow where I played a bit… DB GroupBy and DB Reader_ipazin.knwf (62.6 KB)
Hello armingrudd,
have you tried it with another database e.g. H2. SQLite tends to have problems when it comes to providing useful type information.
Bye
Tobias
No I have not tested the case with other DBs. I encountered this issue while I was designing an assignment for the students and wanted to use SQLite as it does not need installation.
But still I wonder why there are different behaviors on Mac and Windows.
Hi,
that is indeed a good question. we use the SQLite JDBC Driver which comes with included binaries for Windows, Mac and Linux. Maybe there is a difference in the OS implementation of SQLite which is written in C. Also SQLite is “typeless” which leads to all kinds of problems.
That is why I recommend to use H2 which behaves much more like a proper database compared to SQLite and also does not require any installation.
Bye
Tobias
). Reason behind scientific notation for doubles is probably expected as you get same when converting big doubles to string with 