/usr/lib/mysql-testsuite/t/sum_distinct.test is in mysql-testsuite-5.6 5.6.16-1~exp1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
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 110 111 112 113 114 115 116 117 118 119 | #
# 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;
--echo
--echo BUG#37891: Column cannot be null error with aggregate in a subquery
--echo
CREATE TABLE t1 (
pk int(11) NOT NULL AUTO_INCREMENT,
int_key int(11) DEFAULT NULL,
PRIMARY KEY (pk),
KEY int_key (int_key)
);
INSERT INTO t1 VALUES (1,10);
CREATE TABLE t2 (
pk int(11) NOT NULL AUTO_INCREMENT,
time_nokey time DEFAULT NULL,
datetime_key time DEFAULT NULL,
PRIMARY KEY (pk),
KEY datetime_key (datetime_key)
);
INSERT INTO t2 VALUES (1,'18:19:29',NOW());
SELECT * FROM t1 WHERE int_key IN ( SELECT SUM(DISTINCT pk) FROM t2 WHERE time_nokey = datetime_key );
SELECT * FROM t1 WHERE int_key IN ( SELECT AVG(DISTINCT pk) FROM t2 WHERE time_nokey = datetime_key );
drop table t1,t2;
|