Hi,

One thing I would like to see that has bugged me on and off over the years
is to do with the 'group by' clause in SQL (assuming it has not already been
dealt with in 9.4, we are only running 7.31 currently and move to 9.4 this
year).

When doing selects with a 'group by', everything in your select list (maybe
entire records with many columns) has to be listed in your 'group by'
clause, even though you only want to group by one or two of the selected
columns. This is most frustrating.

So, can we have 'group by' where you only need list the columns you actually
want to group by while selecting many more columns than listed in the group
by (or does this break some SQL standard or something?).

Regards,
Bryce Stenberg.


DISCLAIMER: http://www.hrnz.co.nz/eDisclaimer.htm

sending to informix-list


sending to informix-list

Bryce Stenberg wrote:

> Hi,
>
> One thing I would like to see that has bugged me on and off over the years
> is to do with the 'group by' clause in SQL (assuming it has not already been
> dealt with in 9.4, we are only running 7.31 currently and move to 9.4 this
> year).
>
> When doing selects with a 'group by', everything in your select list (maybe
> entire records with many columns) has to be listed in your 'group by'
> clause, even though you only want to group by one or two of the selected
> columns. This is most frustrating.
>
> So, can we have 'group by' where you only need list the columns you actually
> want to group by while selecting many more columns than listed in the group
> by (or does this break some SQL standard or something?).
>
> Regards,
> Bryce Stenberg.
>
>
> DISCLAIMER: http://www.hrnz.co.nz/eDisclaimer.htm
>
> sending to informix-list
>
>
> sending to informix-list
Bryce,

Can you illustrate this with an example and intended semantics?
The rule is that any local element in the select list which is not
aggregated needs to be grouped. Thus it needs to be in the GROUP BY
clause. If you name, say, a column which is neither grouped upon nor
aggregated, what do you expect as a result?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Serge Rielau <srielau@ca.eye-be-em.com> wrote:
> Bryce Stenberg wrote:
> > One thing I would like to see that has bugged me on and off over the years
> > is to do with the 'group by' clause in SQL (assuming it has not already been
> > dealt with in 9.4, we are only running 7.31 currently and move to 9.4 this
> > year).
> >
> > When doing selects with a 'group by', everything in your select list (maybe
> > entire records with many columns) has to be listed in your 'group by'
> > clause, even though you only want to group by one or two of the selected
> > columns. This is most frustrating.
> >
> > So, can we have 'group by' where you only need list the columns you actually
> > want to group by while selecting many more columns than listed in the group
> > by (or does this break some SQL standard or something?).

Succinctly, it breaks the SQL standard, but if there's a bomb-proof
way of providing the functionality, then it could be considered.

> Can you illustrate this with an example and intended semantics?
> The rule is that any local element in the select list which is not
> aggregated needs to be grouped. Thus it needs to be in the GROUP BY
> clause. If you name, say, a column which is neither grouped upon nor
> aggregated, what do you expect as a result?

This is the key point. Suppose we have our classic Order and
OrderItem tables. The columns in the Order table are dependent on the
primary key - OrderNo - of course.

Therefore, the following pseudo-SQL should produce a defined answer:

SELECT O.OrderNo, O.Customer, O.OrderDate, O.DeliveryDate,
SUM(I.ItemCharge) AS TotalCharge
FROM Order O, OrderItem I
WHERE O.OrderNo = I.OrderNo
GROUP BY O.OrderNo;

For each value in O.OrderNo, there will only be a single value of
O.Customer, etc. So, there is no need to specify them in the grouping
clause. But the DBMS has to be able to determine that the O.OrderNo
column is a candidate key of the Order table.

Extending that to a three-table join is left as an exercise for the
reader.

-=JL=-

Jonathan Leffler wrote:

> Serge Rielau <srielau@ca.eye-be-em.com> wrote:
>
>>Bryce Stenberg wrote:
>>
>>>One thing I would like to see that has bugged me on and off over the years
>>>is to do with the 'group by' clause in SQL (assuming it has not already been
>>>dealt with in 9.4, we are only running 7.31 currently and move to 9.4 this
>>>year).
>>>
>>>When doing selects with a 'group by', everything in your select list (maybe
>>>entire records with many columns) has to be listed in your 'group by'
>>>clause, even though you only want to group by one or two of the selected
>>>columns. This is most frustrating.
>>>
>>>So, can we have 'group by' where you only need list the columns you actually
>>>want to group by while selecting many more columns than listed in the group
>>>by (or does this break some SQL standard or something?).
>
>
> Succinctly, it breaks the SQL standard, but if there's a bomb-proof
> way of providing the functionality, then it could be considered.
Indeed.
>>Can you illustrate this with an example and intended semantics?
>>The rule is that any local element in the select list which is not
>>aggregated needs to be grouped. Thus it needs to be in the GROUP BY
>>clause. If you name, say, a column which is neither grouped upon nor
>>aggregated, what do you expect as a result?
>
>
> This is the key point. Suppose we have our classic Order and
> OrderItem tables. The columns in the Order table are dependent on the
> primary key - OrderNo - of course.
>
> Therefore, the following pseudo-SQL should produce a defined answer:
>
> SELECT O.OrderNo, O.Customer, O.OrderDate, O.DeliveryDate,
> SUM(I.ItemCharge) AS TotalCharge
> FROM Order O, OrderItem I
> WHERE O.OrderNo = I.OrderNo
> GROUP BY O.OrderNo;
>
> For each value in O.OrderNo, there will only be a single value of
> O.Customer, etc. So, there is no need to specify them in the grouping
> clause. But the DBMS has to be able to determine that the O.OrderNo
> column is a candidate key of the Order table.
>
> Extending that to a three-table join is left as an exercise for the
> reader.
That would be well defined indeed. IMHO however it's a slippery slope.
The correctness of the statement would depend on the existence of a
unique constraint.
There are other cases where the SQL Compiler can prove transitive
uniqueness, e.g. through functional dependencies, theorem proving and
check constraints.
If I had to write the query I woudl write it as such:
SELECT O.OrderNo, O.Customer, O.OrderDate, O.DeliveryDate,
I.TotalCharge
FROM Order O, (SELECT OrderNo, SUM(ItemCharge) AS TotalCharge
FROM OrderItem
GROUP BY OrderNo) AS I
WHERE O.OrderNo = I.OrderNo;

So I see two issus here:
1. As a developer I cannot see the syntactic correctness of the query
anymore.
2. The query could be correct on one version of the product and not on
another, dependending on how the compiler capabilities improve, or God
forbid, regress.

The second point would break the proposal's neck in the standard.

Lastly, and this is my personal opinion entirely, semantic error
checking belongs into the compiler front end, not the back-end.
The back-end should only complain about not having enough resources to
satisfy a perfectly legal query.
But I maybe biased towards what I know.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Serge Rielau wrote:

> Lastly, and this is my personal opinion entirely, semantic error
> checking belongs into the compiler front end, not the back-end.
> The back-end should only complain about not having enough resources to
> satisfy a perfectly legal query.
> But I maybe biased towards what I know.

Does dbaccess compile my SQL before it passes it through?

--
"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche

Obnoxio The Clown wrote:
> Serge Rielau wrote:
>
>
>>Lastly, and this is my personal opinion entirely, semantic error
>>checking belongs into the compiler front end, not the back-end.
>>The back-end should only complain about not having enough resources to
>>satisfy a perfectly legal query.
>>But I maybe biased towards what I know.
>
>
> Does dbaccess compile my SQL before it passes it through?
>
Not sure I understand the context of the question. With compiler
front-end I refer to the part of the compiler that builds an internal
model of the text (parsing, semantic validation, in SQL likely view
expansion, ...). The back-end is composed of an optimizer and some sort
of code-generator.

dbaccess, if I'm not mistaken, is a tool and not part of the compiler.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Serge Rielau wrote:

> Obnoxio The Clown wrote:
>> Serge Rielau wrote:
>>
>>
>>>Lastly, and this is my personal opinion entirely, semantic error
>>>checking belongs into the compiler front end, not the back-end.
>>>The back-end should only complain about not having enough resources to
>>>satisfy a perfectly legal query.
>>>But I maybe biased towards what I know.
>>
>>
>> Does dbaccess compile my SQL before it passes it through?
>>
> Not sure I understand the context of the question. With compiler
> front-end I refer to the part of the compiler that builds an internal
> model of the text (parsing, semantic validation, in SQL likely view
> expansion, ...). The back-end is composed of an optimizer and some sort
> of code-generator.
>
> dbaccess, if I'm not mistaken, is a tool and not part of the compiler.

Thank you. When you said front end, I thought you meant the client tool. )

--
"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche

Obnoxio The Clown <obnoxio@hotmail.com> wrote in message news:<c0gt9r$16pi5o$1@ID-64669.news.uni-berlin.de>...
> Serge Rielau wrote:
>
> > Lastly, and this is my personal opinion entirely, semantic error
> > checking belongs into the compiler front end, not the back-end.
> > The back-end should only complain about not having enough resources to
> > satisfy a perfectly legal query.
> > But I maybe biased towards what I know.
>
> Does dbaccess compile my SQL before it passes it through?

dbaccess uses dynamic query (sql describe and such).