Обсуждение: Phantom record problem.

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

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

hi
I don't think so. Because there is a difference between a sum where the result
is equal to zero (for example 1+2-3 =0 or 0+0 =0 ) and a sum of row that does
not exist (here where the uid = 5). With this sum, the row return is null but
the result exist (1 row) because the sum is done.

i hope i was clear...

"Poul L. Christiansen" a écrit :

> 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