DB Query Reader wrong data type

Hi all,

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.
image

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

Thanks.

Hi there @Rahul_istores,

welcome to KNIME Community!

I see. Have you tried mapping by column name? Something like this:

Br,
Ivan

Thanks @ipazin for the welcome and the response.

I’ve tried the mapping by column name, without success.


I assume it’s not working because the node doesn’t recognize the columns source type as TINYINT.

Hi there @Rahul_istores,

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?

Br,
Ivan

Hi @ipazin,

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…

Best
Rahul

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.

Bye
Tobias

1 Like

Hi @tobias.koetter,

Thanks for your response.

Using DESCRIBE in the DB Query Reader node, I get a correct type for all TINYINT columns.
image

DB Data of a DB Table Selector is showing a mixed result. DB Type is BIT but DB Type Name is TINYINT:
image
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.

Regards
Rahul

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

2 Likes

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.

Best
Rahul

1 Like

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.