Prev: The job of a relational DBMS (was: Sql request)
Next: Pivot table Query - Need help in building correct logic
From: Thomas Kellerer on 1 Dec 2009 17:44
Gene Wirchenko wrote on 01.12.2009 05:35:
> For example, a group by in SQL forces the result to be sorted by
> the grouping unless otherwise overridden.

Never has been true. Group by does *not* sort the result.
Not even in Oracle 8 and and certainly not for any Oracle version > 9
(and not for any Postgres as well)

Thomas
From: joel garry on 1 Dec 2009 20:23
On Dec 1, 11:48 am, Shakespeare <what...(a)xs4all.nl> wrote:
> joel garry schreef:
>
>
>
> > On Nov 30, 8:35 pm, Gene Wirchenko <ge...(a)ocis.net> wrote:
> >> On Tue, 01 Dec 2009 13:45:44 +1100, Ben Finney
>
> >> <bignose+hates-s...(a)benfinney.id.au> wrote:
> >>> Gene Wirchenko <ge...(a)ocis.net> writes:
> >>>> On Tue, 01 Dec 2009 08:50:34 +1100, Ben Finney
> >>>> <bignose+hates-s...(a)benfinney.id.au> wrote:
> >>>>> joel garry <joel-ga...(a)home.com> writes:
> >>>> [snip]
> >>>>>> Can't a function be part of the DBMS?
> >>>>> A non-relational function shouldn't be part of a relational DBMS, no.
> >>> This was far too broad, I now see. Originally it was in the context of a
> >>> *query* returning a non-relational result, which is really as far as I
> >>> should have taken it.
> >>      I think you still have problems with the division.  (I do not
> >> claim to be able to define the split myself.  I know how difficult it
> >> can be to rigourously define something.)
>
> > And perhaps relvars can torture non-relations to relations.  But this
> > is all too deep for me.  I mostly agree with you guys.  There should
> > be clear delineations between relational and non-relational parts of
> > systems, as well as standard SQL versus extensions and procedurals.
>
> >>>>      Addition of integral values is a function mapping two integral
> >>>> values to an integral value.
> >>> Right; of course, there are heaps of functions operating on attribute
> >>> values that can be used *within* relational operations to modify the
> >>> relation that will be returned. Such functions definitely belong as part
> >>> of the relational DBMS.
> >>> What I was trying to express was that relational operations — like the
> >>> various relational operations that ‘SELECT’ implements — should only
> >>> return data as relations (they might also return status responses). They
> >>> should never return non-relation data.
> >>      A relation does not have order.  This would not be a relation
> >>           select clicode,cliname from clients order by clicode
> >> because of the order by clause.
>
> I thought that one was about the order of attributes, not rows.

Kinda. The tuples of a relation (AKA rows in a table) have no order,
which leads to no order on the attributes. Applications use queries
to access data. So we wind up with confusion between application
access and relational inquiry with SQL. It makes no sense to maintain
that SQL (of any variety) only does or should do relational
operations. It happens to be the single language of both relational
queries and applications, using the relational terminology. So Ben is
both right and wrong about user applications being the one to process
the data for users, as SQL confounds the difference. I used to (like,
15-20 years ago) think this was a big shortcoming of SQL, since it
does only part of the user application. Now that the various extended
SQL's can do all of this (even *gasp* html and xml), and the
standardization is close enough to allow it to be used as a data
access language by various tools, that is less of an issue, though the
basic differences between engines makes for a different problem. I
think we can all agree that the popular engines distance themselves
from relational theory to a great degree - but I don't think that
leads to judging these distances as bad at all. I know that is
flamebait in comp.databases, but, well, I didn't start the
crossposting.

As far as analytics, I think it is good to be able to shove some
processing into the sql engine, but I think we are also seeing that it
is hard to predict that performance will be better by doing that, as a
generality. Basically the same problem as forcing order into group
by, there are times we as programmers know better how to manipulate
the data, especially sorting and ordering mass quantities.

jg
--
@home.com is bogus.
"What does HTML stand for?"
"I don't, ah, HoT Man Links?" - heard on radio call-in trivia
contest.



From: Gene Wirchenko on 1 Dec 2009 22:06
On Tue, 01 Dec 2009 20:48:33 +0100, Shakespeare <whatsin(a)xs4all.nl>
wrote:

[snip]

>>> A relation does not have order. This would not be a relation
>>> select clicode,cliname from clients order by clicode
>>> because of the order by clause.
>
>I thought that one was about the order of attributes, not rows.

It is both. Remember that a relation is a set of tuples. A set
has no order.

[snip]

Sincerely,

Gene Wirchenko
From: Gene Wirchenko on 1 Dec 2009 22:10
On Tue, 01 Dec 2009 23:44:58 +0100, Thomas Kellerer
<OTPXDAJCSJVU(a)spammotel.com> wrote:

>Gene Wirchenko wrote on 01.12.2009 05:35:
>> For example, a group by in SQL forces the result to be sorted by
>> the grouping unless otherwise overridden.
>
>Never has been true. Group by does *not* sort the result.
>Not even in Oracle 8 and and certainly not for any Oracle version > 9
>(and not for any Postgres as well)

It does in my dialect (Visual FoxPro). If you are correct, I
overgeneralised.

Granted that that would be implementation-dependent, but it would
be convenient for it to do it that way while grouping. (How else
would it?) My point is that if the DBMS does any sorting internally,
the relational-data-to-app converter should get the benefit of it if
it needs it. It would be silly for the DBMS to sort internally and
then the converter do it again.

Sincerely,

Gene Wirchenko
From: Shakespeare on 2 Dec 2009 02:13
Thomas Kellerer schreef:
> Gene Wirchenko wrote on 01.12.2009 05:35:
>> For example, a group by in SQL forces the result to be sorted by
>> the grouping unless otherwise overridden.
>
> Never has been true. Group by does *not* sort the result. Not even in
> Oracle 8 and and certainly not for any Oracle version > 9 (and not for
> any Postgres as well)
>
> Thomas

Never? It did at least in Oracle 7. As a well known and too many times
used side effect. I remember having to reprogram queries because
developed programs relied on the sort....

Shakespeare
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: The job of a relational DBMS (was: Sql request)
Next: Pivot table Query - Need help in building correct logic