Bug #103289 bit type display confusing
Submitted: 12 Apr 15:00 Modified: 13 Apr 12:58
Reporter: jiangtao guo Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: bit type

[12 Apr 15:00] jiangtao guo
Description:
drop table if exists t1;
create table t1(c1 bit(10));
insert into t1 values(0b10101);
select c1 from t1;
select coalesce(c1) from t1;

The result of directly select is different with the result when used in coalesce . The first result is more resonable.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.23 MySQL Community Server - GPL
mysql> select c1 from t1;
+------------+
| c1         |
+------------+
| 0x0015     |
+------------+
1 row in set (0.00 sec)

mysql> select coalesce(c1) from t1;
+----------------------------+
| coalesce(c1)               |
+----------------------------+
| 0x3231                     |
+----------------------------+
1 row in set (0.00 sec)

How to repeat:
drop table if exists t1;
create table t1(c1 bit(10));
insert into t1 values(0b10101);
select c1 from t1;
select coalesce(c1) from t1;
[13 Apr 12:40] MySQL Verification Team
Hi Mr. guo,

Thank you for your bug report.

However, I have problems in repeating it. Here is my, very slightly changed test case:

create table t1(c1 bit(10));
insert into t1 values(b'10101');
select c1 from t1;
select coalesce(c1) from t1;
select coalesce(NULL,c1) from t1;
drop table if exists t1;

drop table if exists t1;
create table t1(c1 bit(10));
insert into t1 values(0b0000010101);
select c1 from t1;
select coalesce(c1) from t1;
select coalesce(NULL,c1) from t1;
drop table if exists t1;

And here are my results : 

----------------------------

c1
\0
coalesce(c1)
21
coalesce(NULL,c1)
21
c1
\0
coalesce(c1)
21
coalesce(NULL,c1)
21

-------------------------------

They are as they should be, because b'10101' is 21 in decimal notation.
[13 Apr 12:58] MySQL Verification Team
Hi Mr. guo,

This turns to be a regression bug in 8.0.23. Hence, it is a very recent bug.

We ran your test case couple of more times and got the same results as you.

Verified as reported.
[4 May 8:05] Erlend Dahl
Posted by developer:
 
You get exactly the same output in 5.7 if you start the client with --binary-as-hex=ON (this is the default in 8.0 since 8.0.19).