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

Re: Phantom record problem.

From: "Poul L(dot) Christiansen" <plc(at)faroenet(dot)fo>
To: blackw(at)sfu(dot)ca
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Phantom record problem.
Date: 2000-06-26 18:34:28
Message-ID: 3957A234.C56AD931@faroenet.fo (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

Responses

pgsql-novice by date

Next:From: nronayetteDate: 2000-06-26 20:16:33
Subject: Re: Phantom record problem.
Previous:From: RichardDate: 2000-06-26 18:09:42
Subject: Phantom record problem.

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