Bug #636 calcuations on aggregate functions results in NULLs

• Submitted: 11 Jun 2003 8:33
• Modified: 11 Jun 2003 10:43
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 3.23.51-nt
• OS: Windows (Windows 2000)
• CPU Architecture: Any

[11 Jun 2003 8:33] [ name withheld ]

Description:
A query with a self-join and a group by statement is used to calculate ranks.  It works correctly.  Howver if you add a calculation to the count like "count(*)+1" instead of just "count(*)" some fields (not the counted fields) are returned NULL.

How to repeat:
CREATE TABLE silly (
  id mediumint(9) NOT NULL auto_increment,
  number int(11) default NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY id (id)
) TYPE=MyISAM;

INSERT INTO silly VALUES (1,10),(2,20),(3,100),(4,7),
(5,3),(6,20),(7,10),(8,10);

Now run the following query:

select s1.id, s1.number, count(distinct s2.number) as rank
 from silly s1 left join silly s2
 on s1.number > s2.number
 group by s1.id, s1.number
 order by rank;

As desired it returns:

        id         number           rank
         5              3              0
         4              7              1
         1             10              2
         7             10              2
         8             10              2
         2             20              3
         6             20              3
         3            100              4

Now change the query by adding any calcuation to the rank field:

select s1.id, s1.number, count(distinct s2.number) + 0 as rank
 from silly s1 left join silly s2
 on s1.number > s2.number
 group by s1.id, s1.number
 order by rank

Now it returns NULL's for some of the values in the number column:

        id         number           rank
         5              3              0
         4             NULL            1
         1             10              2
         7             10              2
         8             10              2
         2             NULL            3
         6             NULL            3
         3            100              4

The same occurs if you do "count(distinct s2.number) + 1", "count(distinct s2.number)*1", etc.

Suggested fix:
The null's shouldn't be present.  The correct value for the number should appear.

[11 Jun 2003 10:43] MySQL Verification Team

I tested your query against our last release 3.23.56:

mysql> select s1.id, s1.number,
    -> count(distinct s2.number) + 0 as rank
    -> from silly s1 left join silly s2
    -> on s1.number > s2.number
    -> group by s1.id, s1.number
    -> order by rank;
+----+--------+------+
| id | number | rank |
+----+--------+------+
|  5 |      3 |    0 |
|  4 |      7 |    1 |
|  1 |     10 |    2 |
|  7 |     10 |    2 |
|  8 |     10 |    2 |
|  2 |     20 |    3 |
|  6 |     20 |    3 |
|  3 |    100 |    4 |
+----+--------+------+
8 rows in set (0.00 sec)

Please upgrade your version for this one.