diff options
| author | unknown <Sinisa@sinisa.nasamreza.org> | 2002-06-08 20:02:49 +0300 |
|---|---|---|
| committer | unknown <Sinisa@sinisa.nasamreza.org> | 2002-06-08 20:02:49 +0300 |
| commit | ec09d2e5a83eb52e2b5a51a3c390805a1e9f78fa (patch) | |
| tree | 0ca2fec8a7a9662cdf9598a9e94a46c6b1250bb8 /mysql-test | |
| parent | 15c99d52a2037d2a3f0b909dacd56aded6802c8b (diff) | |
| download | mariadb-git-ec09d2e5a83eb52e2b5a51a3c390805a1e9f78fa.tar.gz | |
A bug fix and test case for a bug that appears in quereis where there
is a GROUP BY a column that is not NOT NULL and ORDER BY is done by
another column.
Even better fix would be to make a separate function for this.
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/group_by.result | 23 | ||||
| -rw-r--r-- | mysql-test/t/group_by.test | 17 |
2 files changed, 40 insertions, 0 deletions
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index aaa03f2668a..d990760d39b 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -393,3 +393,26 @@ One Two sum(Four) 1 2 16 1 3 16 drop table if exists t1; +drop table if exists t1,t2; +create table t1 (id integer primary key not null auto_increment, gender char(1)); +insert into t1 values(NULL, 'M'); +insert into t1 values(NULL, 'F'); +insert into t1 values(NULL, 'F'); +insert into t1 values(NULL, 'F'); +insert into t1 values(NULL, 'M'); +create table t2 (user_id integer not null, timestamp datetime); +insert into t2 values (1, sysdate()); +insert into t2 values (2, sysdate()); +insert into t2 values (1, sysdate()); +insert into t2 values (3, sysdate()); +insert into t2 values (4, sysdate()); +insert into t2 values (4, sysdate()); +select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender; +gender dist_count percentage +F 3 60.00 +M 1 20.00 +select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender order by percentage; +gender dist_count percentage +M 1 20.00 +F 3 60.00 +drop table t1,t2; diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 171b5510227..09a2bee0d0c 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -299,3 +299,20 @@ insert into t1 values (1,3,3,4); insert into t1 values (1,3,4,4); select One, Two, sum(Four) from t1 group by One,Two; drop table if exists t1; +drop table if exists t1,t2; +create table t1 (id integer primary key not null auto_increment, gender char(1)); +insert into t1 values(NULL, 'M'); +insert into t1 values(NULL, 'F'); +insert into t1 values(NULL, 'F'); +insert into t1 values(NULL, 'F'); +insert into t1 values(NULL, 'M'); +create table t2 (user_id integer not null, timestamp datetime); +insert into t2 values (1, sysdate()); +insert into t2 values (2, sysdate()); +insert into t2 values (1, sysdate()); +insert into t2 values (3, sysdate()); +insert into t2 values (4, sysdate()); +insert into t2 values (4, sysdate()); +select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender; +select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender order by percentage; +drop table t1,t2; |