Tinyint(1) type in MySQL source


We have a MySQL table which contains a status field with tinyint(1) type.
This field has several values (from 1 to 20)

When we read data of this table, Dremio interprets them by ‘true’ or ‘false’ values (true if 0, false otherwise).
It’s the standard behaviour for reading this kind of data type : by default, tinyint(1) is consider as a boolean.

We have the same behaviour with logstash for instance.
But we can provide an option for a different interpretation by jdbc property.

For instance:

Is it possible to configure Dremio MySQL source with this option ?


Can you send us a snippet of the ddl/dml used to create the table?

Hi @jumo

Would something like the below work

mysql> create table tinyint_test (a tinyint(1));

Query OK, 0 rows affected (0.07 sec)

mysql> insert into tinyint_test values(1);

Query OK, 1 row affected (0.15 sec)

mysql> insert into tinyint_test values(2);

Query OK, 1 row affected (0.03 sec)

mysql> insert into tinyint_test values(3);

Query OK, 1 row affected (0.13 sec)

mysql> insert into tinyint_test values(4);

Query OK, 1 row affected (0.17 sec)

mysql> insert into tinyint_test values(5);

Query OK, 1 row affected (0.05 sec)

On Dremio if you query this, all fields are shown as true (like you described)

SELECT * FROM localmysql.idio.tinyint_test


cast the column to a varchar like below

SELECT cast(a as varchar) FROM localmysql.idio.tinyint_test

Output will be like below



Hi @balaji.ramaswamy,

Thks for the tips, it works fine.
So double casting if you want an integer :

cast(cast(a_tinyint as varchar) as int)


@balaji.ramaswamy , sorry for bumping an old thread.

While there is a workaround like above, this tinyint(1) completely breaks UI preview. I’m using 3.3.2 and this happened when I open a MySQL datasource table from Web UI which contain tinyint(1)

FUNCTION ERROR: Invalid value for boolean: ‘2’

I know this is a feature from MySQL side which sometimes comes in handy, but if we can put extra JDBC options when creating/modifying MySQL data source, that would be great.


Mysql tinyint is BOOLEAN, if you create a Mysql table with a column type BOOLEAN, it actually stores as tinyint(1), see below example

create table testbool(col1 boolean);

mysql> desc testbool;
| Field | Type | Null | Key | Default | Extra |
| col1 | tinyint(1) | YES | | NULL | |

When Dremio finds a tinyint datatype, it only expects 0 or 1 values and any other value we reject saying “Invalid value for boolean: ‘n’”


If I could change the table on the source, I would. The problem is, We have no control on what our dev team team doing. That’s why if we could just add extra JDBC params to handle issue like this, that would be great!
p.s: We have another pipelines which can handle this. But in some cases, we must plug some of our DB right into Dremio.

Hello @balaji.ramaswamy the fact that boolean is stored as tinyint does not mean tinyint is boolean. This assumption is wrong and is causing Dremio to be unable to read simple MySQL tables. The cast solution is an workaround, not very efficient though.

Paraphrasing Michael Dunn:
The MySQL Documentation says BOOL and BOOLEAN are synonyms for TINYINT(1) (and this makes sense, since booleans are stored as a 0 or 1 in the database itself and is only rendered as true or false at select-time). The documentation does not say that TINYINT(1) is a synonym for BOOLEAN .

The documentation says “all German Shepherds are dogs” while Dremio assumes by default that “all dogs are German Shepherds”.

1 Like

Is Dremio considering to change this behavior in future versions? Or do we need to keep doing this workaround?


This fix will be a part of future release. We would like to recommend to use the workaround till then.


This will be fixed in next release ?

Hello @koolay

I mean in the upcoming releases. Not in the next release.
I will update the thread when the fixed version is available.



There’s any update?

There’s any update?

@bigfacewo Here is the mapping between Mysql and Dremio data types


@balaji.ramaswamy I know this mapping. I mean, should tinyint be converted to int instead of Boolean? Because of the accuracy loss, the data range of tinyint is -128 ~ 127. MySQL :: MySQL 5.7 Reference Manual :: 11.1.2 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT