Hi @tobias.koetter,
Thank you for your prompt response and for pointing me to the SQLite flexible typing docs and the DB Data Spec Extractor node.
If I understand correctly, KNIME should use the DB type returned from the database for the type mapping. I ran the DB Data Spec Extractor on both example queries in my workflow, and in both cases the DB type for standard_value is reported as NUMERIC (see the new attached workflow to the bottom).
In both DB Query Reader nodes, the Input Type Mapping is: NUMERIC → String → String. So I would expect standard_value to come out as a String column in both cases.
However:
-
In the first query, this is what happens: standard_value is a String, which matches the mapping.
-
In the second query, standard_value is instead an Integer column, and the values are truncated. For example, for activity_id = 31906, the value is truncated from 0.18 (in SQLite) to 0 in KNIME, which causes problems.
Given that the DB Data Spec Extractor reports the same DB type (NUMERIC) in both cases, and the mapping is the same, I don’t understand why one query yields a String column and the other yields an Integer with truncated values. Am I missing an additional inference step that DB Query Reader applies on top of the DB type?
I’d really appreciate any clarification on this behaviour.
Thanks in advance!
Gio
sqlite_type_mapping_problem_02.knwf (156.2 KB)