summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/count_distinct.result
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/count_distinct.result
parentInitial commit. (diff)
downloadmariadb-3f619478f796eddbba6e39502fe941b285dd97b1.tar.xz
mariadb-3f619478f796eddbba6e39502fe941b285dd97b1.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/count_distinct.result')
-rw-r--r--mysql-test/main/count_distinct.result145
1 files changed, 145 insertions, 0 deletions
diff --git a/mysql-test/main/count_distinct.result b/mysql-test/main/count_distinct.result
new file mode 100644
index 00000000..037674aa
--- /dev/null
+++ b/mysql-test/main/count_distinct.result
@@ -0,0 +1,145 @@
+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
+#