DB Reader ignoring types for SQLite databases

[Vernalis]

The DB Reader node appears to be ignoring numeric column types with nested queries in SQLite databases. (This behaviour may apply to other DB types too, but it does not apply to PostgresQL)

The attached workflow shows a set of examples. In all cases, the DBData port ‘Cache Rows’ button does observe and respect the column types.

Examples
In each case, we have a simple DB in SQLite, with a single table (‘test’) with a column of integers, a column of doubles, and a column of strings:

SELECT COUNT(1) FROM test - would expect a ‘Long’ column, and indeed running this query directly in a DB Query Reader node returns exactly that.
But, if we use a DB Table Selector node to first connect to the table, followed by a DB Query node (SQL: SELECT COUNT(1) FROM #table# AS "table") and then DB Reader, the output is a single String column (Left), but the port view for the DB Query (Right) shows nearer (we have Integer rather than Long) the expected:

Even using the nested SQL generated in the DB Table Selector/DB Query node sequence:

SELECT COUNT(1) FROM (SELECT * FROM test
) AS "table"

directly in a DB Query Reader node shows the expected behavior.

Similarly, using DB GroupBy to find MIN/MAX values of a numeric column shows the expected numeric values in the output port view:

But, the output of a subsequent DB Reader node reverts to Strings:

The attached workflow shows these cases
DB Type Bug.knwf (88.4 KB)

Driver version: SQLite (ID=SQLite, Version=3.23.0)

Steve

[bruno29a]

@Vernalis this is indeed a weird behaviour, and not what I would have expected.

Good find, and also excellent explanation and demo with the different cases Steve.

Hopefully this will get fixed.

[thor]

What I know from past experiences is that SQLite behaves very strange when it comes to types. The JDBC driver often reports wrong types for columns. Therefore we recommend using H2 instead of SQLite when you want to use an embedded database. Not sure if this is feasible in your case.

[mlauber71]

@Vernalis it seems a newer driver would fix that problem. I tested 3.36 and 3.39 and they both work fine.

I had the 3.36 within my KNIME installation (or so I think). I installed the latest one in addition. @thor maybe KNIME can update the default driver.

You can check the SQLite version from KNIME.

[Vernalis]

Thanks Thorsten,

To be honest, I came across this because I use SQL lite for our community and internal database test workflows because it is so easy to use in those situations (I’m currently working on migrating our ‘Database’ nodes to ‘DB’ versions - ‘Coming soon’…!)

(Incidentally, the legacy reading mechanisms seem not to show this issue)

I will investigate H2 as a possible alternative, or, as @mlauber71 suggests, newer drivers

UPDATE: H2 works seemlessly in the testing environment as a replacement - thanks

@thor

What I know from past experiences is that SQLite behaves very strange when it comes to types. The JDBC driver often reports wrong types for columns. Therefore we recommend using H2 instead of SQLite when you want to use an embedded database. Not sure if this is feasible in your case.

Steve