Date: 2014-08-06 14:31:20 +0200
From: @skinkie
To: SQL devs <>
Version: 11.17.21 (Jan2014-SP3)
CC: @njnes

Last updated: 2014-10-31 14:14:41 +0100

Comment 19962

Date: 2014-08-06 14:31:20 +0200
From: @skinkie

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/37.0.2062.58 Safari/537.36
Build Identifier:

I was unable to make a small example, thus I have exported the column that was causing this. A table with a lot of distinct entries results in one single result (after ordering) while many are expected.

Reproducible: Always

Steps to Reproduce:

Load the table, will be attached. Execute:

select distinct userstopcode from "sys"."test_userstopcode" order by userstopcode asc limit 2;
select distinct userstopcode from "sys"."test_userstopcode" order by userstopcode desc limit 2;

Actual Results:

sql>select distinct userstopcode from "sys"."test_userstopcode" order by userstopcode asc limit 2;
+--------------+
| userstopcode |
+==============+
| null |
+--------------+
1 tuple (49.017ms)
sql>select distinct userstopcode from "sys"."test_userstopcode" order by userstopcode desc limit 2;
+--------------+
| userstopcode |
+==============+
| TZRR |
+--------------+
1 tuple (35.821ms)

Expected Results:

sql>select userstopcode from "sys"."test_userstopcode" group by userstopcode limit 2;
+--------------+
| userstopcode |
+==============+
| null |
| TZRR |
+--------------+
2 tuples (74.285ms)

MonetDB 5 server v11.17.22 (64-bit, 64-bit oids)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2014 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 31.4GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.33 2013-05-28 (compiled with 8.33)
openssl: OpenSSL 1.0.1h 5 Jun 2014 (compiled with OpenSSL 1.0.1h 5 Jun 2014)
libxml2: 2.9.1 (compiled with 2.9.1)
Compiled by: skinkie@bigdata.openebs.nl (x86_64-unknown-linux-gnu)
Compilation: gcc -g -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code
Linking : /usr/x86_64-pc-linux-gnu/bin/ld -m elf_x86_64

Comment 19977

Date: 2014-08-06 18:41:13 +0200
From: @njnes

which version did you test on. I just run a simple test like this on both
stable and default and those run just fine.

Comment 19990

Date: 2014-08-06 20:07:17 +0200
From: @skinkie

My simple tests do not fail either. Thats why I e-mailed the table dump. Currently at Jan2014.

Comment 19997

Date: 2014-08-07 09:58:43 +0200
From: @njnes

the current implementation(s) of firstn and pqueue topn seems to handle multi column topn/firstn okay, but distinct needs a small adjustment. The one but last
multicolumn topn/firstn returns 'atleast' n values (ie maybe larger than the requested number because the next column will determine which are realy needed).
This is not enough for the distinct case, were we need 'N' distinct values.
Doing a distinct before defeats the purpose of the limit, ie will not help performance anymore.

Comment 19998

Date: 2014-08-07 10:45:47 +0200
From: @skinkie

I wondered while making the example for this bug. Why isn't it reproducable with a table with 3 rows?

Comment 20045

Date: 2014-08-13 11:18:21 +0200
From: MonetDB Mercurial Repository <>

Changeset d380de461d7a 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=d380de461d7a

Changeset description:

Test for bug #3527.
It needs to run without --forcemito for the full effect.

Comment 20046

Date: 2014-08-13 11:25:25 +0200
From: MonetDB Mercurial Repository <>

Changeset b373303ee299 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=b373303ee299

Changeset description:

Fix SELECT DISTINCT with ORDER BY and LIMIT.
This fixes bug #3527, but only for the next feature release.
Niels, there is still an issue in sql_gencode.c.  See the TODO comment.

Comment 20047

Date: 2014-08-13 19:54:44 +0200
From: @njnes

fix is in Oct2014 branch

Comment 20386

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

Oct2014 has been released.