Re: Puzzle: What should this query return?

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.

Steve

Peter Gulutzan wrote:

> Steve Kass <skass_at_drew.edu> wrote in message news:<3D1DE185.7234FF7D_at_drew.edu>...
> > I might have gotten the results backwards in my first post, that's all.
> Yes, that's why I couldn't reproduce what you were saying. Now I can,
> and
> Ocelot is indeed foiled by this query, the answer cannot be 0. When it
> replied "I give up" that was a better answer. Anyway, we agree that's
> not the issue.
>
> The standard says: GROUP BY <grand total> is implicit because the
> <select list> contains a <value expression> that contains a <set
> function specification>, namely COUNT. This rule doesn't depend on
> whether COUNT's value is returned.
> But how many groups are in the grand total if the set function isn't
> evaluated? Dunno.
>
> Peter Gulutzan
> Ocelot Computer Services Inc.
Received on Sun Jun 30 2002 - 06:18:29 CEST