[MDEV-10599] UNION / UNION ALL redefines tinyint(1) to tinyint(4) Created: 2016-08-19  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Uli Korn Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: upstream


 Description   

Using UNION and UNION ALL redefines datatypes from tinyint(1) to tinyint(4).

How to do:

 
 
mariadb> create table table_test (column_test_boolean tinyint(1));
 
Query OK, 0 rows affected (0.07 sec)
 
 
 
mariadb> insert into table_test (column_test_boolean) values (1), (0), (1);
 
Query OK, 1 row affected (0.00 sec)
 
 
 
 
 
mariadb> create table table_test_2 select * from table_test;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mariadb> desc table_test_2
 
    -> ;
 
+---------------------+------------+------+-----+---------+-------+
 
| Field               | Type       | Null | Key | Default | Extra |
 
+---------------------+------------+------+-----+---------+-------+
 
| column_test_boolean | tinyint(1) | YES  |     | NULL    |       |
 
+---------------------+------------+------+-----+---------+-------+
 
 
 
mariadb> create table table_test_3 select * from table_test union all select * from table_test;
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
mariadb> desc table_test_3;
 
+---------------------+------------+------+-----+---------+-------+
 
| Field               | Type       | Null | Key | Default | Extra |
 
+---------------------+------------+------+-----+---------+-------+
 
| column_test_boolean | tinyint(4) | YES  |     | NULL    |       |
 
+---------------------+------------+------+-----+---------+-------+

It occurs when opening the select, so it affects "select", "create table ... select", "creates view ... as select", ....
The problem is that i use TinyInt(1) fields as boolean,
so TinyInt(4) breaks my code.

 Comments   
Comment by Elena Stepanova [ 2016-08-26 ]

5.5-10.2 and MySQL 5.5-5.7 are affected.
There are several upstream bugs of the same kind, e.g.
https://bugs.mysql.com/bug.php?id=61131
https://bugs.mysql.com/bug.php?id=30886

Comment by Alexander Barkov [ 2017-10-06 ]

The same behavior is reproduced with INT(5) :

DROP TABLE IF EXISTS t1,t2;
 
CREATE TABLE t1 (a INT(5));
 
CREATE TABLE t2 AS SELECT a FROM t1 UNION SELECT a FROM t1;
 
DESCRIBE t2;


+-------+---------+------+-----+---------+-------+
 
| Field | Type    | Null | Key | Default | Extra |
 
+-------+---------+------+-----+---------+-------+
 
| a     | int(11) | YES  |     | NULL    |       |
 
+-------+---------+------+-----+---------+-------+

Notice, mixing INT(5) + INT(5) for UNION create an INT(11) column.

Comment by Alexander Barkov [ 2017-10-06 ]

When mixing data types, we could preserve the length, and instead of:

  • tinyint(1) UNION tinyint(1) = tinyint(4)
  • int(5) UNION int(5) = int(11)

return the following data types:

  • tinyint(1) UNION tinyint(1) = tinyint(1)
  • int(5) UNION int(5) = int(5)

It will be easier to do in MariaDB-10.3, which introduced a more convenient way to handle data types.

Generated at Thu Feb 08 07:43:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.