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

[27 Jan 2006 20:27] Andre Timmer
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!!
[27 Jan 2006 20:46] Mark Matthews
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.
[27 Jan 2006 21:55] Jorge del Conde
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)
[27 Jan 2006 22:13] Andre Timmer
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.
[27 Jan 2006 22:19] Andre Timmer
In the plan table 'test5' is mentioned, it's really the table mentioned, just a name change.
[27 Jan 2006 22:44] Mark Matthews
> 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).
[4 Aug 2006 15:10] Georgi Kodinov
> 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.
[7 Aug 2006 17:34] Andre Timmer
Shouldn't it be called BOOLEAN?
[9 Aug 2006 15:54] Georgi Kodinov
I'm closing this bug as a sub-case of bug #12185.