Thread
-
Phantom record problem.
Richard <blackw@sfu.ca> — 2000-06-26T18:09:42Z
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 -
Re: Phantom record problem.
Poul L. Christiansen <plc@faroenet.fo> — 2000-06-26T18:34:28Z
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
-
Re: Phantom record problem.
NRonayette <r97m10@socotec.fr> — 2000-06-26T20:16:33Z
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