At 4:37 PM +0100 2000-01-07, Nicolas Prade wrote:
>Hello,
>using a COUNT(...) in an arithmetic operation
>is sometimes very problematic.
>
>Could someone please try this simple test procedure :
>
> >CREATE TABLE test (d DATETIME, i INT);
> >INSERT INTO test VALUES (NOW(), 1);
>
> >SELECT COUNT(i), i, COUNT(i)*i FROM test GROUP BY i;
>+----------+------+------------+
>| COUNT(i) | i | COUNT(i)*i |
>+----------+------+------------+
>| 1 | 1 | 1 |
>+----------+------+------------+
>
> >SELECT COUNT(i), (i+0), COUNT(i)*(i+0) FROM test GROUP BY i;
>+----------+-------+----------------+
>| COUNT(i) | (i+0) | COUNT(i)*(i+0) |
>+----------+-------+----------------+
>| 1 | 1 | 0 |
>+----------+-------+----------------+
>
>Nobody here can explain this.
Happens for me too. I'm running 3.23.8.
>
>- Can you reproduce this result ?
Yes. If I change COUNT(i)*(i+0) to COUNT(i)*(i) or to
COUNT(i)*i, it works correctly.
>- Is there someone one the list who can explain it...
Not me.
--
Paul DuBois, paul@stripped
