From: | Richard <blackw(at)sfu(dot)ca> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Phantom record problem. |
Date: | 2000-06-26 18:09:42 |
Message-ID: | 39579C66.F7BC0B42@sfu.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Poul L. Christiansen | 2000-06-26 18:34:28 | Re: Phantom record problem. |
Previous Message | Patrick Coulombe | 2000-06-26 17:07:54 | date (d-m-Y) |