Bug #33004 integer constants casted to bigints by unions
Submitted: 5 Dec 2007 15:51 Modified: 5 Oct 2017 16:12
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0, 5.1, 5.5.7-bzr OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

[5 Dec 2007 15:51] Domas Mituzas
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
[5 Oct 2017 16:12] Paul Dubois
Posted by developer:
 
Fixed in 9.0.0.

Integer columns in UNION statements could be cast to BIGINT even if a
smaller integer type were more appropriate.
[13 Feb 1:19] Paul Dubois
Posted by developer:
 
Fixed in 8.0.5, not 9.0.0.