firebird-devel

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

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

Hi Claudio,

Claudio Valderrama C. wrote:
> 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?)

Suppose you have a table:

X   Y
--- ---
   1   3
   1   5
   2   4

What should the following query return: SELECT DISTINCT X ORDER BY Y?

1
2

or

2
1

-- 
Milan Babuskov
http://swoes.blogspot.com/
http://www.flamerobin.org

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

[Adriano d. S. F. <adr...@uo...>]

Milan Babuskov wrote:
> Hi Claudio,
>
> Claudio Valderrama C. wrote:
>   
>> 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?)
>>     
>
> Suppose you have a table:
>
> X   Y
> --- ---
>    1   3
>    1   5
>    2   4
>
> What should the following query return: SELECT DISTINCT X ORDER BY Y?
>
> 1
> 2
>
> or
>
> 2
> 1
>   
DISTINCT act like an aggregate (MS Access has a FIRST aggregate 
function) causing many rows to become one.

So I think is not wrong to reject this query, like it's rejected when 
aggregate functions are used.


Adriano

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 Milan
> Babuskov
> Sent: Martes, 28 de Noviembre de 2006 17:54
>
> Suppose you have a table:
>
> X   Y
> --- ---
>    1   3
>    1   5
>    2   4
>
> What should the following query return: SELECT DISTINCT X ORDER BY Y?
>
> 1
> 2
>
> or
>
> 2
> 1

The user asked for a hidden field to drive the order, then I don't care.

I can be convinced that people are afraid of DISTINCT v/s SORT on hidden
field and forbid it, but not the general case.

C.

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

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

> > Suppose you have a table:
> >
> > X   Y
> > --- ---
> >    1   3
> >    1   5
> >    2   4
> >
> > What should the following query return: SELECT DISTINCT X ORDER BY Y?
> >
> > 1
> > 2
> >
> > or
> >
> > 2
> > 1
>
> The user asked for a hidden field to drive the order, then I don't care.
>
> I can be convinced that people are afraid of DISTINCT v/s SORT on hidden
> field and forbid it, but not the general case.

No offence, but this is exactly why a SORT on an "original table column"
doesn't
make sense.

The _resultset_ should be sorted which means you can only sort on values
in the resultset.

Yes, it might _sometimes_ make sense to sort by a "hidden column" from the
original table, but in general, I would say it's rubbish.

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] Using DISTINCT and ORDER BY

[LacaK <la...@us...>]

If I can add my vote, then :

1. SELECT col1 FROM t ORDER BY col2
is useful in real apps, so I would like if this will be supported 
(without error, without warning)

2. SELECT DISTINCT col1 FROM t ORDER BY col2
if this is "problematic", then this usage can be limited (but if not, 
then I will vote to leave it)

-Laco.

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

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

Arno Brinkman wrote:
>  
> 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.

After thinking more, I have to admit that I'd like us to reject such 
statements. As Arno has correctly pointed out, this is exactly the same 
issue as for GROUP BY vs ORDER BY and we disallow it since v1.5. While 
the visible effect is similar to the ordering by a hidden column, the 
situation is in fact different, because even the engine doesn't know how 
to produce a correct result.


Dmitry