firebird-devel

[Firebird-devel] Using DISTINCT and ORDER BY together and unpredictable results

[Arno B. <fbd...@ab...>]

Hi,

If i understand correctly we currently allow unpredictable behaviour =
when DISTINCT and ORDER BY are present and where ORDER BY include other =
fields as in the select list.

Assume this simple table:

CREATE TABLE A_DISTINCT (
  ID INTEGER NOT NULL PRIMARY KEY,
  DISTINCTDATA VARCHAR(20),
  SORTDATA INTEGER);
COMMIT;

Filled with this data:

INSERT INTO A_DISTINCT(ID, DISTINCTDATA, SORTDATA) VALUES (1, 'RED', 1);
INSERT INTO A_DISTINCT(ID, DISTINCTDATA, SORTDATA) VALUES (2, 'GREEN', =
2);
INSERT INTO A_DISTINCT(ID, DISTINCTDATA, SORTDATA) VALUES (3, 'BLUE', =
3);
INSERT INTO A_DISTINCT(ID, DISTINCTDATA, SORTDATA) VALUES (4, 'RED', 4);
INSERT INTO A_DISTINCT(ID, DISTINCTDATA, SORTDATA) VALUES (5, 'GREEN', =
3);
INSERT INTO A_DISTINCT(ID, DISTINCTDATA, SORTDATA) VALUES (6, 'BLUE', =
2);

Query1 only ORDER BY :

SELECT
  d2.DISTINCTDATA,
  d2.SORTDATA
FROM
  A_DISTINCT d2
ORDER BY
  d2.SORTDATA

Okay, very clear this will give this output:

DISTINCTDATA         SORTDATA =20
-------------------------------
RED                  1        =20
GREEN                2        =20
BLUE                 2        =20
GREEN                3        =20
BLUE                 3        =20
RED                  4        =20


Query2 adding DISTINCT :

SELECT
  DISTINCT
  d2.DISTINCTDATA,
  d2.SORTDATA
FROM
  A_DISTINCT d2
ORDER BY
  d2.SORTDATA

Okay, result order somewhat different, but logical output:

DISTINCTDATA         SORTDATA =20
-------------------------------
RED                  1        =20
BLUE                 2        =20
GREEN                2        =20
BLUE                 3        =20
GREEN                3        =20
RED                  4        =20



Query3 only DISTINCTDATA as output:

SELECT
  DISTINCT
  d2.DISTINCTDATA
FROM
  A_DISTINCT d2
ORDER BY
  d2.SORTDATA

Hmmm, now we have:

DISTINCTDATA       =20
--------------------
BLUE               =20
GREEN              =20
RED                =20

Based on which SORTDATA number by BLUE, GREEN and RED?

It seems we should allow this kind of queries.=20
When DISTINCT is used all fields in the ORDER BY clause should be part =
of the select list else we need to raise an error.


Or did i completly missed something here?
Commtents/Suggestion?

Regards,
Arno Brinkman
ABVisie

-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-=3D-
General database development support:
  http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features =
:
  http://www.firebirdsql.org
  http://www.firebirdsql.info
  http://www.fingerbird.de/
  http://www.comunidade-firebird.org/

Support list for Firebird and Interbase users :
fir...@ya...

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info=20



-------------------------------------------------------------------------=
-------
Mijn Postvak In wordt beschermd door SPAMfighter
907 spam-mails zijn er tot op heden geblokkeerd.
Download de gratis SPAMfighter vandaag nog!

Re: [Firebird-devel] Using DISTINCT and ORDER BY together and unpredictable results

[Alex P. <pe...@in...>]

Arno Brinkman:

// skipped

> SELECT
>   DISTINCT
>   d2.DISTINCTDATA
> FROM
>   A_DISTINCT d2
> ORDER BY
>   d2.SORTDATA
> 
> Hmmm, now we have:
> 
> DISTINCTDATA        
> --------------------
> BLUE                
> GREEN               
> RED                 
> 
> Based on which SORTDATA number by BLUE, GREEN and RED?
> 
> It seems we should allow this kind of queries. 
> When DISTINCT is used all fields in the ORDER BY clause should be part of the select list else we need to raise an error.
> 
> 
> Or did i completly missed something here?
> Commtents/Suggestion?
> 


At the first glance your suggestion is correct. On the second hand, 
there will definitely be broken places in applications. If standard is 
silent concerning that issue, I should better leave things 'as is'.

Re: [Firebird-devel] Using DISTINCT and ORDER BY together

[Philippe M. <mak...@fi...>]

Le 26/11/2006 18:28, Alex Peshkov a dit :
> At the first glance your suggestion is correct. On the second hand, 
> there will definitely be broken places in applications. If standard is 
> silent concerning that issue, I should better leave things 'as is'.
> 
always hard when such things can break applications
at least if the rule that "When DISTINCT is used all fields in the ORDER BY
clause should be part of the select list" is the only way to get good result, it
had to be somewhere in documentation
people not following this rule have to be aware that result may be unpredictable
if not doing so when the field is not unique for every record

Re: [Firebird-devel] Using DISTINCT and ORDER BY together andunpredictable results

[Claudio V. C. <cv...@us...>]

> -----Original Message-----
> From: fir...@li...
> [mailto:fir...@li...]On Behalf Of Arno
> Brinkman
> Sent: Domingo, 26 de Noviembre de 2006 11:05
>
> Query3 only DISTINCTDATA as output:
>
> SELECT
>   DISTINCT
>   d2.DISTINCTDATA
> FROM
>   A_DISTINCT d2
> ORDER BY
>   d2.SORTDATA
>
> Hmmm, now we have:
>
> DISTINCTDATA
> --------------------
> BLUE
> GREEN
> RED
>
> Based on which SORTDATA number by BLUE, GREEN and RED?
>
> It seems we should allow this kind of queries.
> When DISTINCT is used all fields in the ORDER BY clause should be
> part of the select list else we need to raise an error.

Since SQL allows to order by a field that doesn't appear in the SELECT list
(and I can't remember any restriction due to DISTINCT, do you see one in the
docs?) I think that by using a field that you cannot see to order, you
accept that the result may seem "magical" to you. I would simply document
your example data and your last example that I kept above.

We can produce a warning, if you want.

C.

Re: [Firebird-devel] Using DISTINCT and ORDER BY togetherandunpredictable results

[Arno B. <fbd...@ab...>]

Hi Claudio,

>> Based on which SORTDATA number by BLUE, GREEN and RED?
>>
>> It seems we should allow this kind of queries.
>> When DISTINCT is used all fields in the ORDER BY clause should be
>> part of the select list else we need to raise an error.
>
> Since SQL allows to order by a field that doesn't appear in the SELECT list
> (and I can't remember any restriction due to DISTINCT, do you see one in the
> docs?)

AFAIU the SQL standard doesn't allow it at all to order by fields which aren't in the select-list, but that's too 
restrictive IMO:

  14.3 <cursor specification>
<..snip..>
    8) If an <order by clause> is specified, then:

      a) Let OBC be the <order by clause>. Let NSK be the number of <sort specification>s in OBC.
          For each i between 1 (one) and NSK, let Ki be the <sort key> contained in the i-th
          <sort specification> in OBC.
      b) Each Ki shall contain a <column reference> and shall not contain a <query expression> or
          a <set function specification>.

> I think that by using a field that you cannot see to order, you
> accept that the result may seem "magical" to you. I would simply document
> your example data and your last example that I kept above.

It is not only that you can't see it, but some random sortdata (between the available equal records) value will be used.

> We can produce a warning, if you want.

Sounds good, although i'm not sure if this will be noticed by the users.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
  http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
  http://www.firebirdsql.org
  http://www.firebirdsql.info

Support list for Interbase and Firebird users:
  fir...@ya...

Nederlandse firebird nieuwsgroep:
  news://newsgroups.firebirdsql.info

Re: [Firebird-devel] Using DISTINCT and ORDER BYtogetherandunpredictable results

[Claudio V. C. <cv...@us...>]

> -----Original Message-----
> From: fir...@li...
> [mailto:fir...@li...]On Behalf Of Arno
> Brinkman
> Sent: Martes, 28 de Noviembre de 2006 7:14
>
> Hi Claudio,
>
> >> Based on which SORTDATA number by BLUE, GREEN and RED?
> >>
> >> It seems we should allow this kind of queries.
> >> When DISTINCT is used all fields in the ORDER BY clause should be
> >> part of the select list else we need to raise an error.
> >
> > Since SQL allows to order by a field that doesn't appear in the
> SELECT list
> > (and I can't remember any restriction due to DISTINCT, do you
> see one in the
> > docs?)
>
> AFAIU the SQL standard doesn't allow it at all to order by fields
> which aren't in the select-list, but that's too
> restrictive IMO:
>
>   14.3 <cursor specification>
> <..snip..>
>     8) If an <order by clause> is specified, then:
>
>       a) Let OBC be the <order by clause>. Let NSK be the number
> of <sort specification>s in OBC.
>           For each i between 1 (one) and NSK, let Ki be the <sort
> key> contained in the i-th
>           <sort specification> in OBC.
>       b) Each Ki shall contain a <column reference> and shall not
> contain a <query expression> or
>           a <set function specification>.

And what is a column reference here? Some lines below, I read:

d) Case:
i) If <sort specification list> contains any <sort key> Ki that contains a
column reference to a
column that is not a column of T, then:
[snip]

This means the text assumes the column references are to a column of T, not
only QS. T is the table and QS is the query specification, isn't it?

Later, we get

9) Case:
A) If NSCR is 0 (zero), then let SKL be the zero-length string.
B) Otherwise:
I) T shall not be a grouped table.
II) QS shall not specify the <set quantifier> DISTINCT or directly contain
one or
more <set function specification>s.

But this restriction is inside an indented block that I interpret as
belonging only to derived tables; read points 6, 7 and 8. I see how the
names are resolved, then the remaining fields are those that form NSCR in
point 8 and so on.


> > I think that by using a field that you cannot see to order, you
> > accept that the result may seem "magical" to you. I would
> simply document
> > your example data and your last example that I kept above.
>
> It is not only that you can't see it, but some random sortdata
> (between the available equal records) value will be used.

What is the difference with ordering by a field that doesn't appear in the
query but having in the query a computed field that is based on a UDF that
you don't know what it does? I could be the rand() call, for example.

> > We can produce a warning, if you want.
>
> Sounds good, although i'm not sure if this will be noticed by the users.

Well, warnings are just for that: if you want to notice them, you pay
attention to them, but otherwise they let you continue running. Well,
completion condition, to not upset the SQL committee.

C.

Re: [Firebird-devel] Using DISTINCT and ORDERBYtogetherandunpredictable results

[Martijn T. <m.t...@up...>]

> > > We can produce a warning, if you want.
> >
> > Sounds good, although i'm not sure if this will be noticed by the users.

No, they will never be noticed.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Re: [Firebird-devel] Using DISTINCT andORDERBYtogetherandunpredictable results

[Claudio V. C. <cv...@us...>]

> -----Original Message-----
> From: fir...@li...
> [mailto:fir...@li...]On Behalf Of
> Martijn Tonies
> Sent: Martes, 28 de Noviembre de 2006 8:11
>
> > > > We can produce a warning, if you want.
> > >
> > > Sounds good, although i'm not sure if this will be noticed by
> the users.
>
> No, they will never be noticed.

Why are you so confident?
This depends on the program and on the intermediate layer. If some layer
doesn't pay attention to warnings, not much else to do, but they use the
same mechanism as errors, so it's not rocket science to trap warnings.

C.

Re: [Firebird-devel] Using DISTINCTandORDERBYtogetherandunpredictable results

[Martijn T. <m.t...@up...>]

> > > > > We can produce a warning, if you want.
> > > >
> > > > Sounds good, although i'm not sure if this will be noticed by
> > the users.
> >
> > No, they will never be noticed.
>
> Why are you so confident?
> This depends on the program and on the intermediate layer. If some layer
> doesn't pay attention to warnings, not much else to do, but they use the
> same mechanism as errors, so it's not rocket science to trap warnings.

Developers can't even be bothered to read the docs :-)


What layers do currently support warnings?

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Re: [Firebird-devel] UsingDISTINCTandORDERBYtogetherandunpredictable results

[Claudio V. C. <cv...@us...>]

> -----Original Message-----
> From: fir...@li...
> [mailto:fir...@li...]On Behalf Of
> Martijn Tonies
> Sent: Martes, 28 de Noviembre de 2006 8:29
> > Why are you so confident?
> > This depends on the program and on the intermediate layer. If some layer
> > doesn't pay attention to warnings, not much else to do, but they use the
> > same mechanism as errors, so it's not rocket science to trap warnings.
>
> Developers can't even be bothered to read the docs :-)

Warnings were introduced with IB6. Enough time.
If they don't read docos, they won't know in the next 30 years that we
support derived tables.

> What layers do currently support warnings?

I don't know. I typically don't test connectivity suites, but if some layer
supports IB/FB it's because it supports their features, unless it's very
general and shallow. Going from errors to warnings is a few lines of code.

C.

Re: [Firebird-devel] UsingDISTINCTandORDERBYtogetherandunpredictable results

[Roman R. <rro...@ac...>]

>> What layers do currently support warnings?

> I don't know. I typically don't test connectivity suites, but if some
> layer supports IB/FB it's because it supports their features, unless it's
> very general and shallow. Going from errors to warnings is a few lines of
> code.

Jaybird, for example, fully supports warnings. However, I'm not really sure 
that they are later checked by any of the application that uses Jaybird :) I 
also think that .Net provider supports warnings.

Roman 

Re: [Firebird-devel] Using DISTINCT and ORDER BYtogetherandunpredictable results

[Martijn T. <m.t...@up...>]

> >> Based on which SORTDATA number by BLUE, GREEN and RED?
> >>
> >> It seems we should allow this kind of queries.
> >> When DISTINCT is used all fields in the ORDER BY clause should be
> >> part of the select list else we need to raise an error.
> >
> > Since SQL allows to order by a field that doesn't appear in the SELECT
list
> > (and I can't remember any restriction due to DISTINCT, do you see one in
the
> > docs?)
>
> AFAIU the SQL standard doesn't allow it at all to order by fields which
aren't in the select-list, but that's too
> restrictive IMO:

Indeed, it doesn't, and for good reason, IMO.

Each resultset is a derived table and you can order by columns in the
resultset only.

What sense does it make to order by a set of values not in your resultset?
The
resultset itself could appear to be random (no ordering).

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

>   14.3 <cursor specification>
> <..snip..>
>     8) If an <order by clause> is specified, then:
>
>       a) Let OBC be the <order by clause>. Let NSK be the number of <sort
specification>s in OBC.
>           For each i between 1 (one) and NSK, let Ki be the <sort key>
contained in the i-th
>           <sort specification> in OBC.
>       b) Each Ki shall contain a <column reference> and shall not contain
a <query expression> or
>           a <set function specification>.
>
> > I think that by using a field that you cannot see to order, you
> > accept that the result may seem "magical" to you. I would simply
document
> > your example data and your last example that I kept above.
>
> It is not only that you can't see it, but some random sortdata (between
the available equal records) value will be used.
>
> > We can produce a warning, if you want.
>
> Sounds good, although i'm not sure if this will be noticed by the users.

Re: [Firebird-devel] Using DISTINCT and ORDER BYtogetherandunpredictable results

[Alex P. <pe...@in...>]

Martijn Tonies:
>> AFAIU the SQL standard doesn't allow it at all to order by fields which
> aren't in the select-list, but that's too
>> restrictive IMO:
> 
> Indeed, it doesn't, and for good reason, IMO.
> 
> Each resultset is a derived table and you can order by columns in the
> resultset only.
> 
> What sense does it make to order by a set of values not in your resultset?
> The
> resultset itself could appear to be random (no ordering).
> 

In a real world SQL output is often not viewed directly, but passed to 
client software, that has a feature of lookup fields. Suppose:

select t1.value, t1.t2link
from t1 join t2 on t1.t2link = t2.id
order by t2.value

At the client side t1.t2link is not displayed directly. Instead 
clientside lookup is done, and user sees t1.value and t2.value in 
dataset, ordered by t2.value - which is exactly what he wants. Therefore 
I think that standard is too restrictive and we should not follow it in 
such a case. Certainly, we may say - dear user, please add t2.value to 
your query result set. But suppose that t1 contains 10000 rows, t2 - 10 
rows, average size of t2.value is 100 byte - in that case adding of 
t2.value to a query leads to additional megabyte of traffic. And if slow 
WAN connection is used, this is not good. Client lookups efficiently 
save traffic in such cases, and order by column not in result set is 
required support for it.

Re: [Firebird-devel] Using DISTINCT and ORDER BYtogetherandunpredictable results

[Martijn T. <m.t...@up...>]

> >> AFAIU the SQL standard doesn't allow it at all to order by fields which
> > aren't in the select-list, but that's too
> >> restrictive IMO:
> >
> > Indeed, it doesn't, and for good reason, IMO.
> >
> > Each resultset is a derived table and you can order by columns in the
> > resultset only.
> >
> > What sense does it make to order by a set of values not in your
resultset?
> > The
> > resultset itself could appear to be random (no ordering).
> >
>
> In a real world SQL output is often not viewed directly, but passed to
> client software, that has a feature of lookup fields. Suppose:
>
> select t1.value, t1.t2link
> from t1 join t2 on t1.t2link = t2.id
> order by t2.value
>
> At the client side t1.t2link is not displayed directly. Instead
> clientside lookup is done, and user sees t1.value and t2.value in
> dataset, ordered by t2.value - which is exactly what he wants. Therefore
> I think that standard is too restrictive and we should not follow it in
> such a case. Certainly, we may say - dear user, please add t2.value to
> your query result set. But suppose that t1 contains 10000 rows, t2 - 10
> rows, average size of t2.value is 100 byte - in that case adding of
> t2.value to a query leads to additional megabyte of traffic. And if slow
> WAN connection is used, this is not good. Client lookups efficiently
> save traffic in such cases, and order by column not in result set is
> required support for it.

If you have a client-side resultset of 10,000 rows, you're in for
some trouble anyway :-)

If you always gonna fetch t2.value, why don't include it up front?

And no, I'm not talking about "fetch on demand" detail lookups.

No offence Alex, but your example is far fetched.

Martijn Tonies
Database Workbench - development tool for Firebird and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Re: [Firebird-devel] Using DISTINCT and ORDER BYtogetherandunpredictable results

[Alex P. <pe...@in...>]

Martijn Tonies:
>> In a real world SQL output is often not viewed directly, but passed to
>> client software, that has a feature of lookup fields. Suppose:
>>
>> select t1.value, t1.t2link
>> from t1 join t2 on t1.t2link = t2.id
>> order by t2.value
>>
>> At the client side t1.t2link is not displayed directly. Instead
>> clientside lookup is done, and user sees t1.value and t2.value in
>> dataset, ordered by t2.value - which is exactly what he wants. Therefore
>> I think that standard is too restrictive and we should not follow it in
>> such a case. Certainly, we may say - dear user, please add t2.value to
>> your query result set. But suppose that t1 contains 10000 rows, t2 - 10
>> rows, average size of t2.value is 100 byte - in that case adding of
>> t2.value to a query leads to additional megabyte of traffic. And if slow
>> WAN connection is used, this is not good. Client lookups efficiently
>> save traffic in such cases, and order by column not in result set is
>> required support for it.
> 
> If you have a client-side resultset of 10,000 rows, you're in for
> some trouble anyway :-)
> 

Well, let it be more realistic example.
select t1.somedate, sum(t1.somedouble), t1.t2link
  from t1 join t2 on t1.t2link = t2.id
where <some-condition>
group by t1.somedate, t1.t2link
  order by t2.somename

Size of one row is 4 + 8 + 4 = 16 bytes, if you add t2.somename it 
becomes about 116 bytes which is 7 times more. And let it not return 
10000 rows at once, let it be 100 requests with different conditions 
with 100 rows per request - anyway with order by column not it result 
set one saves traffic (and money, paid to provider).

> If you always gonna fetch t2.value, why don't include it up front?
> 
> And no, I'm not talking about "fetch on demand" detail lookups.
> 

I don't understand something here. I once cached t2 on client, if I get 
t2link missing in cache I add this particular record to cache, it's fast 
and reliable process. Certainly, if someone changed somename column I 
still have old data in cache, but this is not a big problem in very many 
practical cases.

I don't want to say that we must not try to follow standard, but blindly 
following it's requirements is not always good way to go. And this is 
exactly such a case.

Re: [Firebird-devel] Using DISTINCT and ORDER BYtogetherandunpredictable results

[Arno B. <fbd...@ab...>]

Hi Alex,

> Well, let it be more realistic example.
> select t1.somedate, sum(t1.somedouble), t1.t2link
>  from t1 join t2 on t1.t2link = t2.id
> where <some-condition>
> group by t1.somedate, t1.t2link
>  order by t2.somename

I'm sorry, but this is certainly not going to work (in FB1.5 and higher!).
In fact this is exactly the same problem as with DISTINCT, you're trying to order on a field that is unpredictable (can 
hold multiple values in the group "somedate, t2link" ).

An simple example where i use fields only order by is this:

SELECT
  p.ParticipantName,
  o.OrganisationName,
  ts.TotalScore
FROM
  Participants p
  JOIN Organisations o ON (o.OrganisationID = p.OrganisationID)
  JOIN TotalScores ts ON (ts.ParticipantID = p.ParticipantID)
ORDER BY
  ts.SortKey DESC

ts.SortKey is here a large varchar that holds the sorting-key (outcome from a formula) and you don't want to display 
this.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
  http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
  http://www.firebirdsql.org
  http://www.firebirdsql.info

Support list for Interbase and Firebird users:
  fir...@ya...

Nederlandse firebird nieuwsgroep:
  news://newsgroups.firebirdsql.info

Re: [Firebird-devel] Using DISTINCT and ORDER

[Dmitry Y. <fir...@ya...>]

Martijn Tonies wrote:
>
>> AFAIU the SQL standard doesn't allow it at all to order by fields which
>> aren't in the select-list
> 
> Indeed, it doesn't, and for good reason, IMO.

IIRC, it's the thing that was changed between different standard revisions.


Dmitry

Re: [Firebird-devel] Using DISTINCT and ORDER

[<ai...@ta...>]

On Tue, 28 Nov 2006 11:21:25 +0000 (UTC), m.t...@up...
("Martijn Tonies") wrote:

>What sense does it make to order by a set of values not in your resultset?

I often use it with lazy loading - first I only load PK values in some
order, ie
SELECT DISTINCT PK FROM Customers WHERE(..) ORDER BY Name
and later when I need details I use the PK to load full record. It
would be overkill to include ordering fields into original query's
select list.


ain

Re: [Firebird-devel] Using DISTINCT and ORDER

[Milan B. <mi...@pa...>]

Ain Valtin wrote:
> SELECT DISTINCT PK FROM Customers WHERE(..) ORDER BY Name
> and later when I need details I use the PK to load full record.

If it is really a PK, you don't need to use DISTINCT at all. All PKs are 
distinct by definition.

-- 
Milan Babuskov
http://njam.sourceforge.net
http://www.flamerobin.org

Re: [Firebird-devel] Using DISTINCT and ORDER

[Arno B. <fbd...@ab...>]

Hi,

> I often use it with lazy loading - first I only load PK values in some
> order, ie
> SELECT DISTINCT PK FROM Customers WHERE(..) ORDER BY Name
> and later when I need details I use the PK to load full record. It
> would be overkill to include ordering fields into original query's
> select list.

The DISTINCT is overkill here, because the PK is unique.

This would be fine:
SELECT PK FROM Customers WHERE(..) ORDER BY Name

but this isn't:

SELECT DISTINCT PK FROM Customers WHERE(..) ORDER BY Name

Be prepared that the query will raise an error in the future! 
So change it to a correct query.
In fact DISTINCT <list> and GROUP BY <list> are the same. You'll see that with GROUP BY an error is thrown.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
  http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
  http://www.firebirdsql.org
  http://www.firebirdsql.info

Support list for Interbase and Firebird users:
  fir...@ya...

Nederlandse firebird nieuwsgroep:
  news://newsgroups.firebirdsql.info
 

Re: [Firebird-devel] Using DISTINCT and ORDER

[Arno B. <fbd...@ab...>]

Hi,

>>This would be fine:
>>SELECT PK FROM Customers WHERE(..) ORDER BY Name
>>
>>but this isn't:
>>
>>SELECT DISTINCT PK FROM Customers WHERE(..) ORDER BY Name
>>
>>Be prepared that the query will raise an error in the future! 
> 
> But what about allowing to use "extra" fields in ORDER BY but
> requiring then all(?) fields from SELECT list also appear in ORDER BY
> (only in case of DISTINCT query)? This should make engine happy but
> would be bit more flexible? Ie allow
> 
> SELECT DISTINCT FK FROM Customers WHERE(..) ORDER BY Name, FK
> 
> It could be useful in some cases.

You cannot order by Name and perform distinct on FK.
DISTINCT is always performed on a sorted input.
Imagine this data as result from the internal resultset:

Name        FK
Alex         1
Alex         4
Claudio    2
Dmitry      1

Final output will be

FK
1
4
2
1

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
  http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
  http://www.firebirdsql.org
  http://www.firebirdsql.info

Support list for Interbase and Firebird users:
  fir...@ya...

Nederlandse firebird nieuwsgroep:
  news://newsgroups.firebirdsql.info

Re: [Firebird-devel] Using DISTINCT and ORDER

[<ai...@ta...>]

On Wed, 6 Dec 2006 14:29:18 +0000 (UTC), fbd...@ab...
("Arno Brinkman") wrote:

Hi,

>> I often use it with lazy loading - first I only load PK values in some
>> order, ie
>> SELECT DISTINCT PK FROM Customers WHERE(..) ORDER BY Name
>> and later when I need details I use the PK to load full record. It
>> would be overkill to include ordering fields into original query's
>> select list.
>
>The DISTINCT is overkill here, because the PK is unique.

Doh! In the hurry I didn't think and sent an incomplete example...
Also, looking now to my code (tring to find an real life example of
how do I use DISDINCT and ORDER BY together) I realise that I could
tweak all my queries to return PK instead of DISTINCT FK as the table
with PK is already joined to allow ordering...


>This would be fine:
>SELECT PK FROM Customers WHERE(..) ORDER BY Name
>
>but this isn't:
>
>SELECT DISTINCT PK FROM Customers WHERE(..) ORDER BY Name
>
>Be prepared that the query will raise an error in the future! 

But what about allowing to use "extra" fields in ORDER BY but
requiring then all(?) fields from SELECT list also appear in ORDER BY
(only in case of DISTINCT query)? This should make engine happy but
would be bit more flexible? Ie allow

SELECT DISTINCT FK FROM Customers WHERE(..) ORDER BY Name, FK

It could be useful in some cases.


ain

Re: [Firebird-devel] Using DISTINCT and ORDER BY together

[Dmitry Y. <fir...@ya...>]

Claudio Valderrama C. wrote:
> 
> I would simply document
> your example data and your last example that I kept above.

I tend to agree here.


Dmitry

Re: [Firebird-devel] Using DISTINCT and ORDER BYtogetherandunpredictable results

[Vlad H. <hv...@us...>]

> > We can produce a warning, if you want.
> 
> Sounds good, although i'm not sure if this will be noticed by the users.

MS SQL 2000:

Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Just my $0.02

Regards,
Vlad

Re: [Firebird-devel] Using DISTINCT and ORDER

[Philippe M. <mak...@fi...>]

Le 28/11/2006 13:13, Vlad Horsun a dit :
>>> We can produce a warning, if you want.
>> Sounds good, although i'm not sure if this will be noticed by the user=
s.
>=20
> MS SQL 2000:
>=20
> Server: Msg 145, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if SELECT DISTINCT is spe=
cified.
>=20
and Oracle 10 :
ORA-01791: not a SELECTed expression

 SELECT
   DISTINCT
   d2.DISTINCTDATA
 FROM
   A_DISTINCT  d2
 ORDER BY
   <ERROR>d2.SORTDATA



--=20
Philippe Makowski
http://www.ibphoenix.com
Supporting users of Firebird and InterBase
Firebird serveur SQL open-source en fran=E7ais http://firebird-fr.eu.org

Ma cl=E9 PGP : http://makowski.eu.org/pgpkey.html