At 20:52 22/07/99 -0400, Tom Lane wrote:
>Malcolm Beattie <mbeattie@sable.ox.ac.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
ofgroups such that, for each grouping column of each group of more than one row, no two values of that grouping
columnare distinct."
>From my reading of the standad, 'T' is the result of the select statement prior to being grouped. It would seem that
ifT 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
rowof a given group contains equal values of CN. When a <search condition> or <value expres- sion>
isapplied 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
NOTa 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 |/