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

mysql
done
low_priority
reply

(Stephanie Hutson) #1

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.


#2

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


(Stephanie Hutson) #3

This is helpful, thank you Noah!


(Michael Dunn) #4

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.