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
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
#
# Problem with count(distinct)
#
--disable_warnings
drop table if exists t1,t2,t3;
--enable_warnings
create table t1 (libname varchar(21) not null, city text, primary key (libname));
create table t2 (isbn varchar(21) not null, author text, title text, primary key (isbn));
create table t3 (isbn varchar(21) not null, libname varchar(21) not null, quantity int ,primary key (isbn,libname));
insert into t2 values ('001','Daffy','A duck''s life');
insert into t2 values ('002','Bugs','A rabbit\'s life');
insert into t2 values ('003','Cowboy','Life on the range');
insert into t2 values ('000','Anonymous','Wanna buy this book?');
insert into t2 values ('004','Best Seller','One Heckuva book');
insert into t2 values ('005','EveryoneBuys','This very book');
insert into t2 values ('006','San Fran','It is a san fran lifestyle');
insert into t2 values ('007','BerkAuthor','Cool.Berkley.the.book');
insert into t3 values('000','New York Public Libra','1');
insert into t3 values('001','New York Public Libra','2');
insert into t3 values('002','New York Public Libra','3');
insert into t3 values('003','New York Public Libra','4');
insert into t3 values('004','New York Public Libra','5');
insert into t3 values('005','New York Public Libra','6');
insert into t3 values('006','San Fransisco Public','5');
insert into t3 values('007','Berkeley Public1','3');
insert into t3 values('007','Berkeley Public2','3');
insert into t3 values('001','NYC Lib','8');
insert into t1 values ('New York Public Libra','New York');
insert into t1 values ('San Fransisco Public','San Fran');
insert into t1 values ('Berkeley Public1','Berkeley');
insert into t1 values ('Berkeley Public2','Berkeley');
insert into t1 values ('NYC Lib','New York');
select t2.isbn,city,t1.libname,count(t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city,t1.libname;
select t2.isbn,city,t1.libname,count(distinct t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city having count(distinct t1.libname) > 1;
select t2.isbn,city,t1.libname,count(distinct t1.libname) as a from t3 left join t1 on t3.libname=t1.libname left join t2 on t3.isbn=t2.isbn group by city having count(distinct concat(t1.libname,'a')) > 1;
select t2.isbn,city,@bar:=t1.libname,count(distinct t1.libname) as a
from t3 left join t1 on t3.libname=t1.libname left join t2
on t3.isbn=t2.isbn group by city having count(distinct
t1.libname) > 1;
#
# Wrong result, see bug#49872
#
SELECT @bar;
select t2.isbn,city,concat(@bar:=t1.libname),count(distinct t1.libname) as a
from t3 left join t1 on t3.libname=t1.libname left join t2
on t3.isbn=t2.isbn group by city having count(distinct
t1.libname) > 1;
#
# Wrong result, see bug#49872
#
SELECT @bar;
drop table t1, t2, t3;
#
# Problem with LEFT JOIN
#
create table t1 (f1 int);
insert into t1 values (1);
create table t2 (f1 int,f2 int);
select t1.f1,count(distinct t2.f2),count(distinct 1,NULL) from t1 left join t2 on t1.f1=t2.f1 group by t1.f1;
drop table t1,t2;
#
# Empty tables
#
create table t1 (f int);
select count(distinct f) from t1;
drop table t1;
# End of 4.1 tests
#
# Bug #6515
#
create table t1 (a char(3), b char(20), primary key (a, b));
insert into t1 values ('ABW', 'Dutch'), ('ABW', 'English');
select count(distinct a) from t1 group by b;
drop table t1;
#
# Bug #9593 "The combination of COUNT, DISTINCT and CONCAT
# seems to lock the server"
# Bug appears only on Windows system
#
create table t1 (f1 int, f2 int);
insert into t1 values (0,1),(1,2);
select count(distinct if(f1,3,f2)) from t1;
drop table t1;
#
# Bug #51980 "mysqld service crashes with a simple COUNT(DISTINCT) query
# over a view"
#
create table t1 (i int);
insert into t1 values (0), (1);
create view v1 as select * from t1;
select count(distinct i) from v1;
drop table t1;
drop view v1;
#
# MDEV-12136 SELECT COUNT(DISTINCT) returns the wrong value when tmp_table_size is limited
#
create table t1 (user_id char(64) character set utf8);
insert t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17);
set @@tmp_table_size = 1024;
select count(distinct user_id) from t1;
alter table t1 modify user_id char(128) character set utf8;
select count(distinct user_id) from t1;
drop table t1;
set @@tmp_table_size = default;
#
# MDEV-13457: Wrong result for aggregate function with distinct clause when the value for
# tmp_table_size is small
#
create table t1 (
a VARCHAR(1020),
b int
);
insert into t1 values
( 0 , 1 ),
( 1 , 2 ),
( 2 , 3 ),
( 3 , 4 ),
( 4 , 5 ),
( 5 , 6 ),
( 6 , 7 ),
( 7 , 8 ),
( 8 , 9 ),
( 9 , 10 ),
( 0 , 11 ),
( 1 , 12 ),
( 2 , 13 ),
( 3 , 14 );
set @@tmp_table_size=1024;
select count(distinct a) from t1;
drop table t1;
set @@tmp_table_size = default;
#
# End of 5.5 tests
#
|
