1 files changed, 180 insertions, 0 deletions
diff --git a/mysql-test/main/sum_distinct-big.result b/mysql-test/main/sum_distinct-big.result
new file mode 100644
index 00000000000..2d350826ac8
--- /dev/null
+++ b/mysql-test/main/sum_distinct-big.result
@@ -0,0 +1,180 @@
+DROP TABLE IF EXISTS t1, t2;
+set @save_tmp_table_size=@@tmp_table_size;
+set @save_max_heap_table_size=@@max_heap_table_size;
+set @save_storage_engine=@@storage_engine;
+set storage_engine=MYISAM;
+CREATE TABLE t1 (id INTEGER);
+CREATE TABLE t2 (id INTEGER);
+INSERT INTO t1 (id) VALUES (1), (1), (1),(1);
+INSERT INTO t1 (id) SELECT id FROM t1;
+/* 8 */
+INSERT INTO t1 (id) SELECT id FROM t1;
+/* 12 */
+INSERT INTO t1 (id) SELECT id FROM t1;
+/* 16 */
+INSERT INTO t1 (id) SELECT id FROM t1;
+/* 20 */
+INSERT INTO t1 (id) SELECT id FROM t1;
+/* 24 */
+INSERT INTO t1 SELECT id+1 FROM t1;
+INSERT INTO t1 SELECT id+2 FROM t1;
+INSERT INTO t1 SELECT id+4 FROM t1;
+INSERT INTO t1 SELECT id+8 FROM t1;
+INSERT INTO t1 SELECT id+16 FROM t1;
+INSERT INTO t1 SELECT id+32 FROM t1;
+INSERT INTO t1 SELECT id+64 FROM t1;
+INSERT INTO t1 SELECT id+128 FROM t1;
+INSERT INTO t1 SELECT id+256 FROM t1;
+INSERT INTO t1 SELECT id+512 FROM t1;
+SELECT AVG(DISTINCT id) FROM t1 GROUP BY id % 13;
+AVG(DISTINCT id)
+513.5000
+508.0000
+509.0000
+510.0000
+511.0000
+512.0000
+513.0000
+514.0000
+515.0000
+516.0000
+517.0000
+511.5000
+512.5000
+SELECT SUM(DISTINCT id)/COUNT(DISTINCT id) FROM t1 GROUP BY id % 13;
+SUM(DISTINCT id)/COUNT(DISTINCT id)
+513.5000
+508.0000
+509.0000
+510.0000
+511.0000
+512.0000
+513.0000
+514.0000
+515.0000
+516.0000
+517.0000
+511.5000
+512.5000
+INSERT INTO t1 SELECT id+1024 FROM t1;
+INSERT INTO t1 SELECT id+2048 FROM t1;
+INSERT INTO t1 SELECT id+4096 FROM t1;
+INSERT INTO t1 SELECT id+8192 FROM t1;
+INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand();
+SELECT SUM(DISTINCT id) sm FROM t1;
+sm
+134225920
+SELECT SUM(DISTINCT id) sm FROM t2;
+sm
+134225920
+SELECT SUM(DISTINCT id) sm FROM t1 group by id % 13;
+sm
+10327590
+10328851
+10330112
+10331373
+10332634
+10317510
+10318770
+10320030
+10321290
+10322550
+10323810
+10325070
+10326330
+SET max_heap_table_size=16384;
+SHOW variables LIKE 'max_heap_table_size';
+Variable_name	Value
+max_heap_table_size	16384
+SELECT SUM(DISTINCT id) sm FROM t1;
+sm
+134225920
+SELECT SUM(DISTINCT id) sm FROM t2;
+sm
+134225920
+SELECT SUM(DISTINCT id) sm FROM t1 GROUP BY id % 13;
+sm
+10327590
+10328851
+10330112
+10331373
+10332634
+10317510
+10318770
+10320030
+10321290
+10322550
+10323810
+10325070
+10326330
+# 
+# Bug mdev-4063: SUM(DISTINCT...) with small'max_heap_table_size 
+# (bug #56927)
+# 
+SET max_heap_table_size=default;
+INSERT INTO t1 SELECT id+16384 FROM t1;
+DELETE FROM t2;
+INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand();
+SELECT SUM(DISTINCT id) sm FROM t2;
+sm
+536887296
+SET max_heap_table_size=16384;
+SELECT SUM(DISTINCT id) sm FROM t2;
+sm
+536887296
+DROP TABLE t1;
+DROP TABLE t2;
+SET @@tmp_table_size=@save_tmp_table_size;
+SET @@max_heap_table_size=@save_max_heap_table_size;
+# 
+# Bug mdev-4311: COUNT(DISTINCT...) requiring a file for Unique 
+# (bug #68749)
+# 
+set @save_storage_engine=@@storage_engine;
+set storage_engine=INNODB;
+CREATE TABLE t1 (id INTEGER) ENGINE=InnoDB;
+CREATE TABLE t2 (id INTEGER) ENGINE=InnoDB;
+INSERT INTO t1 (id) VALUES (1), (1), (1),(1);
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 (id) SELECT id FROM t1;
+INSERT INTO t1 SELECT id+1 FROM t1;
+INSERT INTO t1 SELECT id+2 FROM t1;
+INSERT INTO t1 SELECT id+4 FROM t1;
+INSERT INTO t1 SELECT id+8 FROM t1;
+INSERT INTO t1 SELECT id+16 FROM t1;
+INSERT INTO t1 SELECT id+32 FROM t1;
+INSERT INTO t1 SELECT id+64 FROM t1;
+INSERT INTO t1 SELECT id+128 FROM t1;
+INSERT INTO t1 SELECT id+256 FROM t1;
+INSERT INTO t1 SELECT id+512 FROM t1;
+INSERT INTO t1 SELECT id+1024 FROM t1;
+INSERT INTO t1 SELECT id+2048 FROM t1;
+INSERT INTO t1 SELECT id+4096 FROM t1;
+INSERT INTO t1 SELECT id+8192 FROM t1;
+INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand();
+INSERT INTO t2 VALUE(NULL);
+# With default tmp_table_size / max_heap_table_size
+SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
+sm
+16384
+set @@tmp_table_size=1024*256;
+# With reduced tmp_table_size
+SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
+sm
+16384
+set @@tmp_table_size=@save_tmp_table_size;
+SET @@max_heap_table_size=1024*256;
+# With reduced max_heap_table_size
+SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
+sm
+16384
+SET @@max_heap_table_size=@save_max_heap_table_size;
+# Back to default tmp_table_size / max_heap_table_size
+SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
+sm
+16384
+DROP TABLE t1,t2;
+set storage_engine=@save_storage_engine;