Re: Attribute must be GROUPed.... ?

On Thu, 1 May 2003, Daniele Orlandi wrote:

> Stephan Szabo wrote:
> >
> > AFAIK it's a requirement of the SQL spec. (SQL92(draft) 7.9 SR 7, "each
> > <column reference> in each <value expression> that references a column
> > of T shall reference a grouping column or be specified within a <set
> > function specification>."
>
> I see... How should the "shall" term be considered ? I don't have much
> knowledge of the SQL specs language.

"In the Syntax Rules, the term shall defines conditions that are
required to be true of syntactically conforming SQL language."

I think most people would write "must", although I think "shall" might be
more correct.

> How other DBMS behave in this case ? I know that mysql doesn't enforce
> this requirement but... mysql is not a perfect reference wrt standards
> compliance.

I'm not sure actually, someone else will need to speak to this (well, I
can test Oracle later, but that's it).

> > Well, it'd mean you didn't have to put the extra columns in the group by
> > list to make them grouping columns.
>
> This is what I currently do as a workaround, but it's not much clean
> expecially when you have many ungrouped fields in the target list.

Yeah, that can get to be a problem...  In any case, you'll probably get
other comments.  Oh yeah, and you'll probably be asked for documentation
comments if it's even considered since you're adding a visible GUC entry.
:)