More problems with HAVING expressions

From: p.b.van.den.berg@farm.rug.nl
To: bugs@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 ..

>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;

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

Although it is not too difficult to work around this problem,
it is ugly to have to repeat an elaborate summary expression when
an alias should suffice.

>Submitter-Id:
>Originator: paul b van den berg
>Organization: InterAction Database,
Department of Social Pharmacy, Pharmacoepidemiology and
Pharmacotherapeutics
University of Groningen, Netherlands

>MySQL support: [none* | licence | email support | extended email support ]
>Synopsis: More problems with HAVING expressions
>Severity: [ non-critical | serious* | critical ]
>Priority: [ low | medium | high ]
>Category: mysql-server
>Class: [ sw-bug* | doc-bug | change-request | support ]

The bug was discovered on a linux box running Debian Woody (3.0.1)
with a
Debian-patched version of 3.23.49 (patched by the Debian security
team?).
This bug-report was written on a notebook with the same Debian setup
(details
above), and also running Windows 98 SE with mysql-4.0.11-gamma-win.zip
binaries
from www.mysql.com. The bug is reconfirmed with mysql-2.23.56-win.zip.

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14012@lists.mysql.com
To unsubscribe, e-mail

Re: More problems with HAVING expressions

Hello,

P.B.van.den.Berg wrote:
> From: p.b.van.den.berg@farm.rug.nl
> To: bugs@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

--
MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14018@lists.mysql.com
To unsubscribe, e-mail

Re: More problems with HAVING expressions

P.B.van.den.Berg writes:
> From: p.b.van.den.berg@farm.rug.nl
> To: bugs@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 ..
>
> >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;
>
> #query2, 2 resultlines, correct:
> select id, sum(qty) as sqty from t
> group by id having sum(qty)>2 and count(qty)>1;
>
> Although it is not too difficult to work around this problem,
> it is ugly to have to repeat an elaborate summary expression when
> an alias should suffice.
>

Hi!

Thank you for your bug report, which helped us fix a bug.

This is a patch:


===== sql/item_func.cc 1.90 vs edited =====
*** /tmp/item_func.cc-1.90-27748 Wed Mar 19 17:46:31 2003
--- edited/sql/item_func.cc Sat Mar 22 21:38:53 2003
***************
*** 70,84 ****
{ // Print purify happy
for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
{
! if ((*arg)->fix_fields(thd,tables))
return 1; /* purecov: inspected */
! if ((*arg)->maybe_null)
maybe_null=1;
! if ((*arg)->binary)
binary=1;
! with_sum_func= with_sum_func || (*arg)->with_sum_func;
! used_tables_cache|=(*arg)->used_tables();
! const_item_cache&= (*arg)->const_item();
}
}
fix_length_and_dec();
--- 70,87 ----
{ // Print purify happy
for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
{
! Item *item=*arg;
! if (item->fix_fields(thd,tables))
return 1; /* purecov: inspected */
! if (item->type() == Item::REF_ITEM)
! item=*((Item_ref *)item)->ref;
! if (item->maybe_null)
maybe_null=1;
! if (item->binary)
binary=1;
! with_sum_func= with_sum_func || item->with_sum_func;
! used_tables_cache|=item->used_tables();
! const_item_cache&= item->const_item();
}
}
fix_length_and_dec();

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread14022@lists.mysql.com
To unsubscribe, e-mail