Re: Puzzle: What should this query return?

Gert-Jan,

  My copy of the SQL-92 standard may not be so standard... I see now that it's a (presumably near-final) draft. It says:

  A grouped table is a set of groups derived during the evaluation   of a <group by clause> or a <having clause>.

And it allows for a <set expression> in the select list from a non-grouped table, so long all column references are inside set expressions. In that case, the result set of the query (groups aside, since there are none) contains a single row.

I think whether you call it a group or not, the result is the same, and the query should return a single row with X = 12.

Peter said something about an "implied group by <grand total>", and that language isn't in my copy, but you seem to be suggesting the same thing.

  I guess I should spring for the real standard.

Steve

Gert-Jan Strik wrote:

> Either T is a grouped table, or the query is syntactically incorrect. By
> definition, a grouped query without a group by clause will result in one
> group, whether this group is empty or not.
>
> My 2 cents,
> Gert-Jan
>
> Steve Kass wrote:
> >
> > Peter,
> >
> > The only standard I have a copy of is SQL-92, and I was looking at 7.9.syntax rules.7
> > and 7.9.general rules.1. But now I see that the first mention of <grouped table> allows
> > a <grouped table> with no GROUP BY clause, and I can't find enough yet to determine
> > whether or not T is a grouped table or not. If it is, then is there one group with zero
> > rows or no groups? There's still the chance that the value of 12 on a group of zero rows is 12,
> > and it doesn't seem sensible that the two columns of this query produce different numbers
> > of results. I'm still reading.
> >
> > Steve
Received on Sun Jun 30 2002 - 23:41:24 CEST