2001 Feb 26
Problem: HAVING clause malfunction
I get wrong results when using certain combinations of
filtering conditions in the HAVING clause. I'm calculating
the total number of nights (Nights) and the average number
of nights (AvgNights) that travelers stay in hotels. Below
are the descriptions of the relevant fields of the two main
tables. Also shown are the SQL queries along with the
result sets.
Case 1 shows the result set for no HAVING clause.
Case 2:
Filter for groups HAVING Nights >= 2 (a SUM); the query
results are as expected.
Case 3:
Add the condition AvgNights = 5 (an AVG)
HAVING Nights >= 2 AND
AvgNights = 5
What I expect to see is the result set shown in case _4_,
but what I get is shown in case 3 below, which, as you can
see, is identical with case 2. MySQL appears to be ignoring
the condition applied to the calculated floating point
field.
(I do recognize that the Night >= 2 is irrelevant to the
results of this query; this code is part of an automated
system that allows users to add multiple filter conditions,
but makes no attempt to check for such irrelevancies.)
Case 4:
Finally, when I remove the first condition and use
HAVING AvgNights = 5
the correct filtering now takes place.
The problem seems to involve a comparison to an AVG
aggregate field when combined with a comparison to either an
integer field or a SUM aggregate field. The comparison to
the AVG field is ignored.
In contrast, combining two comparisons to two integer
fields, or combining two comparisons to two decimal fields
(include two AVG aggregates), both work as expected.
Is this a (known) bug?
Is there a work-around?
Thank you,
Harvey Chinn
[EMAIL PROTECTED]
MySQL Server version: 3.23.32
describe transx [only the relevant fields shown]
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| tid | int(10) unsigned | | PRI | NULL | auto_increment |
| accountid | int(10) unsigned | YES | MUL | NULL | |
| invdate | date | YES | MUL | NULL | |
| traveler | varchar(50) | YES | MUL | NULL | |
+------------+------------------+------+-----+---------+----------------+
describe hotel [only the relevant fields shown]
+------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| tid | int(10) unsigned | | MUL | 0 | |
| indate | date | YES | MUL | NULL | |
| outdate | date | YES | MUL | NULL | |
+------------+------------------+------+-----+---------+-------+
Case 1
SELECT account.name Account, transx.traveler Traveler, count(*) Num,
SUM( to_days(hotel.outdate) - to_days(hotel.indate) ) Nights,
AVG( to_days(hotel.outdate) - to_days(hotel.indate) ) AvgNights
FROM hotel LEFT JOIN transx USING (tid)
LEFT JOIN account ON account.id = transx.accountid
WHERE transx.accountid = 1290
AND transx.invdate BETWEEN '2001-01-01' AND '2001-01-31'
GROUP BY Account, Traveler
ORDER BY Traveler
+-------------------+--------------------+-----+--------+-----------+
| Account | Traveler | Num | Nights | AvgNights |
+-------------------+--------------------+-----+--------+-----------+
| DEMO COUPON USAGE | ABRAHAMSON/JERRY | 1 | 2 | 2.0000 |
| DEMO COUPON USAGE | ANDERSON/KEVI | 2 | 2 | 1.0000 |
| DEMO COUPON USAGE | ANDERSON/ROB | 1 | 3 | 3.0000 |
| DEMO COUPON USAGE | BJORKLUND/GAY LYNN | 1 | 5 | 5.0000 |
| DEMO COUPON USAGE | CLEMENTE/RYAN | 1 | 1 | 1.0000 |
| DEMO COUPON USAGE | DOWNING/ALAN | 1 | 8 | 8.0000 |
| DEMO COUPON USAGE | HALL/WALID | 3 | 7 | 2.3333 |
| DEMO COUPON USAGE | LARSON/JUSTINA | 3 | 3 | 1.0000 |
| DEMO COUPON USAGE | MCDONOUGH/KAREN AN | 2 | 10 | 5.0000 |
| DEMO COUPON USAGE | OSINSKI/RYAN | 1 | 1 | 1.0000 |
| DEMO COUPON USAGE | ROCKOW/AARON | 1 | 1 | 1.0000 |
| DEMO COUPON USAGE | ROCKOW/RICHARD L | 4 | 22 | 5.5000 |
| DEMO COUPON USAGE | SCHAECHER/WENDY | 1 | 5 | 5.0000 |
| DEMO COUPON USAGE | SCHREFFLER/BRENDA | 1 | 1 | 1.0000 |
| DEMO COUPON USAGE | SMITH/ROB | 1 | 5 | 5.0000 |
| DEMO COUPON USAGE | TOBOLESKI/CHRISTOP | 1 | 8 | 8.0000 |
| DEMO COUPON USAGE | VAUGHN/BRENDA | 2 | 6 | 3.0000 |
+-------------------+--------------------+-----+--------+-----------+
17 rows in set (0.06 sec)
Case 2
SELECT [same as above]
GROUP BY Account, Traveler
HAVING Nights >= 2
ORDER BY Traveler
+-------------------+--------------------+-----+--------+-----------+
| Account | Traveler | Num | Nights | AvgNights |
+-------------------+--------------------+-----+--------+-----------+
| DEMO COUPON USAGE | ABRAHAMSON/JERRY | 1 | 2 | 2.0000 |
| DEMO COUPON USAGE | ANDERSON/KEVI | 2 | 2 | 1.0000 |
| DEMO COUPON USAGE | ANDERSON/ROB | 1 | 3 | 3.0000 |
| DEMO COUPON USAGE | BJORKLUND/GAY LYNN | 1 | 5 | 5.0000 |
| DEMO COUPON USAGE | DOWNING/ALAN | 1 | 8 | 8.0000 |
| DEMO COUPON USAGE | HALL/WALID | 3 | 7 | 2.3333 |
| DEMO COUPON USAGE | LARSON/JUSTINA | 3 | 3 | 1.0000 |
| DEMO COUPON USAGE | MCDONOUGH/KAREN AN | 2 | 10 | 5.0000 |
| DEMO COUPON USAGE | ROCKOW/RICHARD L | 4 | 22 | 5.5000 |
| DEMO COUPON USAGE | SCHAECHER/WENDY | 1 | 5 | 5.0000 |
| DEMO COUPON USAGE | SMITH/ROB | 1 | 5 | 5.0000 |
| DEMO COUPON USAGE | TOBOLESKI/CHRISTOP | 1 | 8 | 8.0000 |
| DEMO COUPON USAGE | VAUGHN/BRENDA | 2 | 6 | 3.0000 |
+-------------------+--------------------+-----+--------+-----------+
13 rows in set (0.07 sec)
Case 3
SELECT [same as above]
GROUP BY Account, Traveler
HAVING Nights >= 2 AND AvgNights = 5
ORDER BY Traveler
+-------------------+--------------------+-----+--------+-----------+
| Account | Traveler | Num | Nights | AvgNights |
+-------------------+--------------------+-----+--------+-----------+
| DEMO COUPON USAGE | ABRAHAMSON/JERRY | 1 | 2 | 2.0000 |
| DEMO COUPON USAGE | ANDERSON/KEVI | 2 | 2 | 1.0000 |
| DEMO COUPON USAGE | ANDERSON/ROB | 1 | 3 | 3.0000 |
| DEMO COUPON USAGE | BJORKLUND/GAY LYNN | 1 | 5 | 5.0000 |
| DEMO COUPON USAGE | DOWNING/ALAN | 1 | 8 | 8.0000 |
| DEMO COUPON USAGE | HALL/WALID | 3 | 7 | 2.3333 |
| DEMO COUPON USAGE | LARSON/JUSTINA | 3 | 3 | 1.0000 |
| DEMO COUPON USAGE | MCDONOUGH/KAREN AN | 2 | 10 | 5.0000 |
| DEMO COUPON USAGE | ROCKOW/RICHARD L | 4 | 22 | 5.5000 |
| DEMO COUPON USAGE | SCHAECHER/WENDY | 1 | 5 | 5.0000 |
| DEMO COUPON USAGE | SMITH/ROB | 1 | 5 | 5.0000 |
| DEMO COUPON USAGE | TOBOLESKI/CHRISTOP | 1 | 8 | 8.0000 |
| DEMO COUPON USAGE | VAUGHN/BRENDA | 2 | 6 | 3.0000 |
+-------------------+--------------------+-----+--------+-----------+
13 rows in set (0.07 sec)
Case 4
SELECT [same as above]
GROUP BY Account, Traveler
HAVING AvgNights = 5
ORDER BY Traveler
+-------------------+--------------------+-----+--------+-----------+
| Account | Traveler | Num | Nights | AvgNights |
+-------------------+--------------------+-----+--------+-----------+
| DEMO COUPON USAGE | BJORKLUND/GAY LYNN | 1 | 5 | 5.0000 |
| DEMO COUPON USAGE | MCDONOUGH/KAREN AN | 2 | 10 | 5.0000 |
| DEMO COUPON USAGE | SCHAECHER/WENDY | 1 | 5 | 5.0000 |
| DEMO COUPON USAGE | SMITH/ROB | 1 | 5 | 5.0000 |
+-------------------+--------------------+-----+--------+-----------+
---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
