Bug #82912 COUNT DISTINCT returns wrong result when tmp files are used by MySQL

Description:
If a COUNT DISTINCT query is run and MySQL chooses to create a temporary file to fulfill the query then incorrect results are returned.

How to repeat:
mysql> create database distinct_bug;
Query OK, 1 row affected (0.00 sec)

mysql> use distinct_bug;
Database changed
mysql> CREATE TABLE `test_table` (`test` varchar(128) CHARACTER SET utf8mb4 DEFAULT NULL, PRIMARY KEY (`test`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test_table (test) value ('foo1'), ('foo2'), ('foo3'), ('foo4');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test_table;
+------+
| test |
+------+
| foo1 |
| foo2 |
| foo3 |
| foo4 |
+------+
4 rows in set (0.00 sec)

mysql> set tmp_table_size=1024000;
Query OK, 0 rows affected (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(distinct test) from test_table;
+----------------------+
| count(distinct test) |
+----------------------+
|                    4 |
+----------------------+
1 row in set (0.00 sec)

mysql> show status like 'created%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> set tmp_table_size=1024;
Query OK, 0 rows affected (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(distinct test) from test_table;
+----------------------+
| count(distinct test) |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)

mysql> show status like 'created%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 1     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

Suggested fix:
Return the correct results for COUNT DISTINCT query

Results

Attachment: bug#82912.txt (text/plain), 5.30 KiB.

Thank you for the bug report. Only 5.6 affected.

Posted by developer:
 
Fixed in 5.7.29