Bug #121671 BIT value changes from raw byte 0x28 to decimal text 0x3430 when inner UNION ALL becomes UNION
Description:
MySQL Community Server 9.7.1 changes a BIT value's binary representation when only the inner `UNION ALL` is replaced with `UNION` in a nested set expression combining DECIMAL, BIT, and BLOB branches.
The reduced case contains one BIT value, `b'00101000'` (numeric value 40). The other two branches return no rows. Query A returns the single raw byte `0x28`; Query B returns the two bytes `0x3430`, the ASCII text `40`. Both results have the same binary BLOB protocol type. There are no duplicate input rows to remove.
The different bytes also persist when the results are stored using `CREATE TABLE ... AS SELECT`, so the observation does not depend on client formatting.
Actual direct results, each containing one row:
| Query | Returned bytes | Byte length | Interpretation |
| --- | --- | --- | --- |
| A | `0x28` | 1 | Original BIT byte; ASCII `(` |
| B | `0x3430` | 2 | Decimal text `40` |
PyMySQL returns `b'('` for A and `b'40'` for B. Each direct query was executed three times with the same result.
How to repeat:
CREATE TABLE bit_values (b BIT(8));
INSERT INTO bit_values VALUES (b'00101000');
CREATE TABLE blob_values (b BLOB);
-- A: adjacent UNION ALL operations.
(SELECT CAST(1 AS DECIMAL) AS x WHERE FALSE
UNION ALL SELECT b FROM bit_values)
UNION ALL SELECT b FROM blob_values WHERE FALSE;
-- B: change only the inner UNION ALL to UNION.
(SELECT CAST(1 AS DECIMAL) AS x WHERE FALSE
UNION SELECT b FROM bit_values)
UNION ALL SELECT b FROM blob_values WHERE FALSE;
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: MySQL Community Server 9.7.1 changes a BIT value's binary representation when only the inner `UNION ALL` is replaced with `UNION` in a nested set expression combining DECIMAL, BIT, and BLOB branches. The reduced case contains one BIT value, `b'00101000'` (numeric value 40). The other two branches return no rows. Query A returns the single raw byte `0x28`; Query B returns the two bytes `0x3430`, the ASCII text `40`. Both results have the same binary BLOB protocol type. There are no duplicate input rows to remove. The different bytes also persist when the results are stored using `CREATE TABLE ... AS SELECT`, so the observation does not depend on client formatting. Actual direct results, each containing one row: | Query | Returned bytes | Byte length | Interpretation | | --- | --- | --- | --- | | A | `0x28` | 1 | Original BIT byte; ASCII `(` | | B | `0x3430` | 2 | Decimal text `40` | PyMySQL returns `b'('` for A and `b'40'` for B. Each direct query was executed three times with the same result. How to repeat: CREATE TABLE bit_values (b BIT(8)); INSERT INTO bit_values VALUES (b'00101000'); CREATE TABLE blob_values (b BLOB); -- A: adjacent UNION ALL operations. (SELECT CAST(1 AS DECIMAL) AS x WHERE FALSE UNION ALL SELECT b FROM bit_values) UNION ALL SELECT b FROM blob_values WHERE FALSE; -- B: change only the inner UNION ALL to UNION. (SELECT CAST(1 AS DECIMAL) AS x WHERE FALSE UNION SELECT b FROM bit_values) UNION ALL SELECT b FROM blob_values WHERE FALSE;