MySQL Bugs: #116523: bit field type is not correct for in column definition of text resultset packet

Bug #116523	bit field type is not correct for in column definition of text resultset packet
Submitted:	1 Nov 2024 8:14	Modified:	1 Nov 2024 11:19
Reporter:	ximin liang (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	9.1.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hello MySQL team:
  I found a case seems not as expected. It is about bit type using distinct.
  Can be repeated in MySQL 9.1.0
  

How to repeat:
Execute sqls as follow:
 create table t1(c1 bit(10));
 insert into t1(c1) values (0xff);
 select distinct c1 from t1 order by c1;
 +------+
 | c1   |
 +------+
 |  255 |
 +------+
  
 using tcpdump can get that in column definition of text resultset packet,
 type of c1 is MYSQL_TYPE_LONG.
 
 It also can be seen from result format, if we execute
 select  c1 from t1 ;
 +------------+
 | c1         |
 +------------+
 | 0x00FF     |
 +------------+
 result is in hex-format, using tcpdump,  type of c1 is FIELD_TYPE_BIT.

Note that there is an easier way than tcpdump:

https://dev.mysql.com/doc/refman/9.1/en/mysql-command-options.html#option_mysql_column-typ...

using MySQL client with --column-type-info option
result is: 

mysql>   select distinct c1 from t1 order by c1;
Field   1:  `c1`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       LONGLONG
Collation:  binary (63)
Length:     10
Max_length: 3
Decimals:   0
Flags:      UNSIGNED NUM 

+------+
| c1   |
+------+
|  255 |
+------+

mysql>   select  c1 from t1 ;
Field   1:  `c1`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       BIT
Collation:  binary (63)
Length:     10
Max_length: 2
Decimals:   0
Flags:      UNSIGNED 

+------------+
| c1         |
+------------+
| 0x00FF     |
+------------+
1 row in set (0.00 sec)

Hi Mr. liang,

Thank you for your bug report.

This is expected behaviour when you apply DISTINCT to the column of type BIT.

We are not sure whether this is documented in our Reference Manual, but we shall check and may be this report becomes a Documentation bug.

Hi,

Actually, this bug turns out to be a duplicate of the following bug:

https://bugs.mysql.com/bug.php?id=115597

The original bug is now in the process of being fixed.

Duplicate.