I tried that, but the fields ended up getting set to '', rather than 0.
The fields are defined as varchar, remember.
*But*, I don't think the NULL values are causing the problems, because
the query runs fine when more than 1 row was returned. And my query
actually returned all the rows, including the one that contained the
NULL values.
Just to clarify:
> SELECT d.name, free_space, size, free_space/size FROM devices d LEFT
OUTER JOIN disks ON (d.id =disks.computer_id and disks.size not null and
disks.free_space not null) WHERE d.name='kensho';
> kensho|71975571456|79966171136|0.900075249740165
> kensho|||
> kensho|206140624896|237003182080|0.869779988128673
> SELECT d.name, sum(free_space), sum(size), sum(free_space)/sum(size)
FROM devices d LEFT OUTER JOIN disks ON (d.id=disks.computer_id) WHERE
d.name='kensho';
> kensho|278116196352.0|316969353216.0|0.877422985945511
> SELECT d.name, sum(free_space), sum(size), sum(free_space)/sum(size)
FROM devices d LEFT OUTER JOIN disks ON (d.id =disks.computer_id and
disks.name='G:') WHERE d.name='kensho';
> kensho|206140624896|237003182080|0
I'm really starting to think I found a bug.
From what I remember of aggregates, if there is a NULL in the
calculation on any field, the result will always be NULL. This is a huge
beware. I'm not sure if it's affecting what you're doing, but I have a
clue that it may be. How are the results affected when you
update devices set free_space = 0 where free_space is NULL;
update devices set size = 0 where size is NULL;
and rerun the two initial queries again?
