Bug #11335 View redefines TinyInt(1) column definition
Submitted: 15 Jun 2005 0:33 Modified: 7 Aug 2005 1:51
Reporter: Julian Ray Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.06 Beta OS:Microsoft Windows (Win XP)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[15 Jun 2005 0:33] Julian Ray
Description:
When creating a view from a table with columns defined as TinyInt(1) , the columns are materialized in the view as TinyInt(4).  This causes other systems to  omit processing the column data as Boolean and process it as Integer.

How to repeat:
Create a table with a tinyint(1) column. Create a view from the table. Look at the  column metadata for the view.
[15 Jun 2005 6:42] Aleksey Kishkin
mysql> create table b11335 ( b tinyint(1));
Query OK, 0 rows affected (0.16 sec)

mysql> create or replace view v11335 as select * from b11335;
Query OK, 0 rows affected (0.00 sec)

mysql> check table v11335;
+-------------+-------+----------+----------+
| Table       | Op    | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| test.v11335 | check | status   | OK       |
+-------------+-------+----------+----------+
1 row in set (0.00 sec)

mysql> show create table v11335;
+--------+-----------------------------------------------------------------------------------------------------------+
| View   | Create View                                                                                               |
+--------+-----------------------------------------------------------------------------------------------------------+
| v11335 | CREATE ALGORITHM=UNDEFINED VIEW `test`.`v11335` AS select `test`.`b11335`.`b` AS `b` from `test`.`b11335` |
+--------+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> desc v11335;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| b     | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
[27 Jun 2005 18:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26459
[29 Jul 2005 21:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27732
[4 Aug 2005 21:00] Evgeny Potemkin
Fixed in 5.0.12, cset 1.1860.54.3
[7 Aug 2005 1:51] Mike Hillyer
Documented in 5.0.12 changelog:

<listitem><para>
 Columns defined as <literal>TINYINT(1)</literal> redefined as <literal>TINYINT(4)</literal> when incorporated into a <literal>VIEW</literal>. (Bug #11335)
</para></listitem>