Re: Puzzle: What should this query return?

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

Peter Gulutzan wrote:

> Steve Kass <skass_at_drew.edu> wrote in message news:<3D1E8695.62E3BC42_at_drew.edu>...
> > Peter,
> >
> > Itzik Ben-Gan has done a good job of convincing me that the correct
> > answer is 12. According to the (SQL-92) standard, if the select list
> > contains a set expression, and there is no grouped table, then the result
> > set contains exactly one row. In that case, the columns of the result
> > set are found be evaluating the value expressions in the select list, which
> > in this case, yields 12.
> >
> > It doesn't matter whether or not the set expression is evaluated or not -
> > its presence is enough to force the single-row result set.
> >
> > I envy you for the opportunity to dig into why Ocelot is foiled, since
> > I imagine it will lead not only to a more robust product, but also to
> > a better understanding of the standard.
> >
> It was my understanding that there is a grouped table because "GROUP
> BY <grand total>" is implied, that that table contains zero rows, that
> the application of a COUNT function using a table as an argument
> source is what produces a value equal to the cardinality of the table.
> I should certainly strive towards a better understanding. Thank you.
>
> Peter Gulutzan
> Ocelot Computer Services Inc.
Received on Sun Jun 30 2002 - 16:47:12 CEST