Re: More problems with HAVING expressions - Alexander Keremidarski - com.mysql.lists.bugs

3 messages in com.mysql.lists.bugsRe: More problems with HAVING express...

From	Sent On	Attachments
P.B.van.den.Berg	21 Mar 2003 08:14
Alexander Keremidarski	21 Mar 2003 11:15
Sinisa Milivojevic	22 Mar 2003 11:46

Subject:	Re: More problems with HAVING expressions
From:	Alexander Keremidarski (sal...@mysql.com)
Date:	03/21/2003 11:15:54 AM
List:	com.mysql.lists.bugs

Hello,

P.B.van.den.Berg wrote:

From: p.b....@farm.rug.nl To: bu...@lists.mysql.com Subject: More problems with HAVING expressions

Description:

Not all problems that I mentioned in havexpr.tgz are not resolved with your bugfix. MySQL cannot mix expressions and aliases in having ..

I recieved your previous email, but as you can see this time responce is much slower. I wanted to check it first against SQL99, but still didn't :(

How-To-Repeat:

The following queries illustrate the bug illustrated in war_mpr.sql again:

#create and populate test table: create table t (id int not null, qty int not null); insert into t values (1,2),(1,3),(2,4),(2,5);

#query1, 0 resultlines, should be 2: select id, sum(qty) as sqty from t group by id having sqty>2 and count(qty)>1;

This returns Empty set

#query2, 2 resultlines, correct: select id, sum(qty) as sqty from t group by id having sum(qty)>2 and count(qty)>1;

+----+------+ | id | sqty | +----+------+ | 1 | 5 | | 2 | 9 | +----+------+

I am not quite sure if both of the above are allowed by SQL99. As far as I know they are not as SQL99 requires that in GROUP BY you can use only column and aliases presented in select part. MySQL extends this, but being non-standard behavour it is not that easy to define how result should be interpreted.

Two more examples.

mysql> select id, sum(qty) as sqty, count(qty) as cqty from t group by id having sqty>2 and cqty>1; +----+------+------+ | id | sqty | cqty | +----+------+------+ | 1 | 5 | 2 | | 2 | 9 | 2 | +----+------+------+

mysql> select id, sum(qty) as sqty, count(qty) as cqty from t group by id having sum(qty)>2 and cqty>1;

Empty set

Now this is what more looks like a bug as and it is exactly what you observed before. Mixing both Aliases and aggregate functions in GROUP BY clause leads to wrong result.

I used this example to enter it as http://bugs.mysql.com/176

Best regards