UNION with subqueries

[monetdb-team]

Date: 2014-07-23 14:45:03 +0200
From: Guillaume Savary <<guillaume.savary>>
To: SQL devs <>
Version: 11.17.17 (Jan2014-SP2)
CC: @njnes, richard.monetdb

Last updated: 2014-10-31 14:13:57 +0100

Comment 19918

Date: 2014-07-23 14:45:03 +0200
From: Guillaume Savary <<guillaume.savary>>

User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:30.0) Gecko/20100101 Firefox/30.0
Build Identifier:

As i was querying with some UNIONs of subqueries, I had an incoherent result.
Then i tried to simplify the query as much as possible and finaly we can resume it like this:

SELECT sum(a), sum(a) FROM (( SELECT 1 AS A ) UNION ALL (SELECT 3 AS A)) AS query;
+------+------+
| L1 | L2 |
+======+======+
| 4 | 1 |
+------+------+

Note that you can reproduce it with real tables.

Their are some funny things like a "1*" in the previous colomn seems to fix the issue:

SELECT sum(1*a), sum(a) FROM (( SELECT 1 AS A ) UNION ALL (SELECT 3 AS A)) AS query;
+------+------+
| L1 | L2 |
+======+======+
| 4 | 4 |
+------+------+

but not outside the sum():

SELECT 1*sum(a), sum(a) FROM (( SELECT 1 AS A ) UNION ALL (SELECT 3 AS A)) AS query;
+------+------+
| L2 | L3 |
+======+======+
| 4 | 1 |
+------+------+

And you can crash it with:

SELECT sum(a), 2*sum(a) FROM (( SELECT 1 AS A ) UNION ALL (SELECT 3 AS A)) AS query;
TypeException:user.s54_1[24]:'bat.insert' undefined in: _65:any := bat.insert(_55:bat[:oid,:sht], _63:oid, _62:lng)
program contains errors

Reproducible: Always

Steps to Reproduce:

just copy/paste the queries i descripbed

Expected Results:

trivial

Comment 19931

Date: 2014-07-28 18:49:17 +0200
From: @njnes

Bug in the push group by through union code. Fixed in the stable branch.

Comment 19932

Date: 2014-07-28 18:53:24 +0200
From: MonetDB Mercurial Repository <>

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

Changeset description:

fixed bug #3518. The rel_push_aggr_down didn't handle aliases to other aggregates correctly.

Comment 19975

Date: 2014-08-06 18:12:04 +0200
From: Richard Hughes <<richard.monetdb>>

*** Bug #3526 has been marked as a duplicate of this bug. ***

Comment 20346

Date: 2014-10-31 14:13:57 +0100
From: @sjoerdmullender

Oct2014 has been released.