Phantom record problem.

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

Responses

Browse pgsql-novice by date

  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)