SQL query with ORDER BY does not order its result as requested

[monetdb-team]

Date: 2014-06-06 15:07:44 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.17.17 (Jan2014-SP2)
CC: @njnes

Last updated: 2014-10-31 14:13:36 +0100

Comment 19831

Date: 2014-06-06 15:07:44 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:29.0) Gecko/20100101 Firefox/29.0
Build Identifier:

Execute SQL query:
SELECT DISTINCT type AS "Data type" FROM SYS.ARGS ORDER BY type
The result is NOT sorted on the type name values

Reproducible: Always

Steps to Reproduce:

1. Start mserver5 (with SQL module loaded)
2. Start SQL frontend program (I used SQuirreL together with the MonetDB JDBC driver)
3. execute SQL query:
   SELECT DISTINCT type AS "Data type" FROM SYS.ARGS ORDER BY type

Actual Results:

Data type
table
polygon
blob
char
linestring
varchar
geometry
mbr
url
decimal
multipolygon
double
month_interval
multilinestring
time
timetz
timestamp
date
real
sec_interval
timestamptz
clob
inet
point
multipoint
bigint
any
oid
int
wrd
tinyint
boolean
smallint

Expected Results:

Data type
any
bigint
blob
boolean
char
clob
date
decimal
double
geometry
inet
int
linestring
mbr
month_interval
multilinestring
multipoint
multipolygon
oid
point
polygon
real
sec_interval
smallint
table
time
timestamp
timestamptz
timetz
tinyint
url
varchar
wrd

The problem is probably caused the combination of DISTINCT and usage of an alias name.
Following queries execute okay:
SELECT DISTINCT type AS "Data type" FROM SYS.ARGS ORDER BY "Data type"
SELECT DISTINCT type AS "Data type" FROM SYS.ARGS ORDER BY 1
SELECT type AS "Data type" FROM SYS.ARGS ORDER BY type

Comment 19894

Date: 2014-07-09 15:46:34 +0200
From: MonetDB Mercurial Repository <>

Changeset d3a1eddca24b made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=d3a1eddca24b

Changeset description:

fixed bug #3498, make sure we have a table (not a single row)
before we do the topn.

fixed bug #3489, distinct with order by, use the proper extend directly for the possible order by columns (which are not part of the output)

Comment 19985

Date: 2014-08-06 19:10:06 +0200
From: @njnes

fixed

Comment 20329

Date: 2014-10-31 14:13:36 +0100
From: @sjoerdmullender

Oct2014 has been released.