fixes/optimizations for count(distinct)

more extensive testing of count(distinct)


sql/item_sum.cc:
  fixes/optimizations for count(distinct)

2 files changed, 114 insertions, 0 deletions

diff --git a/mysql-test/r/count_distinct2.result b/mysql-test/r/count_distinct2.result
new file mode 100644
index 00000000000..b2bcf11a37c
--- /dev/null
+++ b/mysql-test/r/count_distinct2.result
@@ -0,0 +1,72 @@
+n1
+1
+2
+NULL
+count(distinct n1)
+2
+n2
+11
+12
+13
+NULL
+count(distinct n2)
+3
+s
+one
+two
+NULL
+count(distinct s)
+2
+vs
+eleven
+twevle
+thirteen
+NULL
+count(distinct vs)
+3
+t
+eleven
+twelve
+foo
+bar
+NULL
+count(distinct t)
+4
+n1	n2
+1	11
+2	11
+2	12
+2	13
+NULL	13
+2	NULL
+count(distinct n1,n2)
+4
+n1	s
+1	one
+2	two
+NULL	two
+2	NULL
+count(distinct n1,s)
+2
+s	n1	vs
+one	1	eleven
+two	2	eleven
+two	2	twevle
+two	2	thirteen
+two	NULL	thirteen
+NULL	2	thirteen
+two	2	NULL
+count(distinct s,n1,vs)
+4
+s	t
+one	eleven
+two	eleven
+two	twelve
+two	foo
+two	bar
+NULL	bar
+two	NULL
+count(distinct s,t)
+5
+count(distinct n1)	count(distinct n2)
+2	3
diff --git a/mysql-test/t/count_distinct2.test b/mysql-test/t/count_distinct2.test
new file mode 100644
index 00000000000..cfdce77622c
--- /dev/null
+++ b/mysql-test/t/count_distinct2.test
@@ -0,0 +1,42 @@
+create table t1(n1 int, n2 int, s char(20), vs varchar(20), t text);
+insert into t1 values (1,11, 'one','eleven', 'eleven'),
+ (1,11, 'one','eleven', 'eleven'),
+ (2,11, 'two','eleven', 'eleven'),
+ (2,12, 'two','twevle', 'twelve'),
+ (2,13, 'two','thirteen', 'foo'),
+ (2,13, 'two','thirteen', 'foo'),
+ (2,13, 'two','thirteen', 'bar'),
+ (NULL,13, 'two','thirteen', 'bar'),
+ (2,NULL, 'two','thirteen', 'bar'),
+ (2,13, NULL,'thirteen', 'bar'),
+ (2,13, 'two',NULL, 'bar'),
+ (2,13, 'two','thirteen', NULL);
+
+select distinct n1 from t1;
+select count(distinct n1) from t1;
+
+select distinct n2 from t1;
+select count(distinct n2) from t1;
+
+select distinct s from t1;
+select count(distinct s) from t1;
+
+select distinct vs from t1;
+select count(distinct vs) from t1;
+
+select distinct t from t1;
+select count(distinct t) from t1;
+
+select distinct n1,n2 from t1;
+select count(distinct n1,n2) from t1;
+
+select distinct n1,s from t1;
+select count(distinct n1,s) from t1;
+
+select distinct s,n1,vs from t1;
+select count(distinct s,n1,vs) from t1;
+
+select distinct s,t from t1;
+select count(distinct s,t) from t1;
+
+select count(distinct n1), count(distinct n2) from t1;