C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 -p --prompt="mysql 5.6 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.34 Source distribution PULL: 2016-SEP-07

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > drop database if exists distinct_bug;
Query OK, 1 row affected (0.28 sec)

mysql 5.6 > create database distinct_bug;
Query OK, 1 row affected (0.00 sec)

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

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

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

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

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

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

mysql 5.6 > 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 5.6 > set tmp_table_size=1024;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql 5.6 > 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)

========================================================================================
C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.16 Source distribution PULL: 2016-SEP-07

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > drop database if exists distinct_bug;
Query OK, 1 row affected (0.25 sec)

mysql 5.7 > create database distinct_bug;
Query OK, 1 row affected (0.00 sec)

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

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

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

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

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

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

mysql 5.7 > 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 5.7 > set tmp_table_size=1024;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql 5.7 > 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)