SQL parser has problem with (position of) a scalar subquery in a SELECT-list

[monetdb-team]

Date: 2015-07-09 17:28:22 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.19.15 (Oct2014-SP4)
CC: @njnes

Last updated: 2015-08-28 13:43:08 +0200

Comment 20983

Date: 2015-07-09 17:28:22 +0200
From: Martin van Dinther <<martin.van.dinther>>

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

Query:
SELECT b, (SELECT count(*) FROM t2 AS x WHERE x.a < t2.a), CASE a WHEN b THEN 444 END FROM t2;
fails with a parse error: SELECT: identifier 'b' ambiguous

while the similar queries:
SELECT b, CASE a WHEN b THEN 444 END, (SELECT count() FROM t2 AS x WHERE x.a < t2.a) FROM t2;
and
SELECT b as "b1", (SELECT count() FROM t2 AS x WHERE x.a < t2.a), CASE a WHEN b THEN 444 END FROM t2;
are accepted and processed normally.

Reproducible: Always

Steps to Reproduce:

1. start mserver5 (MonetDB v11.19.15 (Oct2014-SP4))
2. start mclient
3. run SQL commands:
   CREATE TABLE t2 (a int, b int);
   INSERT INTO t2 VALUES (1,2);
   SELECT * FROM t2;
   SELECT b, (SELECT count() FROM t2 AS x WHERE x.a < t2.a), CASE a WHEN b THEN 444 END FROM t2;
   SELECT b, CASE a WHEN b THEN 444 END, (SELECT count() FROM t2 AS x WHERE x.a < t2.a) FROM t2;
   SELECT b as "b1", (SELECT count(*) FROM t2 AS x WHERE x.a < t2.a), CASE a WHEN b THEN 444 END FROM t2;
   DROP TABLE t2;

Actual Results:

bash-4.2$ ./start_mserver5.sh
builtin opt gdk_dbpath = /export/scratch1/dinther/Downloads/INSTALL/var/monetdb5/dbfarm/demo
builtin opt gdk_debug = 0
builtin opt gdk_vmtrim = no
builtin opt monet_prompt = >
builtin opt monet_daemon = no
builtin opt mapi_port = 50000
builtin opt mapi_open = false
builtin opt mapi_autosense = false
builtin opt sql_optimizer = default_pipe
builtin opt sql_debug = 0
cmdline opt mapi_port = 49000
cmdline opt gdk_debug = 10
MonetDB 5 server v11.19.15 "Oct2014-SP4"
Serving database 'demo', using 8 threads
Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked
Found 15.590 GiB available main-memory.
Copyright (c) 1993-July 2008 CWI.
Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Listening for connection requests on mapi:monetdb://127.0.0.1:49000/
MonetDB/GIS module loaded
MonetDB/SQL module loaded

bash-4.2$ mclient -p 49000
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.19.15 (Oct2014-SP4), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE t2 (a int, b int);
operation successful (8.875ms)
sql>INSERT INTO t2 VALUES (1,2);
1 affected row (1.067ms)
sql>SELECT * FROM t2;
+------+------+
| a | b |
+======+======+
| 1 | 2 |
+------+------+
1 tuple (1.234ms)
sql>SELECT b, (SELECT count() FROM t2 AS x WHERE x.a < t2.a), CASE a WHEN b THEN 444 END FROM t2;
SELECT: identifier 'b' ambiguous
sql>SELECT b, CASE a WHEN b THEN 444 END, (SELECT count() FROM t2 AS x WHERE x.a < t2.a) FROM t2;
+------+------+------+
| b | L1 | L3 |
+======+======+======+
| 2 | null | 0 |
+------+------+------+
1 tuple (6.470ms)
sql>SELECT b as "b1", (SELECT count(*) FROM t2 AS x WHERE x.a < t2.a), CASE a WHEN b THEN 444 END FROM t2;
+------+------+-----------------------+
| b1 | L2 | ifthenelse_isnull_=_a |
+======+======+=======================+
| 2 | 0 | null |
+------+------+-----------------------+
1 tuple (5.905ms)
sql>DROP TABLE t2;
operation successful (1.581ms)
sql>\q
bash-4.2$

Expected Results:

No error for the 1st SELECT query: SELECT b, (SELECT count(*) FROM t2 AS x WHERE x.a < t2.a), CASE a WHEN b THEN 444 END FROM t2;
It should produce:
+------+------+-----------------------+
| b | L2 | ifthenelse_isnull_=_a |
+======+======+=======================+
| 2 | 0 | null |
+------+------+-----------------------+
1 tuple (5.905ms)

Comment 21076

Date: 2015-08-01 15:18:09 +0200
From: MonetDB Mercurial Repository <>

Changeset 30d12a4105a0 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=30d12a4105a0

Changeset description:

Bug fixes

Bug #3759 fixed types of returned bats (ie use timestamps instead of lng) in modules/mal/clients.c
Bug #3769 fixed crash (triggered assert) by properly looking for referenced groupby columns in push aggr down
optimizer in rel_optimizer.c
Bug #3760 fixed correlated subquery handling in rel_select.c, ie merge only distinct expressions.
Bug #3761 fixed by properly adding the zero_or_one aggregation only when needed (ie when the cardinality of
the outer query is a column/table, ie when we expect one value per row for the subquery).
Bug #3364 fixed in sql_privileges.c, ie allow set role when role_id == auth_id (ie default role).
Bug #3365 fixed in sql_privileges.c, the sql_grant_table_privs always added 'all' privileges in one go
	       in rel_schema.c properly pass the login_id or role_id based on the 'from current_{role/user}'
		part of the grant statement
Bug #3476 was already fixed by previous revoke / schema fixes.

in gdk/gdk_select.c fixed problem with anti select and nils (stop before first nil instead of last)

Comment 21084

Date: 2015-08-01 16:22:40 +0200
From: MonetDB Mercurial Repository <>

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

Changeset description:

added tests for bugs 3760 and 3761

Comment 21086

Date: 2015-08-01 16:23:05 +0200
From: @njnes

Fixed, ie properly merge unique lists

Comment 21232

Date: 2015-08-28 13:43:08 +0200
From: @sjoerdmullender

Jul2015 has been released.