enterprise-support team mailing list archive

[Ovais Tariq <1621593@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

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


mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.6.32-78.0-log |
+-----------------+
1 row in set (0.00 sec)

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)

** Affects: mysql-server
     Importance: Unknown
         Status: Unknown

** Affects: percona-server
     Importance: Undecided
         Status: New

** Bug watch added: MySQL Bug System #82912
   http://bugs.mysql.com/bug.php?id=82912

** Also affects: mysql-server via
   http://bugs.mysql.com/bug.php?id=82912
   Importance: Unknown
       Status: Unknown

-- 
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1621593

Title:
  COUNT DISTINCT returns wrong result when tmp files are used by MySQL

To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1621593/+subscriptions