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
[22 Oct 2024 4:28] Shinya Sugiyama
It seems something wrong with "Using temporary".
The temporary workaround avoids using an internal temporary table.

```
mysql> SELECT a, b FROM t1 ORDER BY a;
+------------+------+
| a          | b    |
+------------+------+
| 0x00       |   23 |
| 0x01       |  293 |
+------------+------+
2 rows in set (0.00 sec)

mysql> explain SELECT a, b FROM t1 ORDER BY a;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT DISTINCT a, b FROM t1 ORDER BY a;
+------+------+
| a    | b    |
+------+------+
|    0 |   23 |
|    1 |  293 |
+------+------+
2 rows in set (0.00 sec)

mysql> explain SELECT DISTINCT a, b FROM t1 ORDER BY a;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> alter table t1 add index idx_t1_a_b(a,b);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain SELECT DISTINCT a, b FROM t1 ORDER BY a;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | idx_t1_a_b    | idx_t1_a_b | 7       | NULL |    2 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT DISTINCT a, b FROM t1 ORDER BY a;
+------------+------+
| a          | b    |
+------------+------+
| 0x00       |   23 |
| 0x01       |  293 |
+------------+------+
2 rows in set (0.00 sec)

```

Regards