Description:
Applying COALESCE() to a BIT column produces a result that is displayed differently from the same expression after it is materialized using CREATE TABLE ... AS SELECT.
The direct expression is displayed as 0x31, whereas the materialized column is displayed as 1.
This may indicate inconsistent result type metadata or type inference between direct expression evaluation and CREATE TABLE ... AS SELECT.
Because 0x31 is the byte representation of the character 1, this report includes HEX(), numeric conversion, character-set information, generated table definitions, and client result metadata to distinguish a display-only difference from an actual value conversion.
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE t0 (
c0 BIT
);
INSERT INTO t0 (c0) VALUES (1);
CREATE TABLE t1 AS (
SELECT COALESCE(c0) AS c0
FROM t0
);
-- Direct expression result
SELECT COALESCE(c0) AS result_from_t0
FROM t0;
-- Materialized expression result
SELECT c0 AS result_from_t1
FROM t1;
Actual result
The direct expression is displayed as a hexadecimal binary value:
+--------------------------------+
| result_from_t0 |
+--------------------------------+
| 0x31 |
+--------------------------------+
The materialized result is displayed differently:
+----------------+
| result_from_t1 |
+----------------+
| 1 |
+----------------+
Description: Applying COALESCE() to a BIT column produces a result that is displayed differently from the same expression after it is materialized using CREATE TABLE ... AS SELECT. The direct expression is displayed as 0x31, whereas the materialized column is displayed as 1. This may indicate inconsistent result type metadata or type inference between direct expression evaluation and CREATE TABLE ... AS SELECT. Because 0x31 is the byte representation of the character 1, this report includes HEX(), numeric conversion, character-set information, generated table definitions, and client result metadata to distinguish a display-only difference from an actual value conversion. How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE t0 ( c0 BIT ); INSERT INTO t0 (c0) VALUES (1); CREATE TABLE t1 AS ( SELECT COALESCE(c0) AS c0 FROM t0 ); -- Direct expression result SELECT COALESCE(c0) AS result_from_t0 FROM t0; -- Materialized expression result SELECT c0 AS result_from_t1 FROM t1; Actual result The direct expression is displayed as a hexadecimal binary value: +--------------------------------+ | result_from_t0 | +--------------------------------+ | 0x31 | +--------------------------------+ The materialized result is displayed differently: +----------------+ | result_from_t1 | +----------------+ | 1 | +----------------+