In DB GroupBy node, DB Type is Number but Knime Type is String

KNIME Analytics Platform
2

Hi @saddas ,

This appears to be a bug in the sqllite jdbc driver.

To investigate, I created a demo workflow for others to play with, using SQLLite which demonstrates the issue you have raised. You can find that below.

I did a search of the forum and found this: DB Reader ignoring types for SQLite databases - #4 by mlauber71

The suggestion there was to maybe update the SQLite driver to the latest version, or alternatively are you able to move to using an H2 database instead of SQLite? I haven’t tried updating my SQLite driver to see if that will resolve this specific problem. I’m currently using the SQLite 3.36 driver which was the default install on my KNIME version (4.7.1)

Demonstration of the issue

Demo SqlLite Count as String issue.knwf (34.8 KB)

image
image1288×751 127 KB

In three of four different ways of trying to obtain a count, it returns a String. It appears on the face of my examples that the act of renaming the count() column causes KNIME to return the data as a string, even though previewing the data in the DB Table Selector returns it as Integer

image

The only way KNIME returned it as an Integer was by writing a custom query in the DB Table Selector, and not renaming the column.

i.e. This returns an Integer:

SELECT "ArtistId", 
cast(count("AlbumId") as Integer)  
FROM #table#
group by "ArtistId"

But this returns a String:

SELECT "ArtistId", 
cast(count("AlbumId") as Integer) as "COUNT(AlbumId)"
FROM #table#
group by "ArtistId"

I hope this provides some assistance or pointers. Maybe somebody who already has a later SQLite version installed can try the workflow above out to see if the issue persists?

4 Likes