Bug #115893 Inconsistent Results When Using COALESCE

• Submitted: 22 Aug 10:11
• Modified: 22 Aug 12:05
• Reporter: Wenqian Deng
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[22 Aug 10:11] Wenqian Deng

Description:
There is an inconsistency in the results when using the COALESCE function.

How to repeat:
1. Set up table t0:

CREATE TABLE t0 (c0 BIT, c1 BIT);
INSERT INTO t0 VALUES (0, 1);

2. Use COALESCE in a SELECT query:

SELECT (COALESCE(t0.c0, t0.c1)) AS c0 FROM t0;
--Result: 0x30

3. Store the result of COALESCE in another table t1 and SELECT FROM t1:

CREATE TABLE t1 AS (SELECT (COALESCE(t0.c0, t0.c1)) AS c0 FROM t0);
SELECT * FROM t1;
--Result: 0

The result should be consistent regardless of whether the result is directly selected or stored in another table.

[22 Aug 12:05] MySQL Verification Team

Hi Mr. Deng,

Thank you for your bug report.

We have tried to simplify your test case as much as we could, but it is still returning a wrong result. 

This has to do with a bug in COALESCE when dealing with BIT data type.

This is now a fully verified bug , affecting latest releases of 8.0, 8.4 and 9.0.

Verified as reported.

Thank you.

[22 Aug 12:07] MySQL Verification Team

HI,

These are the results of our, simplified, tests with two variants:

mysql> DROP TABLE IF EXISTS t0, t1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql>
mysql>
mysql> CREATE TABLE t0 (c0 BIT, c1 BIT);
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql> INSERT INTO t0 VALUES (0, 1);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT COALESCE(t0.c0, t0.c1)  FROM t0;
+------------------------------------------------+
| COALESCE(t0.c0, t0.c1)                         |
+------------------------------------------------+
| 0x30                                           |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> CREATE TABLE t1 AS SELECT COALESCE(t0.c0, t0.c1)  FROM t0;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM t1;
+------------------------+
| COALESCE(t0.c0, t0.c1) |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> DROP TABLE IF EXISTS t0, t1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> CREATE TABLE t0 (c0 BIT, c1 BIT);
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO t0 VALUES (NULL, 1);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT COALESCE(t0.c0, t0.c1)  FROM t0;
+------------------------------------------------+
| COALESCE(t0.c0, t0.c1)                         |
+------------------------------------------------+
| 0x31                                           |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> CREATE TABLE t1 AS SELECT COALESCE(t0.c0, t0.c1)  FROM t0;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM t1;
+------------------------+
| COALESCE(t0.c0, t0.c1) |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

mysql>
mysql> DROP TABLE IF EXISTS t0, t1;
Query OK, 0 rows affected (0.01 sec)

As you can see, COALESCE does not work with BIT data type even when NULL is inserted.