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