Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

Next:From: Poul L. ChristiansenDate: 2000-06-26 18:34:28
Subject: Re: Phantom record problem.
Previous:From: Patrick CoulombeDate: 2000-06-26 17:07:54
Subject: date (d-m-Y)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group