196
196
drop table t1,t2,t3;
199
# Test using DISTINCT on a function that contains a group function
200
# This also test the case when one doesn't use all fields in GROUP BY.
203
create table t1 (a int not null, b int not null, t time);
204
insert into t1 values (1,1,"00:06:15"),(1,2,"00:06:15"),(1,2,"00:30:15"),(1,3,"00:06:15"),(1,3,"00:30:15");
205
select a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b;
206
select distinct a,sec_to_time(sum(time_to_sec(t))) from t1 group by a,b;
207
create table t2 (a int not null primary key, b int);
208
insert into t2 values (1,1),(2,2),(3,3);
209
select t1.a,sec_to_time(sum(time_to_sec(t))) from t1 left join t2 on (t1.b=t2.a) group by t1.a,t2.b;
210
select distinct t1.a,sec_to_time(sum(time_to_sec(t))) from t1 left join t2 on (t1.b=t2.a) group by t1.a,t2.b;
214
199
# Test problem with DISTINCT and HAVING
216
201
create table t1 (a int not null,b char(5), c text);