Re: Whatcha' wanta have?????
A wishlist thread: a user asked why GROUP BY forces every non-aggregated select-list column to be repeated in the GROUP BY, and wanted to list only the columns actually being grouped. Jonathan Leffler explained this breaks the SQL standard, though it would be well-defined where the grouping column is a candidate key (e.g. Order joined to OrderItem grouped by OrderNo). Serge Rielau argued against it: validity would hinge on constraints and on compiler smarts varying between versions, and offered the standard workaround — aggregate OrderItem in a derived table and join it to Order. A side exchange clarified that 'compiler front-end' means parsing/semantic checking in the server, not dbaccess, which uses dynamic SQL. No product change or other resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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. :o)
--
"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).