| Bug #61131 | UNION / UNION ALL redefines tinyint(1) to tinyint(4) while creating views | ||
|---|---|---|---|
| Submitted: | 11 May 2011 10:19 | Modified: | 11 May 2011 13:57 |
| Reporter: | Luis Marques | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.1.50, 5.1.57, 5.5.13 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Tinyint, UNION, UNION ALL | ||
[11 May 2011 13:57]
Valeriy Kravchuk
Verified just as described on Mac OS X:
macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.57-debug Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create table la_luis_teste (especie_de_boolean tinyint(1));
Query OK, 0 rows affected (0.13 sec)
mysql> create view la_luis_view1 as select * from la_luis_teste;
Query OK, 0 rows affected (0.18 sec)
mysql> desc la_luis_view1
-> ;
+--------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------+------+-----+---------+-------+
| especie_de_boolean | tinyint(1) | YES | | NULL | |
+--------------------+------------+------+-----+---------+-------+
1 row in set (0.11 sec)
mysql> create view la_luis_view2 as select * from la_luis_view1 union all select * from
-> la_luis_view1;
Query OK, 0 rows affected (0.11 sec)
mysql> desc la_luis_view2;
+--------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------+------+-----+---------+-------+
| especie_de_boolean | tinyint(4) | YES | | NULL | |
+--------------------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
[11 May 2011 14:00]
MySQL Verification Team
5.5 affected too.
Description: Using UNION and UNION ALL while creating a view from other view or table redefines datatypes from tinyint(1) to tinyint(4). How to repeat: mysql> create table la_luis_teste (especie_de_boolean tinyint(1)); Query OK, 0 rows affected (0.07 sec) mysql> insert into la_luis_teste (especie_de_boolean) values (1); Query OK, 1 row affected (0.00 sec) mysql> insert into la_luis_teste (especie_de_boolean) values (0); Query OK, 1 row affected (0.00 sec) mysql> insert into la_luis_teste (especie_de_boolean) values (1); Query OK, 1 row affected (0.00 sec) mysql> create view la_luis_view1 as select * from la_luis_teste; Query OK, 0 rows affected (0.00 sec) mysql> desc la_luis_view1 -> ; +--------------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------+------+-----+---------+-------+ | especie_de_boolean | tinyint(1) | YES | | NULL | | +--------------------+------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> create view la_luis_view2 as select * from la_luis_view1 union all select * from la_luis_view1; Query OK, 0 rows affected (0.01 sec) mysql> desc la_luis_view2; +--------------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------+------+-----+---------+-------+ | especie_de_boolean | tinyint(4) | YES | | NULL | | +--------------------+------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> create view la_luis_view3 as select * from la_luis_view1 union select * from la_luis_view1; Query OK, 0 rows affected (0.00 sec) mysql> desc la_luis_view3; +--------------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------+------+-----+---------+-------+ | especie_de_boolean | tinyint(4) | YES | | NULL | | +--------------------+------------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> create view la_luis_view4 as select * from la_luis_teste union select * from la_luis_teste; Query OK, 0 rows affected (0.00 sec) mysql> desc la_luis_view4; +--------------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+------------+------+-----+---------+-------+ | especie_de_boolean | tinyint(4) | YES | | NULL | | +--------------------+------------+------+-----+---------+-------+ 1 row in set (0.00 sec)