Question

TinyInt converted to Boolean whether or not it's used as one

  • 21 September 2018
  • 3 replies
  • 1436 views

TINYINT in MySQL 5.6 are supposed to be able to hold values up to 127 (or 255 if unsigned), but when we query for the results from a TINYINT column it gets automatically translated into a boolean field. Example:


SELECT tiny_int_value, COUNT(*) FROM table GROUP BY tiny_int_value

returns


        tiny_int_value	COUNT(*)
∅ 626308
true 30740
true 30576
true 28133

when it should return


    tiny_int_value	COUNT(*)
∅ 626308
0 30740
1 30576
2 28133

I don’t see a clear way to fix this.


3 replies

Hey Stephanie,


In MySQL, tinyint(1) is a synonym for boolean. Even though values from -127 to 127 can be stored in it, when using SQL Runner, the values will show up as ‘true’ for non-zero values and ‘false’ for 0 values by default.


There are a couple options to fix this:




  1. Use a cast. SELECT cast(tiny_int_value as signed) FROM table




  2. The second is to update the database connection, and add this as an additional parameter:

    tinyInt1isBit=false. Which will look like this:




14%20PM


Let me know if this solves your issue!


Noah

This is helpful, thank you Noah!

Userlevel 1

tinyint(1) is a synonym for boolean



No it’s not; it’s the other way around. 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 the MySQL client Looker uses assumes by default that “all dogs are German Shepherds”.


I’m glad there’s a workaround, though.

Reply