Date: 2017-08-04 16:01:27 +0200
From: @yzchang
To: SQL devs <>
Version: 11.27.5 (Jul2017-SP1)
CC: @yzchang

Last updated: 2017-10-26 14:01:44 +0200

Comment 25551

Date: 2017-08-04 16:01:27 +0200
From: @yzchang

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/603.3.8 (KHTML, like Gecko) Version/10.1.2 Safari/603.3.8
Build Identifier:

MonetDB seems to be not able to handle user defined AGGREGATE functions with more than 2 parameters. It doesn't recognise such functions as aggregate functions, therefore, it always gives the error "cannot use non GROUP BY column ... in query results without an aggregate function". This is demonstrated by the calls to "aggr3" and "aggr4" in the SELECT queries below.

In addition, the error messages involving AGGR UDFs can also be improved. For instance, if I call an AGGR UDF with the wrong number of parameters (e.g. the queries below calling "aggr2"), I also get the error "cannot use non GROUP BY column ... in query results without an aggregate function". It would be clearer to say something like "Aggregate function <...> not found".

Reproducible: Always

Steps to Reproduce:

Run the following queries:

CREATE AGGREGATE aggr2(val INTEGER, val2 INTEGER)
RETURNS INTEGER
LANGUAGE PYTHON {
unique = numpy.unique(aggr_group)
x = numpy.zeros(shape=(unique.size))
for i in range(0, unique.size):
x[i] = numpy.sum(val[aggr_group==unique[i]])
return(x)
};

CREATE AGGREGATE aggr3(val INTEGER, val2 INTEGER, val3 INTEGER)
RETURNS DOUBLE
LANGUAGE PYTHON {
unique = numpy.unique(aggr_group)
x = numpy.zeros(shape=(unique.size))
for i in range(0, unique.size):
x[i] = numpy.sum(val[aggr_group==unique[i]])
return(x)
};

CREATE AGGREGATE aggr4(val INTEGER, val2 INTEGER, val3 INTEGER, val4 INTEGER)
RETURNS DOUBLE
LANGUAGE PYTHON {
unique = numpy.unique(aggr_group)
x = numpy.zeros(shape=(unique.size))
for i in range(0, unique.size):
x[i] = numpy.sum(val[aggr_group==unique[i]])
return(x)
};

CREATE TABLE grouped_ints (value INTEGER, groupnr INTEGER, dbvalue double);
INSERT INTO grouped_ints VALUES (1, 0, 0.11), (2, 1, 0.22), (3, 0, 0.33), (4,1, 0.44), (5,0, 0.55);

SELECT groupnr, aggr3(value, value, value) FROM grouped_ints GROUP BY groupnr;
SELECT groupnr, aggr4(value, value, value, value) FROM grouped_ints GROUP BY groupnr;

SELECT groupnr, aggr2(value) FROM grouped_ints GROUP BY groupnr;
SELECT groupnr, aggr2(value, value, value) FROM grouped_ints GROUP BY groupnr;

DROP TABLE grouped_ints;
DROP AGGREGATE aggr2;
DROP AGGREGATE aggr3;
DROP AGGREGATE aggr4;

Actual Results:

All 4 SELECT queries give the error "cannot use non GROUP BY column ... in query results without an aggregate function"

Expected Results:

The first 2 SELECT queries using "aggr3" and "aggr4" should give the following results:

+---------+------+
| groupnr | L3 |
+=========+======+
| 0 | 9 |
| 1 | 6 |
+---------+------+

The last 2 SELECT queries should give errors such as:
no such aggregate function 'aggr2(integer)'
no such aggregate function 'aggr2(integer, integer, integer)'

Comment 25552

Date: 2017-08-04 16:41:05 +0200
From: MonetDB Mercurial Repository <>

Changeset 078c96c28337 made by Jennie Zhang y.zhang@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=078c96c28337

Changeset description:

Added tests for Bug #6385

Comment 25553

Date: 2017-08-07 08:57:45 +0200
From: MonetDB Mercurial Repository <>

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

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=517019db709e

Changeset description:

fixes for bug #6385, ie check for a aggregate earlier (also improved the
error in case of a missing aggregate function)

Comment 25556

Date: 2017-08-07 16:06:13 +0200
From: @yzchang

Works for me now, closing