# # Various tests for SUM(DISTINCT ...) # --disable_warnings DROP TABLE IF EXISTS t1, t2; --enable_warnings CREATE TABLE t1 ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, gender CHAR(1), name VARCHAR(20) ); # According to ANSI SQL, SUM(DISTINCT ...) should return NULL for empty # record set SELECT SUM(DISTINCT LENGTH(name)) s1 FROM t1; # According to ANSI SQL, SUM(DISTINCT ...) should return NULL for records sets # entirely consisting of NULLs INSERT INTO t1 (gender, name) VALUES (NULL, NULL); INSERT INTO t1 (gender, name) VALUES (NULL, NULL); INSERT INTO t1 (gender, name) VALUES (NULL, NULL); SELECT SUM(DISTINCT LENGTH(name)) s1 FROM t1; # Filling table with t1 INSERT INTO t1 (gender, name) VALUES ('F', 'Helen'), ('F', 'Anastasia'), ('F', 'Katherine'), ('F', 'Margo'), ('F', 'Magdalene'), ('F', 'Mary'); CREATE TABLE t2 SELECT name FROM t1; SELECT (SELECT SUM(DISTINCT LENGTH(name)) FROM t1) FROM t2; DROP TABLE t2; INSERT INTO t1 (gender, name) VALUES ('F', 'Eva'), ('F', 'Sofia'), ('F', 'Sara'), ('F', 'Golda'), ('F', 'Toba'), ('F', 'Victory'), ('F', 'Faina'), ('F', 'Miriam'), ('F', 'Beki'), ('F', 'America'), ('F', 'Susan'), ('F', 'Glory'), ('F', 'Priscilla'), ('F', 'Rosmary'), ('F', 'Rose'), ('F', 'Margareth'), ('F', 'Elizabeth'), ('F', 'Meredith'), ('F', 'Julie'), ('F', 'Xenia'), ('F', 'Zena'), ('F', 'Olga'), ('F', 'Brunhilda'), ('F', 'Nataly'), ('F', 'Lara'), ('F', 'Svetlana'), ('F', 'Grethem'), ('F', 'Irene'); SELECT SUM(DISTINCT LENGTH(name)) s1, SUM(DISTINCT SUBSTRING(NAME, 1, 3)) s2, SUM(DISTINCT LENGTH(SUBSTRING(name, 1, 4))) s3 FROM t1; SELECT SUM(DISTINCT LENGTH(g1.name)) s1, SUM(DISTINCT SUBSTRING(g2.name, 1, 3)) s2, SUM(DISTINCT LENGTH(SUBSTRING(g3.name, 1, 4))) s3 FROM t1 g1, t1 g2, t1 g3; SELECT SUM(DISTINCT LENGTH(g1.name)) s1, SUM(DISTINCT SUBSTRING(g2.name, 1, 3)) s2, SUM(DISTINCT LENGTH(SUBSTRING(g3.name, 1, 4))) s3 FROM t1 g1, t1 g2, t1 g3 GROUP BY LENGTH(SUBSTRING(g3.name, 5, 10)); # here we explicitly request summing through temporary table (so # Item_sum_sum_distinct::copy_or_same() is called) SELECT SQL_BUFFER_RESULT SUM(DISTINCT LENGTH(name)) s1, SUM(DISTINCT SUBSTRING(NAME, 1, 3)) s2, SUM(DISTINCT LENGTH(SUBSTRING(name, 1, 4))) s3 FROM t1; SELECT SQL_BUFFER_RESULT SUM(DISTINCT LENGTH(g1.name)) s1, SUM(DISTINCT SUBSTRING(g2.name, 1, 3)) s2, SUM(DISTINCT LENGTH(SUBSTRING(g3.name, 1, 4))) s3 FROM t1 g1, t1 g2, t1 g3 GROUP BY LENGTH(SUBSTRING(g3.name, 5, 10)); # this test demonstrates that strings are automatically converted to numbers # before summing SET @l=1; UPDATE t1 SET name=CONCAT(name, @l:=@l+1); SELECT SUM(DISTINCT RIGHT(name, 1)) FROM t1; # this is a test case for ordinary t1 SELECT SUM(DISTINCT id) FROM t1; SELECT SUM(DISTINCT id % 11) FROM t1; DROP TABLE t1; # # Test the case when distinct values doesn't fit in memory and # filesort is used (see uniques.cc:merge_walk) # CREATE TABLE t1 (id INTEGER); CREATE TABLE t2 (id INTEGER); INSERT INTO t1 (id) VALUES (1), (1), (1),(1); INSERT INTO t2 (id) SELECT id FROM t1; INSERT INTO t1 (id) SELECT id FROM t2; /* 8 */ INSERT INTO t1 (id) SELECT id FROM t2; /* 12 */ INSERT INTO t1 (id) SELECT id FROM t2; /* 16 */ INSERT INTO t1 (id) SELECT id FROM t2; /* 20 */ INSERT INTO t1 (id) SELECT id FROM t2; /* 24 */ DELETE FROM t2; INSERT INTO t2 (id) SELECT id+1 FROM t1; INSERT INTO t1 SELECT id FROM t2; DELETE FROM t2; INSERT INTO t2 (id) SELECT id+2 FROM t1; INSERT INTO t1 SELECT id FROM t2; DELETE FROM t2; INSERT INTO t2 (id) SELECT id+4 FROM t1; INSERT INTO t1 SELECT id FROM t2; DELETE FROM t2; INSERT INTO t2 (id) SELECT id+8 FROM t1; INSERT INTO t1 SELECT id FROM t2; DELETE FROM t2; INSERT INTO t2 (id) SELECT id+16 FROM t1; INSERT INTO t1 SELECT id FROM t2; DELETE FROM t2; INSERT INTO t2 (id) SELECT id+32 FROM t1; INSERT INTO t1 SELECT id FROM t2; DELETE FROM t2; INSERT INTO t2 (id) SELECT id+64 FROM t1; INSERT INTO t1 SELECT id FROM t2; DELETE FROM t2; INSERT INTO t2 (id) SELECT id+128 FROM t1; INSERT INTO t1 SELECT id FROM t2; DELETE FROM t2; INSERT INTO t2 (id) SELECT id+256 FROM t1; INSERT INTO t1 SELECT id FROM t2; DELETE FROM t2; INSERT INTO t2 (id) SELECT id+512 FROM t1; INSERT INTO t1 SELECT id FROM t2; DELETE FROM t2; INSERT INTO t2 (id) SELECT id+1024 FROM t1; INSERT INTO t1 SELECT id FROM t2; DELETE FROM t2; INSERT INTO t2 (id) SELECT id+2048 FROM t1; INSERT INTO t1 SELECT id FROM t2; DELETE FROM t2; INSERT INTO t2 (id) SELECT id+4096 FROM t1; INSERT INTO t1 SELECT id FROM t2; DELETE FROM t2; INSERT INTO t2 (id) SELECT id+8192 FROM t1; INSERT INTO t1 SELECT id FROM t2; DELETE FROM t2; #INSERT INTO t2 (id) SELECT id+16384 FROM t1; #INSERT INTO t1 SELECT id FROM t2; #DELETE FROM t2; #INSERT INTO t2 (id) SELECT id+32768 FROM t1; #INSERT INTO t1 SELECT id FROM t2; #DELETE FROM t2; #INSERT INTO t2 (id) SELECT id+65536 FROM t1; #INSERT INTO t1 SELECT id FROM t2; #DELETE FROM t2; INSERT INTO t2 SELECT id FROM t1 ORDER BY id*rand(); # SELECT '++++++++++++++++++++++++++++++++++++++++++++++++++'; SELECT SUM(DISTINCT id) sm FROM t1; SELECT SUM(DISTINCT id) sm FROM t2; SELECT SUM(DISTINCT id) sm FROM t1 group by id % 13; # this limit for max_heap_table_size is set to force testing the case, when # all distinct sum values can not fit in memory and must be stored in a # temporary table SET max_heap_table_size=16384; # to check that max_heap_table_size was actually set (hard limit for minimum # max_heap_table_size is set in mysqld.cc): SHOW variables LIKE 'max_heap_table_size'; SELECT SUM(DISTINCT id) sm FROM t1; SELECT SUM(DISTINCT id) sm FROM t2; SELECT SUM(DISTINCT id) sm FROM t1 GROUP BY id % 13; DROP TABLE t1; DROP TABLE t2;