MySQL Bugs: #106241: BIT column serialized incorrectly for aggregate function

Bug #106241	BIT column serialized incorrectly for aggregate function
Submitted:	22 Jan 2022 0:37	Modified:	16 Aug 2022 21:23
Reporter:	Bradley Grainger (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When a BIT(n) column is selected using an aggregate function (e.g., MIN, MAX), it is serialized on the wire incorrectly by MySQL Server. Specifically, BINARY_FLAG is set in the ColumnDefinition packet (which usually means the BIT(n) column is being sent as text), but the column value is actually sent as a MSB-first binary value.

This is similar to bug #97113, but the problem has gotten worse: now aggregate functions are affected, not just CASE expressions.

How to repeat:
Create a table with a BIT(n) column:

CREATE TABLE bits(data BIT(32));
INSERT INTO bits(data) VALUES(1);

Execute the following two queries:

SELECT data FROM bits;
SELECT MAX(data) FROM bits;

I will attach a packet capture if possible.

The first query sends FIELD_TYPE_BIT, length 32, UNSIGNED_FLAG with four data bytes: 00 00 00 01. BINARY_FLAG is not specified and data is sent as binary.

The second query sends FIELD_TYPE_BIT, length 32, UNSIGNED_FLAG|BINARY_FLAG with four data bytes: 00 00 00 01. The BINARY_FLAG is specified but data is sent as binary, when text is expected.

In MySQL Server 8.0.27, the second query is different. The server's column metadata is the same (FIELD_TYPE_BIT, length 32, UNSIGNED_FLAG|BINARY_FLAG) but there is just one data byte: 0x31 (i.e., ASCII '1').

Thus, in MySQL Server 8.0.28, data is serialized differently on the wire (binary, not text) but the column metadata hasn't changed. This will cause clients to interpret the data incorrectly.

If the query "select max(data) from bits;" is run in MySQL Workbench, it will show the answer as "0" when the expected result is actually "1".

Suggested fix:
Revert to MySQL Server 8.0.27's serialization logic and send the data in text format.

Packet capture with MySQL Server 8.0.28

Attachment: dump28.pcap (application/octet-stream, text), 1.64 KiB.

Packet capture with MySQL Server 8.0.27, to show previous behaviour

Attachment: dump27.pcap (application/octet-stream, text), 1.64 KiB.

Incorrect results shown in MySQL Workbench

Attachment: workbench.png (image/png, text), 12.91 KiB.

Here is the debug output from mysql --column-type-info

MySQL 8.0.27

mysql> SELECT MAX(data) FROM bits;
Field   1:  `MAX(data)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       BIT
Collation:  binary (63)
Length:     32
Max_length: 1
Decimals:   0
Flags:      UNSIGNED BINARY

+----------------------+
| MAX(data)            |
+----------------------+
| 0x31                 |
+----------------------+
1 row in set (0.00 sec)

MySQL 8.0.28

mysql> SELECT MAX(data) FROM bits;
Field   1:  `MAX(data)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       BIT
Collation:  binary (63)
Length:     32
Max_length: 4
Decimals:   0
Flags:      UNSIGNED BINARY

+----------------------+
| MAX(data)            |
+----------------------+
| 0x00000001           |
+----------------------+
1 row in set (0.00 sec)

You can see that the column type info is all the same, but the data itself has changed.

It's possible that this regression was introduced by the fix for bug #100859.

Hello Bradley,

Thank you for the report and test case.

regards,
Umesh

Posted by developer:
 
Fixed as of the upcoming MySQL 8.0.31 release, and here's the proposed changelog entry from the documentation team:

An aggregate query on a BIT field returned a value that was formatted as a
bit string, but also had the BINARY flag added automatically. New
validation now checks for and skips setting the BINARY flag for BIT
results.

Thank you for the bug report.