Date: 2011-05-17 03:03:15 +0200
From: me.anonimous
To: SQL devs <>
Version: 11.3.3 (Apr2011-SP1) [obsolete]
CC: @njnes

Last updated: 2011-07-29 10:52:40 +0200

Comment 15814

Date: 2011-05-17 03:03:15 +0200
From: me.anonimous

User-Agent: Opera/9.80 (Windows NT 6.1; U; en) Presto/2.8.131 Version/11.10
Build Identifier:

While sub-query run well in the WHERE clause, they don't seem to run in the FROM clause.
I can't change how the client software ships the query [through JDBC], so suggesting re-writting it is not an option for me.

In addition, the latter form has the advantage of being able to "join" on multiple conditions in an efficient manner.

BTW, I'm running tests against other dBs (Lucid, MySSQL, MSSQL), none of them has problems with the latter query.

I'm using the data from this article:
http://type-exit.org/adventures-with-open-source-bi/2011/03/taking-luciddb-for-a-test-drive/

BTW, I had a very hard time loading the file, in fact could not load the last record. No other DB had that issue. If you know why, please notify about that as well (not related to the bug at all, the query works perfect with 999999 records, but very intriguing)

Reproducible: Always

Steps to Reproduce:

1. Load the data table from above described link

2. try to run the query where the sub-query is in the FROM clause, e.g.:
   SELECT
   SUM(PRICE) as PRICE,
   ITEM,
   CATEGORY,
   CITY,
   REGION,
   COUNTRY
   FROM
   (SELECT MAX(price) as MAXPRICE FROM SALESMART) T,
   SALESMART S
   WHERE T.MAXPRICE = S.PRICE
   GROUP BY
   ITEM,
   CATEGORY,
   CITY,
   REGION,
   COUNTRY;

3. nothing happens

Actual Results:

I get NOTHING when I try it, no error, no warning, no syntax control...

Expected Results:

The same result as when running a subquery in the WHERE clause, e.g.:
SELECT
SUM(PRICE) as PRICE,
ITEM,
CATEGORY,
CITY,
REGION,
COUNTRY
FROM SALESMART S
WHERE S.PRICE IN (SELECT MAX(price) as MAXPRICE FROM SALESMART)
GROUP BY
ITEM,
CATEGORY,
CITY,
REGION,
COUNTRY;

MonetDB 5 server v11.3.1 "Apr2011"
Serving database 'demo', using 2 threads
Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked
Found 3.936 GiB available main-memory.
Copyright (c) 1993-July 2008 CWI.
Copyright (c) August 2008-2011 MonetDB B.V., all rights reserved
Visit http://monetdb.cwi.nl/ for further information
Listening for connection requests on mapi:monetdb://cfc-T61:50000/
MonetDB/SQL module loaded

Comment 15815

Date: 2011-05-17 14:03:32 +0200
From: @njnes

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

Changeset description:

fixed bug #2812

a join with a table and a simple aggregate (single value case) was incorrectly
rewriten in the sql optimizer (because the expression wasn't recognized as a
join expression).

Comment 15816

Date: 2011-05-17 14:05:07 +0200
From: @njnes

fixed. There is indeed a small difference in handling the aggregate in the from or in the in list. The from case hit a bug in recognizing a join expression (in the sql optimizer). This was fixed. Added a test to 2011 bugtracker.

Comment 15824

Date: 2011-05-19 18:34:03 +0200
From: me.anonimous

(In reply to comment 2)

fixed. There is indeed a small difference in handling the aggregate in the from
or in the in list. The from case hit a bug in recognizing a join expression (in
the sql optimizer). This was fixed. Added a test to 2011 bugtracker.

Excellent, thanks for your amazingly quick response!
If I want to test this, where would I be able to find a nightly build (or equivalent)? Does the SP1 version of May 17 (http://dev.monetdb.org/downloads/testing/Windows/Apr2011-SP1/) contain that fix?

Thanks again!

Comment 15825

Date: 2011-05-19 18:40:46 +0200
From: @grobian

unfortunately not, but can you try the nightlies instead?

http://monetdb.cwi.nl/testing/projects/monetdb/testweb/web/40259:b0180df2a35a/

Comment 16000

Date: 2011-07-29 10:52:40 +0200
From: @sjoerdmullender

The Apr2011-SP2 bugfix release is out.