[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!