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: | |
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
[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