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