MySQL Bugs: #11335: View redefines TinyInt(1) column definition

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:	Windows (Win XP)
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

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.

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)

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

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

Fixed in 5.0.12, cset 1.1860.54.3

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>