 | |
[Info-Ingres] not quit what I meant by min()
comp.databases.ingres comp.databases.ingres
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: not quit what I meant by min() -
04-10-2009
, 10:48 AM
|
Whoa. Down, boy. You are ranting enthusiastically about a pathologicalexample that was presented as such. |
person; if I had any friends you could ask them!
Quote:
|
Ingres DATE type offers a number of date functions that are extremely tempting. * |
options? ARRRGH!!
#12
| |||
| |||
|
|
I defend our right to write garbage. Its in the constitution! > Granted pornographers use the same defense.... |
#13
| |||
| |||
|
|
That took me awhile to understand. You mean that tuples are formed, logically, as a Cartesian product of the tables, the WHERE clause filters them, the GROUP BY consolidates them, and the SELECT statement chooses columns. And because things are -- theoretically -- done thus, the GROUP BY can't know the aliases established "later" by SELECT. Except, who applies the aggregation functions? Doesn't GROUP BY have to peek? Nope. The grouping is done on the columns, then the groups are reduced to single rows (constants, aggregates, grouping columns and expressions built on those things). You cannot GROUP BY an expression. |
or is an expression derived from a column. I don't know if it's
that way in the Standard, but it's a perfectly reasonable and
logical extension of grouping on pure columns. The syntax
extensions (group by 1,2,3; group by result-column) are just
short-hand ways of identifying a grouping expression, and I
don't particularly care for either of the short-hand's.
The syntax means that the implementation has to match up the
group-by expressions with the select result list to ensure that
all of the result-list expressions are either aggs or are
also group-by expressions. This is harder with expressions than
it is with simple columns, but since when has SQL ever had any
sympathy for implementation issues?
The GROUP BY list establishes how the rows that survive the WHERE filter
are grouped into separate piles. One then takes all of the aggregation
functions needed in following steps (HAVING clause, and the result-list)
and applies them to reduce each pile to a single output row, ie group.
You then apply the HAVING filter, if any, and evaluate the
SELECT result list to get the (sub-)select result. Last comes
union and order by.
Karl
#14
| |||
| |||
|
|
Whoa. Down, boy. You are ranting enthusiastically about a pathological example that was presented as such. Oh, you know I always rant on Newsgroups. But I am a nice guy in person; if I had any friends you could ask them! Ingres DATE type offers a number of date functions that are extremely tempting. Have you ever played with SQL Server and their insane cornucopia of options? ARRRGH!! |
I've worked extensively with SQL Server and Oracle dates.
Oracle dates are ... let's be gentle ... frakkin' insane!
They work, no doubt. But it reminds me of the time I told my Dad
I wanted to build a go-cart, so he gave me an engine that, once
rebuilt and modified properly, would be perfect for the job.
So, I started to rebuild the engine, but he wasn't terribly forthcoming
with the information on how to do that.
Just like Oracle dates. They can do ANYTHING if you can just figure
out how ...
Cheers,
Michael Leo
Director of Managed Services
Kettle River Consulting
#15
| |||
| |||
|
|
Well, you can, you know. *You can GROUP BY anything that is a column or is an expression derived from a column. *I don't know if it's that wayin the Standard, but it's a perfectly reasonable and logical extension ofgrouping on pure columns. |
was a logician who trained under Raymond Smullyan and he kept us
straight. A column is at the lowest level of abstraction. If you
want to have a simple expression, you can do this with a nested
query:
SELECT ..
FROM (SELECT <exp> AS x1..
FROM ..) AS Foobar( ., x, ..) -- creates a table!
GROUP BY x1, ..;
But it falls apart when you get a self-reference. Russell's paradox
and all that jazz. I cannot do the query right now, but it is
basically GROUP BY <<Russell's Barber >>.
Having been thru a decade of ANSI X3H2, I want to write the extra code
and not argue over how vague shorthand works. My favorite is just the
simple "GROUP BY 1,2,3" syntax. Drop a column, what happens/? What
does "GROUP BY 1,2,2" mean? Weren't columns supposed referenced by
name and not position in a cursor? Etc? Sequential processing
lives!!
*The syntax
Quote:
|
extensions (group by 1,2,3; *group by result-column) are just short-hand ways of identifying a grouping expression, and I don't particularly care for either of the short-hand's. The syntax means that the implementation has to match up the group-by expressions with the select result list to ensure that all of the result-list expressions are either aggs or are also group-by expressions. *This is harder with expressions than it is with simple columns, but since when has SQL ever had any sympathy for implementation issues? The GROUP BY list establishes how the rows that survive the WHERE filter are grouped into separate piles. *One then takes all of the aggregation functions needed in following steps (HAVING clause, and the result-list) and applies them to reduce each pile to a single output row, ie group. You then apply the HAVING filter, if any, and evaluate the SELECT result list to get the (sub-)select result. *Last comes union and order by. Karl |
#16
| |||
| |||
|
|
Well, you can, you know. *You can GROUP BY anything that is a column or is an expression derived from a column. *I don't know if it's that way in the Standard, but it's a perfectly reasonable and logical extension of grouping on pure columns. Nope. We left it out of the standards deliberately. Bruce Horowitz was a logician who trained under Raymond Smullyan and he kept us straight. A column is at the lowest level of abstraction. If you want to have a simple expression, you can do this with a nested query: SELECT .. FROM (SELECT <exp> AS x1.. FROM ..) AS Foobar( ., x, ..) -- creates a table! GROUP BY x1, ..; But it falls apart when you get a self-reference. Russell's paradox and all that jazz. I cannot do the query right now, but it is basically GROUP BY <<Russell's Barber >>. |
don't see how grouping by an expression could ever possibly be
ambiguous or wrong. We start by forming the Cartesian product of the
tables; we then restrict the meaningless combinations; then we group.
At the point where we group we still haven't projected the columns so
we can compute any expression we like. The parser and the optimizer
have sight of the whole query so they can perfectly well "look ahead"
to see what is in the GROUP BY and HAVING clauses.
I have two questions really: (1) how can that break down in a way that
makes it fundamentally wrong to use expressions in the GROUP BY
clause; and (2) even if it could, how is that so much worse than a lot
of other stuff that SQL does wrong yet is still tolerated/accommodated
(e.g. duplicate rows, or treating NULL
differently depending on context)?
Quote:
|
Having been thru a decade of ANSI X3H2, I want to write the extra code and not argue over how vague shorthand works. My favorite is just the simple "GROUP BY 1,2,3" syntax. Drop a column, what happens/? What does "GROUP BY 1,2,2" mean? Weren't columns supposed referenced by name and not position in a cursor? Etc? |
expressions to grouping by ordinal column number. I have to admit that
I didn't know Ingres even allowed that; I would always have guessed
that would be a syntax error. Now that I know it's allowed I
abhor the idea too. Grouping by ordinals *is* nonsense. But using
ordinals is a different problem.
--
Roy
UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.