Bug #67503 having max(valus) can not return records which valus between {1, -1}

• Submitted: 7 Nov 2012 15:48
• Modified: 8 Nov 2012 0:56
• Reporter: rocky zheng
• Status: Closed
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 5.5.27
• OS: Linux (CentOS 5.7)
• CPU Architecture: Any
• Tags: having max function

[7 Nov 2012 15:48] rocky zheng

Description:
SELECT NAME,MAX(amount) FROM havingmax 
GROUP BY NAME
HAVING MAX(amount); 

if amount between [1, -1], can not return records,
 
 

How to repeat:
CREATE TABLE havingmax (
  amount  DECIMAL(19,2),
  NAME   CHAR(12)
)

INSERT INTO havingmax VALUES ( 0.01,  "rocky" );
INSERT INTO havingmax VALUES ( -0.01, "zheng" );
INSERT INTO havingmax VALUES ( 13.3,  "test" );
INSERT INTO havingmax VALUES ( 14,    "name" );
INSERT INTO havingmax VALUES ( 1.000001, "xx" );
INSERT INTO havingmax VALUES ( 0.00,    "bb" );
INSERT INTO havingmax VALUES ( -0.99,    "cc" );
INSERT INTO havingmax VALUES ( -1.0001,    "dd" );

#SQL 1
SELECT NAME,MAX(amount) FROM havingmax 
GROUP BY NAME
HAVING MAX(amount); 

#SQL 2
SELECT NAME,MAX(amount) FROM havingmax 
GROUP BY NAME

--------
run SQL 1  , only amount >=1 records selected. 
run SQL 2  , all records selected.

[7 Nov 2012 17:38] MySQL Verification Team

Please read: http://dev.mysql.com/doc/refman/5.5/en/group-by-hidden-columns.html
Thanks.