summaryrefslogtreecommitdiff
path: root/mysql-test/r/count_distinct2.result
blob: b92665b5c56f64e5215026820d1f8c8d4eae3a0f (plain)
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
120
121
122
123
124
125
126
127
128
129
drop table if exists t1;
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;
n1
1
2
NULL
select count(distinct n1) from t1;
count(distinct n1)
2
select distinct n2 from t1;
n2
11
12
13
NULL
select count(distinct n2) from t1;
count(distinct n2)
3
select distinct s from t1;
s
one
two
NULL
select count(distinct s) from t1;
count(distinct s)
2
select distinct vs from t1;
vs
eleven
twevle
thirteen
NULL
select count(distinct vs) from t1;
count(distinct vs)
3
select distinct t from t1;
t
eleven
twelve
foo
bar
NULL
select count(distinct t) from t1;
count(distinct t)
4
select distinct n1,n2 from t1;
n1	n2
1	11
2	11
2	12
2	13
NULL	13
2	NULL
select count(distinct n1,n2) from t1;
count(distinct n1,n2)
4
select distinct n1,s from t1;
n1	s
1	one
2	two
NULL	two
2	NULL
select count(distinct n1,s) from t1;
count(distinct n1,s)
2
select distinct s,n1,vs from t1;
s	n1	vs
one	1	eleven
two	2	eleven
two	2	twevle
two	2	thirteen
two	NULL	thirteen
NULL	2	thirteen
two	2	NULL
select count(distinct s,n1,vs) from t1;
count(distinct s,n1,vs)
4
select distinct s,t from t1;
s	t
one	eleven
two	eleven
two	twelve
two	foo
two	bar
NULL	bar
two	NULL
select count(distinct s,t) from t1;
count(distinct s,t)
5
select count(distinct n1), count(distinct n2) from t1;
count(distinct n1)	count(distinct n2)
2	3
select count(distinct n2), n1 from t1 group by n1;
count(distinct n2)	n1
1	NULL
1	1
3	2
drop table t1;
create table t1 (n int default NULL);
flush status;
select count(distinct n) from t1;
count(distinct n)
5000
show status like 'Created_tmp_disk_tables';
Variable_name	Value
Created_tmp_disk_tables	0
drop table t1;
create table t1 (s text);
flush status;
select count(distinct s) from t1;
count(distinct s)
5000
show status like 'Created_tmp_disk_tables';
Variable_name	Value
Created_tmp_disk_tables	1
drop table t1;