| Bug #97113 | BIT column serialized incorrectly in CASE expression | ||
|---|---|---|---|
| Submitted: | 6 Oct 2019 3:12 | Modified: | 11 Oct 2019 12:09 |
| Reporter: | Bradley Grainger (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Connection Handling | Severity: | S3 (Non-critical) |
| Version: | 8.0.17 | OS: | Linux (Debian GNU/Linux 9) |
| Assigned to: | CPU Architecture: | Other (x64) | |
[6 Oct 2019 3:13]
Bradley Grainger
Packet capture demonstrating the problem
Attachment: dump.pcap (application/octet-stream, text), 1.39 KiB.
[7 Oct 2019 12:06]
MySQL Verification Team
Hi Mr. Grainger, Thank you for your bug report. I do not think that this is a bug. You made lots of assumptions and possible mistakes. First of all, using the alias with the same name as a column. Then, you presume that NULL should be of the type BIT, which is not possible ... Last, but not least, do not send packets when MySQL CLI can send the output about the column types that it received .......
[7 Oct 2019 15:03]
Bradley Grainger
Field types from MySQL Workbench
Attachment: field-types.png (image/png, text), 43.14 KiB.
[7 Oct 2019 15:05]
Bradley Grainger
The column alias isn't important; delete it. The BIT column is not "NOT NULL", so NULL is a completely valid value to be stored in it; it's also a completely valid value to return from an expression. I've attached a screen shot of the field types from MySQL Workbench. You can see that it interprets the column as "BIT" but reads the value as 0 when it should be 1. This is because the server is serializing the data incorrectly on the wire; see the packet capture for a clear demonstration of the problem.
[8 Oct 2019 11:54]
MySQL Verification Team
Hi Mr. Grainger, You do not seem to have understood me. When NULL is used in an expression, like a CASE expression, then its type is the one that is presumed by default. If you wish to have a specific type returned, you will have to do some typecasting ... Not a bug.
[8 Oct 2019 15:07]
Bradley Grainger
Can you run the query in MySQL Workbench and confirm that it displays 0 as the result of the column? If so, can you explain how that is a valid value for the result of the expression? My expectation is that the value is either the value of the column, which is 1, or NULL. Neither of those are 0, but that is what MySQL Workbench displays. (Or, if the expression isn't valid, MySQL Server should emit a syntax error.) If we accept that that is the incorrect answer, we have to determine whether it's a client-side or server-side bug. I believe my packet capture demonstrates that it is server-side.
[9 Oct 2019 11:52]
MySQL Verification Team
As I wrote before, check the types in MySQL CLI. Your category is "Server" and not "Workbench".
[9 Oct 2019 16:26]
Bradley Grainger
I did check the types (and attached a screenshot). It shows that the result column is BIT(1) (as expected). Here's the output from mysql --column-type-info mysql> SELECT value FROM query_bit; Field 1: `value` Catalog: `def` Database: `test` Table: `query_bit` Org_table: `query_bit` Type: BIT Collation: binary (63) Length: 1 Max_length: 1 Decimals: 0 Flags: UNSIGNED +-------+ | value | +-------+ | ☺ | +-------+ 1 row in set (0.00 sec) mysql> SELECT MAX(value) FROM query_bit; Field 1: `MAX(value)` Catalog: `def` Database: `` Table: `` Org_table: `` Type: BIT Collation: binary (63) Length: 1 Max_length: 1 Decimals: 0 Flags: UNSIGNED BINARY +------------+ | MAX(value) | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) mysql> SELECT CASE WHEN name IS NOT NULL THEN value ELSE NULL END FROM query_bit; Field 1: `CASE WHEN name IS NOT NULL THEN value ELSE NULL END` Catalog: `def` Database: `` Table: `` Org_table: `` Type: BIT Collation: binary (63) Length: 1 Max_length: 1 Decimals: 0 Flags: UNSIGNED BINARY +-----------------------------------------------------+ | CASE WHEN name IS NOT NULL THEN value ELSE NULL END | +-----------------------------------------------------+ | ☺ | +-----------------------------------------------------+ 1 row in set (0.00 sec) You can see that the serialization is inconsistent. The first and third have the value 0x01, the second has 0x31 (i.e., '1'). But the first has "UNSIGNED" and the second and third have "UNSIGNED BINARY". The server is sending a BIT(1) column in three different ways, with inconsistent flags that make it impossible for any client to know how to parse the returned data correctly.
[10 Oct 2019 12:26]
MySQL Verification Team
That is actually expected behaviour. That is how MAX() function works. You should handle it in your application.
[10 Oct 2019 22:11]
Bradley Grainger
I *do* handle it in my application. I write a MySQL client library (that understands the MySQL Protocol). Here is the code that parses the packet off the wire when the BINARY flag isn't set: https://github.com/mysql-net/MySqlConnector/blob/4e22f350dd768007590d7fb2861d377f7a7710ca/... And here is the code that parses the packet off the wire when the BINARY flag is set: https://github.com/mysql-net/MySqlConnector/blob/4e22f350dd768007590d7fb2861d377f7a7710ca/... What I'm trying to point out is that when MySQL Server evaluates the third expression (in my original bug report) it both sets the BINARY flag and sends binary data. This is not consistent with how it serializes the results of the first two expressions; it ends up sending a packet that can't be deserialized correctly. MySQL Server sends a BIT(1) field in one of three different ways, and there is not enough information in the packet on the wire to tell those three encodings apart. Thus, client applications are left with ambiguous information and fail to report the result correctly. You can see that by using MySQL Workbench (as reported in this bug), by using mysql CLI (also reported in this bug), by using MySQL Connector/NET, or by examining the packet capture. Can you agree that "mysql --column-type-info" shows that a BIT(1) result is being sent with three unique combinations of Flags and Value? Flags - Value 1. UNSIGNED - 0x01 2. UNSIGNED BINARY - 0x31 3. UNSIGNED BINARY - 0x01
[11 Oct 2019 12:09]
MySQL Verification Team
Hello Mr. Grainger, I have repeated your results. I must admit that returned values and types are not consistent. Verified as reported.
[15 Oct 2019 12:21]
MySQL Verification Team
Behaviour on 5.7 is identical as with 8.0.
[5 Nov 2019 13:41]
MySQL Verification Team
Bug #97486 is a duplicate of this one.
Description: When a BIT(n) column is selected as part of a CASE WHEN ... END expression, 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. How to repeat: Create and populate a table with the following SQL: CREATE TABLE query_bit(name TEXT, value BIT(1)); INSERT INTO query_bit VALUES('a', 1); Execute the following three queries: SELECT value FROM query_bit; SELECT MAX(value) FROM query_bit; SELECT CASE WHEN name IS NOT NULL THEN value ELSE NULL END AS value FROM query_bit; I will attach a packet capture if possible. The first query sends FIELD_TYPE_BIT, length 1, UNSIGNED_FLAG with data 0x01. BINARY_FLAG is not specified and data is sent as binary. The second query sends FIELD_TYPE_BIT, length 1, UNSIGNED_FLAG|BINARY_FLAG with data 0x31 (i.e., '1'). The BINARY_FLAG is specified and data is sent as text. The third query sends FIELD_TYPE_BIT, length 1, UNSIGNED_FLAG|BINARY_FLAG with data 0x01. BINARY_FLAG is specified but the data is (unexpectedly) sent as binary. You can also demonstrate this problem by executing the three queries in MySQL Workbench. The first two are correctly read as 1 (even though one is transmitted as binary and one as text), but the third is read as 0, because deserializing the data fails. Suggested fix: MySQL Server should either keep sending the data as binary and clear the BINARY_FLAG, or keep setting BINARY_FLAG and start sending the data as text.