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; isbn city libname a 007 Berkeley Berkeley Public1 1 007 Berkeley Berkeley Public2 1 000 New York New York Public Libra 6 001 New York NYC Lib 1 006 San Fran San Fransisco Public 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 t1.libname) > 1; isbn city libname a 007 Berkeley Berkeley Public1 2 000 New York New York Public Libra 2 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; isbn city libname a 007 Berkeley Berkeley Public1 2 000 New York New York Public Libra 2 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; isbn city @bar:=t1.libname a 007 Berkeley Berkeley Public1 2 000 New York New York Public Libra 2 SELECT @bar; @bar Berkeley Public2 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; isbn city concat(@bar:=t1.libname) a 007 Berkeley Berkeley Public1 2 000 New York New York Public Libra 2 SELECT @bar; @bar Berkeley Public2 drop table t1, t2, t3; 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; f1 count(distinct t2.f2) count(distinct 1,NULL) 1 0 0 drop table t1,t2; create table t1 (f int); select count(distinct f) from t1; count(distinct f) 0 drop table t1; 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; count(distinct a) 1 1 drop table t1; create table t1 (f1 int, f2 int); insert into t1 values (0,1),(1,2); select count(distinct if(f1,3,f2)) from t1; count(distinct if(f1,3,f2)) 2 drop table t1; create table t1 (i int); insert into t1 values (0), (1); create view v1 as select * from t1; select count(distinct i) from v1; count(distinct i) 2 drop table t1; drop view v1; 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; count(distinct user_id) 17 alter table t1 modify user_id char(128) character set utf8; select count(distinct user_id) from t1; count(distinct user_id) 17 drop table t1; set @@tmp_table_size = default; 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; count(distinct a) 10 drop table t1; set @@tmp_table_size = default; # # End of 5.5 tests # # # MDEV-26452 SIGSEGV in Item::cleanup from Item::cleanup_processor # create table t (a int,b date,primary key(a,b)); select b,count(distinct a) from t group by b having b is null; b count(distinct a) drop table t; # # End of 10.7 tests #