Re: [HACKERS] Phantom row from aggregate in self-join in 6.5

At 20:52 22/07/99 -0400, Tom Lane wrote:
>Malcolm Beattie <mbeattie(at)sable(dot)ox(dot)ac(dot)uk> writes:
>> Using 6.5 (via Thomas Lockhart's Linux RPM build of Jul 2), I get a
>> phantom row when doing the following:
>> create table foo (a int);
>> select t1.a, count(*) from foo t1, foo t2 group by t1.a;
>> I get
>> a|count
>> -+-----
>> | 0
>> (1 row)
>> instead of zero rows.
>

>if you want it changed you'll need to cite chapter and verse from the
>SQL92 standard, not just assert that Informix does it differently.

Sadly, I only have access to a 1993 draft standard, but the following is from section 7.10:

"The result of the <group by clause> is a partitioning of T into
a set of groups. The set is the minimum number of groups such
that, for each grouping column of each group of more than one
row, no two values of that grouping column are distinct."

>From my reading of the standad, 'T' is the result of the select statement prior to being grouped. It would seem that if T contains no rows, then "the minimum number of groups" would have to be zero.

Other references, such as:

2) Let CR be the <column reference> with <column name> CN identi-
fying the grouping column. Every row of a given group contains
equal values of CN. When a <search condition> or <value expres-
sion> is applied to a group, CR is a reference to the value of
CN.

(General Rules, Section 7.10)

Would seem to indicate that any grouped result row must be supported by underlying rows on the ungrouped result set.

Finally, using the above example:

>> create table foo (a int);
>> select t1.a, count(*) from foo t1, foo t2 group by t1.a;
>> I get
>> a|count
>> -+-----
>> | 0
>> (1 row)

the values returned in the column 'a' NEVER appears in the source table. Is there anyone out there who believes this is NOT a problem?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: +61-03-5367 7422 | _________ \
Fax: +61-03-5367 7430 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/