Date: 2016-02-19 02:30:34 +0100
From: Anthony Damico <>
To: SQL devs <>
Version: 11.21.13 (Jul2015-SP2)
CC: ajdamico, @njnes

Last updated: 2016-06-23 10:24:27 +0200

Comment 21804

Date: 2016-02-19 02:30:34 +0100
From: Anthony Damico <>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:44.0) Gecko/20100101 Firefox/44.0
Build Identifier:

happens in latest monetdblite but not mserver&mclient with Jul2015-SP2

Reproducible: Always

Steps to Reproduce:

library(MonetDB.R) load the MonetDB.R package (connects r to a monet database)
library(MonetDBLite) load MonetDBLite package (creates database files in R)

db <- dbConnect( MonetDBLite() , tempdir() )

dbSendQuery( db , "create table my_table (my_id clob, my_double double)" )
dbGetQuery( db , "SELECT COUNT( DISTINCT my_id ) AS unique_enrollees , QUANTILE( my_double , 0.25 ) AS some_quantile FROM my_table" )

Actual Results:

Error in .local(conn, statement, ...) :
Unable to execute statement 'SELECT COUNT( DISTINCT my_id ) AS unique_enrollees , QUANTILE( my_double , 0.25 ) AS some_quantile F...'.
Server says 'SQLException:SQLparser:Errors encountered in query'.

Expected Results:

Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP2)
Database: MonetDB v11.21.13 (Jul2015-SP2), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>create table my_table (my_id clob, my_double double);
operation successful (144.499ms)
sql>SELECT
more> COUNT( DISTINCT my_id ) AS unique_enrollees ,
more> QUANTILE( my_double , 0.25 ) AS some_quantile
more> FROM my_table;
+------------------+--------------------------+
| unique_enrollees | some_quantile |
+==================+==========================+
| 0 | null |
+------------------+--------------------------+
1 tuple (9.886ms)
sql>

Comment 21809

Date: 2016-02-23 01:18:28 +0100
From: Anthony Damico <>

not just monetdblite--

sql>create table my_table (my_id clob, my_double double);
operation successful (9.317ms)
sql>SELECT COUNT( DISTINCT my_id ) AS unique_enrollees , QUANTILE( my_double , 0.25 ) AS some_quantile FROM my_table;
+------------------+--------------------------+
| unique_enrollees | some_quantile |
+==================+==========================+
| 0 | null |
+------------------+--------------------------+
1 tuple (3.639ms)

However, with te latest version of the default branch (changeset c6a29bdbc3d5),
they fail with the following error (no crash, though):

sql>create table my_table (my_id clob, my_double double);
operation successful (1.789ms)
sql>SELECT COUNT( DISTINCT my_id ) AS unique_enrollees , QUANTILE( my_double , 0.25 ) AS some_quantile FROM my_table;
TypeException:user.s3_1[21]:'aggr.quantile' undefined in: aggr.quantile(X_35:bat[:dbl]);
program contains errors

Comment 21919

Date: 2016-03-16 16:15:55 +0100
From: MonetDB Mercurial Repository <>

Changeset 44b66098ae8a 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=44b66098ae8a

Changeset description:

fixed bug #3927, ie only rewrite distinct in aggregate expressions when
the other aggregates can be split in 2 phases.

Comment 21920

Date: 2016-03-16 16:16:44 +0100
From: @njnes

Fixed, ie don't rewrite the distinct in the count because quantiles cannot be split into to phases.