Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

AGGREGATE UDFs with more than 2 parameters incorrectly processed #6385

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

AGGREGATE UDFs with more than 2 parameters incorrectly processed #6385

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant