Bug #113446 Query with DISTINCT and ORDER BY on BIT data type returns wrong result
Submitted: 18 Dec 2023 2:32 Modified: 18 Dec 2023 7:58
Reporter: Hope Lee (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.33 OS:Any
Assigned to: CPU Architecture:Any

[18 Dec 2023 2:32] Hope Lee
Description:
Query with DISTINCT + ORDER BY on BIT data type returns wrong result.

How to repeat:
CREATE TABLE t1 (a BIT(1), b INT, c INT);
INSERT INTO t1 VALUES (b'0', 23, 92), (b'1', 293, 23);

8.0.33-debug> SELECT a, b FROM t1 ORDER BY a;
+------------+------+
| a          | b    |
+------------+------+
| 0x00       |   23 |
| 0x01       |  293 |
+------------+------+

8.0.33-debug> SELECT a, SUM(b) FROM t1 GROUP BY a;
+------------+--------+
| a          | SUM(b) |
+------------+--------+
| 0x00       |     23 |
| 0x01       |    293 |
+------------+--------+

8.0.33-debug> SELECT a, SUM(b) FROM t1 GROUP BY a WITH ROLLUP;
+------------+--------+
| a          | SUM(b) |
+------------+--------+
| 0x00       |     23 |
| 0x01       |    293 |
| NULL       |    316 |
+------------+--------+

8.0.33-debug> SELECT DISTINCT a, b FROM t1 ORDER BY a;
+------+------+
| a    | b    |
+------+------+
|    0 |   23 |
|    1 |  293 |
+------+------+

Here, we can see the last query with DISTINCT and ORDER BY returns the wrong result on the field of BIT data type.
[18 Dec 2023 7:58] MySQL Verification Team
Hello Hope Lee,

Thank you for the report and feedback.

regards,
Umesh