Description:
Though underlying types in union are INT or TINYINT, final result ends up being BIGINT:
CREATE TABLE `xt1` (
`a` tinyint(4) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` bigint(20) DEFAULT NULL
)
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `xv1` AS select `xt1`.`a` AS `a`,`xt1`.`b` AS `b`,`xt1`.`c` AS `c`,1 AS `1`,1 AS `My_exp_1`,1 AS `My_exp_1_1` from `xt1` union select -(1) AS `-1`,-(1) AS `-1`,-(1) AS `-1`,-(1) AS `-1`,-(1) AS `-1`,-(1) AS `-1`
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `xv2` AS select 1 AS `1`,1 AS `My_exp_1`,1 AS `My_exp_1_1`,-(1) AS `-1`,-(1) AS `My_exp_-1`,-(1) AS `My_exp_1_-1`
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `xv3` AS select `xt1`.`a` AS `a`,`xt1`.`b` AS `b`,`xt1`.`c` AS `c` from `xt1`
How to repeat:
mysql> describe xt1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a | tinyint(4) | YES | | NULL | |
| b | int(11) | YES | | NULL | |
| c | bigint(20) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> describe xv1;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| a | bigint(20) | YES | | NULL | |
| b | bigint(20) | YES | | NULL | |
| c | bigint(20) | YES | | NULL | |
| 1 | bigint(20) | NO | | 0 | |
| My_exp_1 | bigint(20) | NO | | 0 | |
| My_exp_1_1 | bigint(20) | NO | | 0 | |
+------------+------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> describe xv2;
+-------------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------+------+-----+---------+-------+
| 1 | int(1) | NO | | 0 | |
| My_exp_1 | int(1) | NO | | 0 | |
| My_exp_1_1 | int(1) | NO | | 0 | |
| -1 | int(2) | NO | | 0 | |
| My_exp_-1 | int(2) | NO | | 0 | |
| My_exp_1_-1 | int(2) | NO | | 0 | |
+-------------+--------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> describe xv3;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a | tinyint(4) | YES | | NULL | |
| b | int(11) | YES | | NULL | |
| c | bigint(20) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Suggested fix:
use proper types returned by unions (as non of underlying fields are BIGINT and this may cause troubles in upper stacks).
Verified against BK builds of 5.0 and 5.1 as of December 5, 2007
