Bug #103289 bit type display confusing

• Submitted: 12 Apr 2021 15:00
• Modified: 11 May 2021 5:28
• Reporter: jiangtao guo
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any
• Tags: bit type

[12 Apr 2021 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 2021 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 2021 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 2021 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).

[11 May 2021 5:28] jiangtao guo

Actually what I want to say is that results in the following situations are different(Server Version: 8.0.23, --binary-as-hex=ON is added when connect to server):
1. select col_bit from t;
2. select coalesce(col_bit) from t;

Result of the first SQL is "0x0015", and the second is "0x3231". And in my option, both results should be "0x0015".

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;