Possible bug in rlm_sqlcounter examples

[Tim White]

Hi All.

I am using the following SQL in sqlcounter for a MySQL database in the 
Grase Hotspot project, as part of daily/hourly/monthly counters.

         query = "SELECT SUM(acctsessiontime - \
                  GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) \
                  FROM radacct WHERE username = '%{%k}' AND \
                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"

This is taken directly out of the examples that come with Freeradius, 
and is also in the Wiki. 
http://wiki.freeradius.org/Rlm_sqlcounter#Example+Setup

Recently I was having problems where the first login for a day, wasn't 
being limited to it's daily limit. However, subsequent logins for they 
day were. So for example, if they had a 4 hour limit, and the first 
login went over 4 hours, it could keep going as Session-Limit was being 
returned by freeradius. However, all subsequent logins would return a 
valid Session-Limit (timeout?) or an access denied if they had gone over 
the daily limit.
Some poking around showed that if there was no logins for that day, the 
above SQL will return NULL, which Freeradius complains about, something 
along the lines of there not being an integer in the results (I can't 
get the exact error message right now), and so the sqlcounter just 
passes through as noop.
To solve the problem, I needed to use an IFNULL (or COALESCE) to return 
a 0 instead of NULL and then Freeradius sqlcounter returns the correct 
attributes.
         query = "SELECT COALESCE( SUM(acctsessiontime - \
                  GREATEST((%b - UNIX_TIMESTAMP(acctstarttime)), 0)) ) \
                  FROM radacct WHERE username = '%{%k}' AND \
                  UNIX_TIMESTAMP(acctstarttime) + acctsessiontime > '%b'"

This happens on the arm architecture, and so may be architecture 
dependent. A quick test on x86 MySQL shows it also returns NULL, however 
I've not had the chance to test how Freeradius interprets the NULL, as 0 
or NULL. I will get out an x86 test machine shortly and test what 
Freeradius is returning.

$ apt-cache policy freeradius
freeradius:
   Installed: 2.1.10+dfsg-2

Debian 6.0.3 Linux Kernel 2.6.32 armv5tel

Has anyone else run into this problem?

Tim