1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
|
DROP TABLE IF EXISTS t1, t2;
CREATE TABLE t1 (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
gender CHAR(1),
name VARCHAR(20)
);
SELECT SUM(DISTINCT LENGTH(name)) s1 FROM t1;
s1
NULL
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;
s1
NULL
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;
(SELECT SUM(DISTINCT LENGTH(name)) FROM t1)
18
18
18
18
18
18
18
18
18
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;
s1 s2 s3
42 0 7
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;
s1 s2 s3
42 0 7
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));
s1 s2 s3
42 0 NULL
42 0 7
42 0 4
42 0 4
42 0 4
42 0 4
42 0 4
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;
s1 s2 s3
42 0 7
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));
s1 s2 s3
42 0 NULL
42 0 7
42 0 4
42 0 4
42 0 4
42 0 4
42 0 4
SET @l=1;
UPDATE t1 SET name=CONCAT(name, @l:=@l+1);
SELECT SUM(DISTINCT RIGHT(name, 1)) FROM t1;
SUM(DISTINCT RIGHT(name, 1))
45
SELECT SUM(DISTINCT id) FROM t1;
SUM(DISTINCT id)
703
SELECT SUM(DISTINCT id % 11) FROM t1;
SUM(DISTINCT id % 11)
55
DROP TABLE t1;
#
# Bug #777654: empty subselect in FROM clause returning
# SUM(DISTINCT) over non-nullable field
#
CREATE TABLE t1 (a int NOT NULL) ;
SELECT SUM(DISTINCT a) FROM t1;
SUM(DISTINCT a)
NULL
SELECT * FROM (SELECT SUM(DISTINCT a) FROM t1) AS t;
SUM(DISTINCT a)
NULL
DROP TABLE t1;
|
