Date: 2012-05-30 13:18:36 +0200
From: Inho Kim <<georgios.kim>>
To: SQL devs <>
Version: -- development
CC: @bartscheers, @njnes

Last updated: 2012-11-27 15:22:07 +0100

Comment 17306

Date: 2012-05-30 13:18:36 +0200
From: Inho Kim <<georgios.kim>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/536.5 (KHTML, like Gecko) Chrome/19.0.1084.52 Safari/536.5
Build Identifier: MonetDB v11.9.5

sql failed if use scalar subquery with alias

Reproducible: Always

Steps to Reproduce:

create table test1 ( id int, name varchar(20));

create table test2 ( id int, name varchar(20));

select
(select count(id) as cnt1 from test1 ) as c1,
(select count(b.id) as cnt2 from test2 b where a.id = b.id) as c2
from test1 a;

Actual Results:

No error, no output.. just display prompt..

Expected Results:

+----+----+
| L2 | c2 |
+====+====+
+----+----+
0 tuples

case 1:

select
(select count(id) as cnt1 from test1 ),
(select count(b.id) as cnt2 from test2 b where a.id = b.id)
from test1 a;

=> OK

case 2:

select
(select count(id) as cnt1 from test1 ) ,
(select count(b.id) as cnt2 from test2 b where a.id = b.id) as c2
from test1 a;

==> OK

case 3

select
(select count(b.id) as cnt2 from test2 b where a.id = b.id) as c2,
(select count(id) as cnt1 from test1 ) as c1
from test1 a;
==> OK

case 4:
select
(select count(id) as cnt1 from test1 ) as c1,
(select count(b.id) as cnt2 from test2 b where a.id = b.id) as c2
from test1 a;
==> Fail

Comment 17443

Date: 2012-07-11 18:22:59 +0200
From: @njnes

Changeset 9b77e58aa75e 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=9b77e58aa75e

Changeset description:

fixed bug #3093. When we repeat the projection of partial query before the correlated subquery we need to use the aliases of this partial projection.

Comment 17444

Date: 2012-07-11 18:25:50 +0200
From: @njnes

Fixed. Indeed the alias wasn't propagated properly through out the query.

Comment 17643

Date: 2012-08-23 10:14:40 +0200
From: @sjoerdmullender

Jul2012-SP1 has been released.

Comment 18112

Date: 2012-11-27 15:22:07 +0100
From: @bartscheers

Added test, with and without data