I’ve encountered an issue with the DB Query Reader node which kinda messes up my data.
We have a column named “products_availability” of data type TINYINT in our SQL DB.
Values used are 0, 1 or 3.
Somehow, the DB Query Reader node is recognizing this column as data type BIT and makes a BOOLEAN out of it -> meaning I cannot differenciate between values 1 and 3 anymore (both are set to TRUE) which I need to do because it’s a big difference for us and I need to filter for the values within the workflow.
How do I change the behavior of the Query Reader node so that it’s not falsifying my data?
I tried the input type mapping tab but there I can only select BOOLEAN for BIT data types.
Using KNIME 4.0.2
that is a bit weird. Can you tell me what database are you using? Was this column always type tinyint in your database or it went through changes? Can you try to read another table having tinyint column to see how that ends?
Thanks again for the response.
We’re using MySQL 5.6.40. There haven’t been any changes to the database lately. It also seems like typical behavior of the node, it’s the same with all other TINYINT columns in other tables.
I’m working around this issue now by using CAST for the columns in my query but that shouldn’t be necessary and one must be aware that KNIME is altering data in this case…
Hello Rahul,
I just tested this with our MySQL db with the following workflow and get the displayed expected results. Tinyint columns are returned as integer columns in KNIME:
In the DB Query Reader node I execute an DESCRIBE statement for the test table. Can you please do the same and compare it with my result. Can you also please have a look at the DB Data out port view of a DB Table Selector to see what DB Type and DB Type Name the driver reports to KNIME for the problematic column.
If this all looks good please double check that none of the nodes has changed the input type mapping for tinyint database type and that the input type mapping of the MySQL Connector node has the following mapping:
The type mapping is carried along the DB Connection/Data connections. So whatever you specify in the connector node is used in all connected nodes unless you change the mapping via the DB Type Mapper node. For further details see the Type Mapping section in the DB documentation.
Using DESCRIBE in the DB Query Reader node, I get a correct type for all TINYINT columns.
DB Data of a DB Table Selector is showing a mixed result. DB Type is BIT but DB Type Name is TINYINT:
I’m not sure what the reason for this is and what the difference between DB Type and DB Type Name is.
Also checked the mappings in MySQL Connector node. No issues here.
Hello Rahul,
the output of the describe table statement help. It seems that TINYINT(1) is a synonym for boolean in MySQL.
" BOOL, BOOLEAN: These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true" https://dev.mysql.com/doc/refman/8.0/en/numeric-type-overview.html
I guess that is why the driver returns Bit as data type. The mapping framework only considers the DB Type which explains why it suggests boolean. We will check if we can do something about this discrepancy in the returned information from MySQL to map TINYINT(1) to integer even though the driver return Bit as type.
Bye
Tobias
Hello Rahul,
one more addition. Using the build in MySQL driver version 5.x I get the same result as you did. However I just tested it also with the included MySQL 8.x driver and it actually returns BIT for DB Type and Type Name:
So looking at the type name will no longer tell KNIME that this BIT is actually a TINYINT(1) and we can no longer do anything about this discrepancy. I will do some more testing and see if we can safely add a mapping rule from bit to int that you have to explicitly select in the mapping.
Bye
Tobias
Hi Tobias,
Thanks for the response. The behavior of the node kinda makes sense now.
An explicit mapping BIT to INT would be nice if it keeps the column values this way.
However, the workaround using CAST in the query for these columns is working as well.
