SQL order by <column number> only sorts on first call

[monetdb-team]

Date: 2013-04-26 18:30:17 +0200
From: Pete Hollobon <>
To: SQL devs <>
Version: 11.15.3 (Feb2013-SP1)
CC: @njnes, pete

Last updated: 2013-07-03 08:47:58 +0200

Comment 18701

Date: 2013-04-26 18:30:17 +0200
From: Pete Hollobon <>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.31 (KHTML, like Gecko) Chrome/26.0.1410.64 Safari/537.31
Build Identifier:

When using a column number to specify a column to order by (e.g. "order by 2"), subsequent queries which are identical - apart from the column number ordered by - ignore the the ordering and return identical results to the first query.

Ordering by column name works correctly.

Reproducible: Always

Steps to Reproduce:

sql>create table testorder (a string, b string);
operation successful (48.648ms)
sql>insert into testorder values ('a', 'z'), ('b', 'y'), ('c', 'x');
3 affected rows (10.922ms)
sql>select * from testorder order by 1;
+------+------+
| a | b |
+======+======+
| a | z |
| b | y |
| c | x |
+------+------+
3 tuples (1.616ms)
sql>select * from testorder order by 2;

Actual Results:

+------+------+
| a | b |
+======+======+
| a | z |
| b | y |
| c | x |
+------+------+
3 tuples (0.817ms)

Expected Results:

+------+------+
| a | b |
+======+======+
| c | x |
| b | y |
| a | z |
+------+------+

The MAL is identical for the "order by 1" and "order by 2" variants, looks like a caching bug.

I also tested with integer columns: this also exhibited the bug.

Comment 18713

Date: 2013-05-03 13:07:56 +0200
From: MonetDB Mercurial Repository <>

Changeset 1bfb10f341a8 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for bug #3278.

Comment 18784

Date: 2013-06-06 19:26:20 +0200
From: MonetDB Mercurial Repository <>

Changeset 52ed955b70c9 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=52ed955b70c9

Changeset description:

fixed bug #3279 and 3278. We properly look up the order by columns.
And keep table names with all sub-relations.
Fixed bug #3296, ie fixed type checking on table returning functions.