Create 'main' test directory and move 't' and 'r' there

1 files changed, 107 insertions, 0 deletions

diff --git a/mysql-test/main/sum_distinct.test b/mysql-test/main/sum_distinct.test
new file mode 100644
index 00000000000..633a72fddc8
--- /dev/null
+++ b/mysql-test/main/sum_distinct.test
@@ -0,0 +1,107 @@
+#
+# 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 #777654: empty subselect in FROM clause returning  
+--echo #              SUM(DISTINCT) over non-nullable field 
+--echo #
+
+CREATE TABLE t1 (a int NOT NULL) ;
+
+SELECT SUM(DISTINCT a) FROM t1;
+SELECT * FROM (SELECT SUM(DISTINCT a) FROM t1) AS t;
+
+DROP TABLE t1;