correlated subselect does not work

[monetdb-team]

Date: 2013-07-03 12:09:33 +0200
From: Dieter <>
To: SQL devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: @njnes

Last updated: 2015-12-16 08:43:31 +0100

Comment 18900

Date: 2013-07-03 12:09:33 +0200
From: Dieter <>

User-Agent: Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; MALC)
Build Identifier:

SELECT Statement ends with error:
[MonetDB][ODBC Driver 11.15.11]cannot use non GROUP BY column 'valid_from' in query results without an aggregate function

select vid, date, cur, val,
(select max(price) from prices p where
p.valid_from =
(select max(q.valid_from) from prices q
where q.valid_from <= v.date
and q.currency = v.cur )
) as Preis
from vouchers v;

Reproducible: Always

Steps to Reproduce:

Here my steps:

create table prices (currency char(3) ,
valid_from char(8), price decimal(15,2));

insert into prices values('USD', '20130101' , 1.2);
insert into prices values('USD', '20130201' , 1.3);

create table vouchers (vid int, date char(8), cur char(3), val decimal(15,2));

insert into vouchers values(1, '20130110' , 'USD' , 1000.0)
insert into vouchers values(1, '20130210' , 'USD' , 2000.0)

select vid, date, cur, val,
(select max(price) from prices p where
p.valid_from =
(select max(q.valid_from) from prices q
where q.valid_from <= v.date
and q.currency = v.cur )
) as Preis
from vouchers v;

Actual Results:

error message:
[MonetDB][ODBC Driver 11.15.11]cannot use non GROUP BY column 'valid_from' in query results without an aggregate function

Expected Results:

There should be 2 records in the result set,
each with the appropriate price.
one record with id=1, date=20130110 cur=USD val=1000 price 1.2
one record with id=2, date=20130210 cur=USD val=2000 price 1.3

The SELECT should give for each voucher the valid price from the table prices
depending on the date of the voucher and the valid_from date of each price.

Comment 18960

Date: 2013-07-31 11:36:48 +0200
From: @njnes

fixed, by a large rewrite of the parser. We now handle many more correlated cases in a cleaner way (using a relational apply operator)

Comment 21668

Date: 2015-12-16 08:43:31 +0100
From: MonetDB Mercurial Repository <>

Changeset 02b5c58d1717 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=02b5c58d1717

Changeset description:

removed old hack from merge table optimizer, ie handling group by/group by combinations.
(fixing old bug #3317, which broke after recent fixes)