1# 2# Problem with count(distinct) 3# 4 5--disable_warnings 6drop table if exists t1,t2,t3; 7--enable_warnings 8 9create table t1 (libname varchar(21) not null, city text, primary key (libname)); 10create table t2 (isbn varchar(21) not null, author text, title text, primary key (isbn)); 11create table t3 (isbn varchar(21) not null, libname varchar(21) not null, quantity int ,primary key (isbn,libname)); 12insert into t2 values ('001','Daffy','A duck''s life'); 13insert into t2 values ('002','Bugs','A rabbit\'s life'); 14insert into t2 values ('003','Cowboy','Life on the range'); 15insert into t2 values ('000','Anonymous','Wanna buy this book?'); 16insert into t2 values ('004','Best Seller','One Heckuva book'); 17insert into t2 values ('005','EveryoneBuys','This very book'); 18insert into t2 values ('006','San Fran','It is a san fran lifestyle'); 19insert into t2 values ('007','BerkAuthor','Cool.Berkley.the.book'); 20insert into t3 values('000','New York Public Libra','1'); 21insert into t3 values('001','New York Public Libra','2'); 22insert into t3 values('002','New York Public Libra','3'); 23insert into t3 values('003','New York Public Libra','4'); 24insert into t3 values('004','New York Public Libra','5'); 25insert into t3 values('005','New York Public Libra','6'); 26insert into t3 values('006','San Fransisco Public','5'); 27insert into t3 values('007','Berkeley Public1','3'); 28insert into t3 values('007','Berkeley Public2','3'); 29insert into t3 values('001','NYC Lib','8'); 30insert into t1 values ('New York Public Libra','New York'); 31insert into t1 values ('San Fransisco Public','San Fran'); 32insert into t1 values ('Berkeley Public1','Berkeley'); 33insert into t1 values ('Berkeley Public2','Berkeley'); 34insert into t1 values ('NYC Lib','New York'); 35select 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; 36select 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; 37select 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; 38 39select t2.isbn,city,@bar:=t1.libname,count(distinct t1.libname) as a 40 from t3 left join t1 on t3.libname=t1.libname left join t2 41 on t3.isbn=t2.isbn group by city having count(distinct 42 t1.libname) > 1; 43# 44# Wrong result, see bug#49872 45# 46SELECT @bar; 47 48select t2.isbn,city,concat(@bar:=t1.libname),count(distinct t1.libname) as a 49 from t3 left join t1 on t3.libname=t1.libname left join t2 50 on t3.isbn=t2.isbn group by city having count(distinct 51 t1.libname) > 1; 52# 53# Wrong result, see bug#49872 54# 55SELECT @bar; 56 57drop table t1, t2, t3; 58 59# 60# Problem with LEFT JOIN 61# 62 63create table t1 (f1 int); 64insert into t1 values (1); 65create table t2 (f1 int,f2 int); 66select t1.f1,count(distinct t2.f2),count(distinct 1,NULL) from t1 left join t2 on t1.f1=t2.f1 group by t1.f1; 67drop table t1,t2; 68 69 70# 71# Empty tables 72# 73create table t1 (f int); 74select count(distinct f) from t1; 75drop table t1; 76 77# End of 4.1 tests 78 79# 80# Bug #6515 81# 82 83create table t1 (a char(3), b char(20), primary key (a, b)); 84insert into t1 values ('ABW', 'Dutch'), ('ABW', 'English'); 85select count(distinct a) from t1 group by b; 86drop table t1; 87 88# 89# Bug #9593 "The combination of COUNT, DISTINCT and CONCAT 90# seems to lock the server" 91# Bug appears only on Windows system 92# 93 94create table t1 (f1 int, f2 int); 95insert into t1 values (0,1),(1,2); 96select count(distinct if(f1,3,f2)) from t1; 97drop table t1; 98 99# 100# Bug #51980 "mysqld service crashes with a simple COUNT(DISTINCT) query 101# over a view" 102# 103 104create table t1 (i int); 105insert into t1 values (0), (1); 106create view v1 as select * from t1; 107select count(distinct i) from v1; 108drop table t1; 109drop view v1; 110 111# 112# MDEV-12136 SELECT COUNT(DISTINCT) returns the wrong value when tmp_table_size is limited 113# 114create table t1 (user_id char(64) character set utf8); 115insert t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17); 116set @@tmp_table_size = 1024; 117select count(distinct user_id) from t1; 118alter table t1 modify user_id char(128) character set utf8; 119select count(distinct user_id) from t1; 120drop table t1; 121set @@tmp_table_size = default; 122 123# 124# MDEV-13457: Wrong result for aggregate function with distinct clause when the value for 125# tmp_table_size is small 126# 127 128create table t1 ( 129a VARCHAR(1020), 130b int 131); 132insert into t1 values 133( 0 , 1 ), 134( 1 , 2 ), 135( 2 , 3 ), 136( 3 , 4 ), 137( 4 , 5 ), 138( 5 , 6 ), 139( 6 , 7 ), 140( 7 , 8 ), 141( 8 , 9 ), 142( 9 , 10 ), 143( 0 , 11 ), 144( 1 , 12 ), 145( 2 , 13 ), 146( 3 , 14 ); 147set @@tmp_table_size=1024; 148select count(distinct a) from t1; 149drop table t1; 150set @@tmp_table_size = default; 151 152# 153# End of 5.5 tests 154# 155