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