Hi,
A friend runs 3.23-2-alpha under FreeBSD, and noticed this odd behaviour...
I've searched the lists for anything similar (I think I remember reading
something a while ago, but the search didn't show anything up).
Take the following output,
"
mysql> SELECT Username,AcctSessionTime AS AcctSessionTimeTotal FROM
Accounting JOIN ISPDDI WHERE ISPDDI.ISPID=1 AND
ISPDDI.DDI=Accounting.CalledStationID ORDER BY Accounting.Username;
+----------+----------------------+
| Username | AcctSessionTimeTotal |
+----------+----------------------+
| testing | 0 |
| testing | 1000 |
+----------+----------------------+
2 rows in set (0.00 sec)
"
So, how come adding a SUM() and GROUP BY nets,
"
mysql> SELECT Username,SUM(AcctSessionTime) AS AcctSessionTimeTotal FROM
Accounting JOIN ISPDDI WHERE ISPDDI.ISPID=1 AND
ISPDDI.DDI=Accounting.CalledStationID GROUP BY Accounting.Username ORDER BY
Accounting.Username;
+----------+----------------------+
| Username | AcctSessionTimeTotal |
+----------+----------------------+
| testing | 0 |
+----------+----------------------+
1 row in set (0.00 sec)
"
Shouldn't this show "testing" as having an "AcctSessionTimeTotal" of 1000 -
not zero?
The output of an explain is below - I dind't want to publish the tabledef's /
globs of detail, in case this has already been noted/found/fixed etc. (Like I
said, my search of the archives didn't find anything)
Regards,
Karl Pielorz
EXPLAIN output:
"
mysql> EXPLAIN SELECT Username,SUM(AcctSessionTime) AS AcctSessionTimeTotal
FROM Accounting JOIN ISPDDI WHERE ISPDDI.ISPID=1 AND
ISPDDI.DDI=Accounting.CalledStationID GROUP BY Accounting.Username ORDER BY
Accounting.Username;
+------------+-------+-----------------------+---------+---------+------+------+------------------------+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+------------+-------+-----------------------+---------+---------+------+------+-------------------------+
| Accounting | ALL | NULL | NULL | NULL | NULL |
2 | |
| ISPDDI | range | PRIMARY,ISPID,ISPID_2 | PRIMARY | NULL | NULL |
4 | where used; Using index |
+------------+-------+-----------------------+---------+---------+------+------+-------------------------+
2 rows in set (0.01 sec)
"
