1# 2# Various tests for SUM(DISTINCT ...) 3# 4--disable_warnings 5DROP TABLE IF EXISTS t1, t2; 6--enable_warnings 7 8CREATE TABLE t1 ( 9 id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, 10 gender CHAR(1), 11 name VARCHAR(20) 12); 13 14# According to ANSI SQL, SUM(DISTINCT ...) should return NULL for empty 15# record set 16 17SELECT SUM(DISTINCT LENGTH(name)) s1 FROM t1; 18 19# According to ANSI SQL, SUM(DISTINCT ...) should return NULL for records sets 20# entirely consisting of NULLs 21 22INSERT INTO t1 (gender, name) VALUES (NULL, NULL); 23INSERT INTO t1 (gender, name) VALUES (NULL, NULL); 24INSERT INTO t1 (gender, name) VALUES (NULL, NULL); 25 26SELECT SUM(DISTINCT LENGTH(name)) s1 FROM t1; 27 28 29# Filling table with t1 30 31INSERT INTO t1 (gender, name) VALUES ('F', 'Helen'), ('F', 'Anastasia'), 32('F', 'Katherine'), ('F', 'Margo'), ('F', 'Magdalene'), ('F', 'Mary'); 33 34CREATE TABLE t2 SELECT name FROM t1; 35 36SELECT (SELECT SUM(DISTINCT LENGTH(name)) FROM t1) FROM t2; 37 38DROP TABLE t2; 39 40INSERT INTO t1 (gender, name) VALUES ('F', 'Eva'), ('F', 'Sofia'), 41('F', 'Sara'), ('F', 'Golda'), ('F', 'Toba'), ('F', 'Victory'), 42('F', 'Faina'), ('F', 'Miriam'), ('F', 'Beki'), ('F', 'America'), 43('F', 'Susan'), ('F', 'Glory'), ('F', 'Priscilla'), ('F', 'Rosmary'), 44('F', 'Rose'), ('F', 'Margareth'), ('F', 'Elizabeth'), ('F', 'Meredith'), 45('F', 'Julie'), ('F', 'Xenia'), ('F', 'Zena'), ('F', 'Olga'), 46('F', 'Brunhilda'), ('F', 'Nataly'), ('F', 'Lara'), ('F', 'Svetlana'), 47('F', 'Grethem'), ('F', 'Irene'); 48 49SELECT 50 SUM(DISTINCT LENGTH(name)) s1, 51 SUM(DISTINCT SUBSTRING(NAME, 1, 3)) s2, 52 SUM(DISTINCT LENGTH(SUBSTRING(name, 1, 4))) s3 53FROM t1; 54 55SELECT 56 SUM(DISTINCT LENGTH(g1.name)) s1, 57 SUM(DISTINCT SUBSTRING(g2.name, 1, 3)) s2, 58 SUM(DISTINCT LENGTH(SUBSTRING(g3.name, 1, 4))) s3 59FROM t1 g1, t1 g2, t1 g3; 60 61SELECT 62 SUM(DISTINCT LENGTH(g1.name)) s1, 63 SUM(DISTINCT SUBSTRING(g2.name, 1, 3)) s2, 64 SUM(DISTINCT LENGTH(SUBSTRING(g3.name, 1, 4))) s3 65FROM t1 g1, t1 g2, t1 g3 GROUP BY LENGTH(SUBSTRING(g3.name, 5, 10)); 66 67# here we explicitly request summing through temporary table (so 68# Item_sum_sum_distinct::copy_or_same() is called) 69 70SELECT SQL_BUFFER_RESULT 71 SUM(DISTINCT LENGTH(name)) s1, 72 SUM(DISTINCT SUBSTRING(NAME, 1, 3)) s2, 73 SUM(DISTINCT LENGTH(SUBSTRING(name, 1, 4))) s3 74FROM t1; 75 76SELECT SQL_BUFFER_RESULT 77 SUM(DISTINCT LENGTH(g1.name)) s1, 78 SUM(DISTINCT SUBSTRING(g2.name, 1, 3)) s2, 79 SUM(DISTINCT LENGTH(SUBSTRING(g3.name, 1, 4))) s3 80FROM t1 g1, t1 g2, t1 g3 GROUP BY LENGTH(SUBSTRING(g3.name, 5, 10)); 81 82# this test demonstrates that strings are automatically converted to numbers 83# before summing 84 85SET @l=1; 86UPDATE t1 SET name=CONCAT(name, @l:=@l+1); 87 88SELECT SUM(DISTINCT RIGHT(name, 1)) FROM t1; 89 90# this is a test case for ordinary t1 91 92SELECT SUM(DISTINCT id) FROM t1; 93SELECT SUM(DISTINCT id % 11) FROM t1; 94 95DROP TABLE t1; 96 97--echo # 98--echo # Bug #777654: empty subselect in FROM clause returning 99--echo # SUM(DISTINCT) over non-nullable field 100--echo # 101 102CREATE TABLE t1 (a int NOT NULL) ; 103 104SELECT SUM(DISTINCT a) FROM t1; 105SELECT * FROM (SELECT SUM(DISTINCT a) FROM t1) AS t; 106 107DROP TABLE t1; 108