|
1
by brian
clean slate |
1 |
#
|
2 |
# Various tests for SUM(DISTINCT ...) |
|
3 |
#
|
|
4 |
--disable_warnings
|
|
5 |
DROP TABLE IF EXISTS t1, t2; |
|
6 |
--enable_warnings
|
|
7 |
||
8 |
CREATE 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 |
||
17 |
SELECT 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 |
||
22 |
INSERT INTO t1 (gender, name) VALUES (NULL, NULL); |
|
23 |
INSERT INTO t1 (gender, name) VALUES (NULL, NULL); |
|
24 |
INSERT INTO t1 (gender, name) VALUES (NULL, NULL); |
|
25 |
||
26 |
SELECT SUM(DISTINCT LENGTH(name)) s1 FROM t1; |
|
27 |
||
28 |
||
29 |
# Filling table with t1 |
|
30 |
||
31 |
INSERT INTO t1 (gender, name) VALUES ('F', 'Helen'), ('F', 'Anastasia'), |
|
32 |
('F', 'Katherine'), ('F', 'Margo'), ('F', 'Magdalene'), ('F', 'Mary'); |
|
33 |
||
34 |
CREATE TABLE t2 SELECT name FROM t1; |
|
35 |
||
36 |
SELECT (SELECT SUM(DISTINCT LENGTH(name)) FROM t1) FROM t2; |
|
37 |
||
38 |
DROP TABLE t2; |
|
39 |
||
40 |
INSERT 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 |
||
49 |
SELECT
|
|
50 |
SUM(DISTINCT LENGTH(name)) s1, |
|
51 |
SUM(DISTINCT SUBSTRING(NAME, 1, 3)) s2, |
|
52 |
SUM(DISTINCT LENGTH(SUBSTRING(name, 1, 4))) s3 |
|
53 |
FROM t1; |
|
54 |
||
55 |
SELECT
|
|
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 |
|
59 |
FROM t1 g1, t1 g2, t1 g3; |
|
60 |
||
61 |
SELECT
|
|
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 |
|
65 |
FROM 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 |
||
70 |
SELECT 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 |
|
74 |
FROM t1; |
|
75 |
||
76 |
SELECT 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 |
|
80 |
FROM 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 |
||
85 |
SET @l=1; |
|
86 |
UPDATE t1 SET name=CONCAT(name, @l:=@l+1); |
|
87 |
||
88 |
SELECT SUM(DISTINCT RIGHT(name, 1)) FROM t1; |
|
89 |
||
90 |
# this is a test case for ordinary t1 |
|
91 |
||
92 |
SELECT SUM(DISTINCT id) FROM t1; |
|
93 |
SELECT SUM(DISTINCT id % 11) FROM t1; |
|
94 |
||
95 |
DROP TABLE t1; |