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