MySQL Bugs: #84053: unreasonable behavior for union bit type with string

Bug #84053	unreasonable behavior for union bit type with string
Submitted:	5 Dec 2016 8:04	Modified:	7 Dec 2016 4:09
Reporter:	帅 Bang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.6, 5.7	OS:	Linux
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
mysql> create table t1(c1 varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2(c1 bit(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values('a'),('b');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1 union select * from t2;
+------+
| c1   |
+------+
| a    |
| b    |
| 1    |
| 2    |
+------+
4 rows in set (0.00 sec)

It seems quite reasonable at first sight. However, in fact, IMHO, this is unreasonable. Why？ 

As we can see from the doc of mysql , and just as bit type indicates that, bit type can be acted as either integer or  byte stream.  In another word, bit type can be interpreted as either integer or byte string and which rule is applied depends on the context. 

mysql> select c1 + 1 from t2;
+--------+
| c1 + 1 |
+--------+
|      2 | // equal with 1 + 1
|      3 | //equal with  2 + 1
+--------+
2 rows in set (0.00 sec)

mysql> select concat("hello ", c1) from t2;

+----------------------+
| concat("hello ", c1) |
+----------------------+
| hello               | //NOTE HERE: not "hello1"  
| hello               | //NOTE HERE: not "hello2" 
+----------------------+
2 rows in set (0.00 sec)

So, IMHO, when we perform a union operation with bit and varchar in mysql,  byte stream rule rather than integer rule for bit type should be applied.

How to repeat:
create table t1(c1 varchar(20));
create table t2(c1 bit(10));
insert into t1 values('a'),('b');
insert into t2 values(1),(2);
select * from t1 union select * from t2;

Suggested fix:
+------+
| c1   |
+------+
| a    |
| b    |
|       |  //not empty string but just non-ascii byte steam
|       |  //not empty string but just non-ascii byte steam
+------+

is returned

Hi,

If you look at the manual you see:

If the data types of corresponding SELECT columns do not match, the types and lengths of the columns in the UNION result take into account the values retrieved by all of the SELECT statements.

Looking at your query the data type of the first and second select do not match. The context you are talking about is "local" - inside a single SELECT, it is not a context of "outer" query, namely the main UNION query in this example, so the behavior is expected. The local context returns 1 and 2 and then the union handles the mismatched columns the way it did.

kind regards
Bogdan Kecman