1#
2# Various tests for SUM(DISTINCT ...)
3#
4
5--source include/big_test.inc
6--source include/have_innodb.inc
7--source include/have_sequence.inc
8
9set @save_tmp_table_size=@@tmp_table_size;
10set @save_max_heap_table_size=@@max_heap_table_size;
11
12#
13# Test the case when distinct values doesn't fit in memory and
14# filesort is used (see uniques.cc:merge_walk)
15#
16
17set storage_engine=MYISAM;
18
19CREATE TABLE t1 (id INTEGER);
20CREATE TABLE t2 (id INTEGER);
21
22INSERT INTO t1 SELECT b.seq FROM seq_1_to_128,seq_1_to_1024 b;
23
24# Just test that AVG(DISTINCT) is there
25SELECT AVG(DISTINCT id) FROM t1 GROUP BY id % 13;
26SELECT SUM(DISTINCT id)/COUNT(DISTINCT id) FROM t1 GROUP BY id % 13;
27
28INSERT INTO t1 SELECT b.seq FROM seq_1_to_128,seq_1025_to_16384 b;
29INSERT INTO t2 SELECT b.seq FROM seq_1_to_128 a,seq_1_to_16384 b
30ORDER by (a.seq*0+b.seq)*rand();
31
32# SELECT '++++++++++++++++++++++++++++++++++++++++++++++++++';
33
34SELECT SUM(DISTINCT id) sm FROM t1;
35SELECT SUM(DISTINCT id) sm FROM t2;
36SELECT SUM(DISTINCT id) sm FROM t1 group by id % 13;
37
38# this limit for max_heap_table_size is set to force testing the case, when
39# all distinct sum values can not fit in memory and must be stored in a
40# temporary table
41
42SET max_heap_table_size=16384;
43
44# to check that max_heap_table_size was actually set (hard limit for minimum
45# max_heap_table_size is set in mysqld.cc):
46
47SHOW variables LIKE 'max_heap_table_size';
48
49SELECT SUM(DISTINCT id) sm FROM t1;
50SELECT SUM(DISTINCT id) sm FROM t2;
51SELECT SUM(DISTINCT id) sm FROM t1 GROUP BY id % 13;
52
53--echo #
54--echo # Bug mdev-4063: SUM(DISTINCT...) with small'max_heap_table_size
55--echo # (bug #56927)
56--echo #
57
58SET max_heap_table_size=default;
59
60INSERT INTO t1 SELECT b.seq FROM seq_1_to_128,seq_16385_to_32768 b;
61TRUNCATE t2;
62INSERT INTO t2 SELECT b.seq FROM seq_1_to_128 a,seq_1_to_32768 b
63ORDER BY (a.seq*0+b.seq)*rand();
64
65SELECT SUM(DISTINCT id) sm FROM t2;
66
67SET max_heap_table_size=16384;
68
69SELECT SUM(DISTINCT id) sm FROM t2;
70
71DROP TABLE t1;
72DROP TABLE t2;
73
74SET @@tmp_table_size=@save_tmp_table_size;
75SET @@max_heap_table_size=@save_max_heap_table_size;
76
77--echo #
78--echo # MDEV-4311: COUNT(DISTINCT...) requiring a file for UNIQUE (bug #68749)
79--echo #
80CREATE TABLE t2 (id INTEGER) ENGINE=InnoDB;
81
82BEGIN;
83INSERT INTO t2 SELECT b.seq FROM seq_1_to_128 a, seq_1_to_16384 b
84ORDER BY b.seq*rand();
85INSERT INTO t2 VALUE(NULL);
86
87--echo # With default tmp_table_size / max_heap_table_size
88SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
89
90set @@tmp_table_size=1024*256;
91
92--echo # With reduced tmp_table_size
93SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
94
95set @@tmp_table_size=@save_tmp_table_size;
96SET @@max_heap_table_size=1024*256;
97
98--echo # With reduced max_heap_table_size
99SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
100
101SET @@max_heap_table_size=@save_max_heap_table_size;
102
103--echo # Back to default tmp_table_size / max_heap_table_size
104SELECT SQL_NO_CACHE count(DISTINCT id) sm FROM t2;
105COMMIT;
106
107DROP TABLE t2;
108