Re: Phantom record problem.

I think that is suppose to happen when no records are returned.

But I must admit that it would be more meaningful, to return a 0 instead of
an empty string.

Poul L. Christiansen

Richard wrote:

> I am experiencing a problem whereby when I submit a query containing an
> aggregate, and if the WHERE clause is not satisfied, I receive a result
> of "(1 row)", but that row is blank.
>
> Here is an example:
>
> Table 1: Customers
>
> Name | UID
> ----------------------
> Andrews | 1
> Smith | 2
> Jones | 3
> Baker | 4
>
> Table 2: Deposits
>
> UID | Amount
> ----------------------
> 3 | 7.00
> 1 | 15.00
> 1 | 10.00
> 2 | 8.00
> 3 | 20.00
>
> Now, if I submit the following query:
>
> SELECT name, sum(amount)
> FROM customers c, deposits d
> WHERE c.uid = d.uid and c.uid = 3
> GROUP BY name;
>
> I get back:
>
> name | sum
> --------------------
> Jones | 27.00
> (1 row)
>
> Fine, this looks good.
>
> BUT, if I submit the following query:
>
> SELECT name, sum(amount)
> FROM customers c, deposits d
> WHERE c.uid = d.uid and c.uid = 5
> GROUP BY name;
>
> (note that there is no record where c.uid=5) I would not expect any rows
> back. However, I get:
>
> name | sum
> --------------------
> |
> (1 row)
>
> Is this a bug, or is it supposed to work this way? I've tried this on
> both versions 6.5.2 and 6.5.3 with the same result.
>
> Any insight would be appreciated. Thank-you.
>
> Cheers,
> Richard