~drizzle-trunk/drizzle/development

#

# 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 CROSS JOIN t1 g2 CROSS JOIN t1 g3;

SELECT

  SQL_BIG_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 CROSS JOIN t1 g2 CROSS JOIN 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_BIG_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 CROSS JOIN t1 g2 CROSS JOIN 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;