MySQL Bugs: #16854: tinyint(1) are not treated as booleans in group by

Bug #16854	tinyint(1) are not treated as booleans in group by
Submitted:	27 Jan 2006 20:27	Modified:	9 Aug 2006 15:54
Reporter:	Andre Timmer	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	MySQL 5.0.18	OS:
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
Driver: mysql-connector-java-3.1.12

select type, ind_nomail, count(*)
from   vestiging 
group by 1,2      

Returns more than the maximum of 4 rows.

Cause: 
- in MySQL 5 tinyint(1) is default treated as a boolean according to specs
  However: this is apparently NOT true

How to repeat:
CREATE TABLE `vestiging` (
  `type` tinyint(1) NOT NULL default '0',
  `ind_nomail` tinyint(1) NOT NULL default '2'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into vestiging (type, ind_nomail) values (5, 6);
insert into vestiging (type, ind_nomail) values (7, 1);
insert into vestiging (type, ind_nomail) values (0, 1);
insert into vestiging (type, ind_nomail) values (1, 0);

select type, ind_nomail, count(*)
from   vestiging 
group by 1,2   

Suggested fix:
Threat as boolean.

Note:
- out intention is that these columns contain numbers, will convert then to tinyint with lenth 2

However still think this is a bug.

Find it confusing to thread tinyint(1) different, intuitivily it looks like a number.
A more transparent implementation would be to introduce a real boolean type!!

Changed to a server bug, as (1) Types are handled by the server, and (2) this is a case of resolution through temporary table clobbering the type, so the driver "loses" the TINYINT(1) type (more than likely the server is returning INT as the type).

An "explain" of your query posted here, as well as the schema and some test data for reproducing the error would help in diagnosing what's going on in the server.

mysql> select type, ind_nomail, count(*)
    -> from   vestiging
    -> group by 1,2
    -> ;
+------+------------+----------+
| type | ind_nomail | count(*) |
+------+------------+----------+
|    0 |          1 |        1 |
|    1 |          0 |        1 |
|    5 |          6 |        1 |
|    7 |          1 |        1 |
+------+------------+----------+
4 rows in set (0.00 sec)

Using unix (Solaris 8) mysql client it produces numbers.
Using jdbc it produces true and false.

So maybe it's still a jdbc problem?

The plan using mysqld is:
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | test5 | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

When using jdbc it's the same.

In the plan table 'test5' is mentioned, it's really the table mentioned, just a name change.

> Using unix (Solaris 8) mysql client it produces numbers.
> Using jdbc it produces true and false.

> So maybe it's still a jdbc problem?

No, it's not a JDBC problem. TINYINT(1) is mapped to boolean in the JDBC driver, as MySQL has no true BOOLEAN type (the _server_ substitutes TINYINT(1) for this type when used in DDL).

What's happening is that because of your GROUP BY, the server is using a temporary table, causing it to "simplify" the datatypes, so the _server_ returns INT(something) as a datatype for the column that was previously TINYINT(1). There's no straightforward way for any client, including the JDBC driver to determine that the column was originally TINYINT(1).

> What's happening is that because of your GROUP BY, the server is using a
> temporary table, causing it to "simplify" the datatypes, so the _server_ returns
> INT(something) as a datatype for the column that was previously TINYINT(1).

Verified the report with the latest 5.0. The above seems not to be the case anymore : 
create view v1 as select vtype, ind_nomail, count(*) from vestiging group by 1,2;
create view v2 as select vtype, ind_nomail from vestiging;
desc v1;
Field Type    Null    Key     Default Extra
vtype tinyint(1)      NO              0
ind_nomail    tinyint(1)      NO              2
count(*)      bigint(21)      NO              0
desc v2;
Field Type    Null    Key     Default Extra
vtype tinyint(1)      NO              0
ind_nomail    tinyint(1)      NO              2

Besides there is a "real" boolean type now. Here's what the Manual says (chapter 11.2) : "As of MySQL 5.0.3, a BIT data type is available for storing bit-field values. (Before 5.0.3, MySQL interprets BIT as TINYINT(1).) In MySQL 5.0.3, BIT is supported only for MyISAM. MySQL 5.0.5 extends BIT support to MEMORY, InnoDB, and BDB.".

So I'm reassigning to Mark.

Shouldn't it be called BOOLEAN?

I'm closing this bug as a sub-case of bug #12185.