SQL query parser fails to parse valid SELECT query with a CASE .. END in it. It fails with parser error: identifier 'x' ambiguous

[monetdb-team]

Date: 2015-09-03 18:32:00 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.21.5 (Jul2015)
CC: @njnes

Last updated: 2015-11-03 10:18:24 +0100

Comment 21246

Date: 2015-09-03 18:32:00 +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:

CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER);
SELECT a, e, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b10 END, a-b FROM t1 ORDER BY 2,4,3,1;

returns ERROR = !SELECT: identifier 'a' ambiguous
See many more examples in the Steps to Reproduce section.

Reproducible: Always

Steps to Reproduce:

1. Start mserver5 (MonetDB 5 server v11.21.6)
2. Start mclient
3. Run following SQL commands:
   CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER);
   INSERT INTO t1(e,c,b,d,a) VALUES(103,102,100,101,104);
   INSERT INTO t1(a,c,d,e,b) VALUES(107,106,108,109,105);
   INSERT INTO t1(e,d,b,a,c) VALUES(110,114,112,111,113);
   INSERT INTO t1(d,c,e,a,b) VALUES(116,119,117,115,118);
   INSERT INTO t1(c,d,b,e,a) VALUES(123,122,124,120,121);
   INSERT INTO t1(a,d,b,e,c) VALUES(127,128,129,126,125);
   INSERT INTO t1(e,c,a,d,b) VALUES(132,134,131,133,130);
   INSERT INTO t1(a,d,b,e,c) VALUES(138,136,139,135,137);

SELECT CASE a+1 WHEN b THEN 111 WHEN c THEN 222 WHEN d THEN 333 WHEN e THEN 444 ELSE 555 END, CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN a<b+3 THEN 333 ELSE 444 END, a+b2+c3+d4, a+b2+c3, c, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b*10 END, abs(b-c) FROM t1 WHERE EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b) OR b>c OR d NOT BETWEEN 110 AND 150 ORDER BY 4,1,5,2,6,3,7;
-- ERROR = !SELECT: identifier 'c' ambiguous

SELECT a, a+b2+c3+d4+e5, c-d, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b10 END, b-c, a+b*2 FROM t1 ORDER BY 6,2,4,5,3,1;
-- ERROR = !SELECT: identifier 'a' ambiguous

SELECT a+b2+c3+d4+e5, CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN a<b+3 THEN 333 ELSE 444 END, a, abs(b-c), a+b2, d, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b*10 END FROM t1 WHERE (e>c OR e<d) OR a>b ORDER BY 4,5,3,7,1,6,2;
-- ERROR = !SELECT: identifier 'a' ambiguous

SELECT a, e, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b10 END, a-b FROM t1 ORDER BY 2,4,3,1;
-- ERROR = !SELECT: identifier 'a' ambiguous

SELECT d, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b10 END, abs(b-c), a+b2+c3+d4+e5, CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN a<b+3 THEN 333 ELSE 444 END, d-e FROM t1 ORDER BY 1,6,2,3,5,4;
-- ERROR = !SELECT: identifier 'd' ambiguous

SELECT a+b2+c3+d4+e5, a, abs(a), a-b, d-e, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b10 END FROM t1 WHERE EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b) AND b>c ORDER BY 4,6,3,1,5,2;
-- ERROR = !SELECT: identifier 'a' ambiguous

SELECT CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN a<b+3 THEN 333 ELSE 444 END, a+b2+c3+d4+e5, a, CASE a+1 WHEN b THEN 111 WHEN c THEN 222 WHEN d THEN 333 WHEN e THEN 444 ELSE 555 END, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b10 END, (SELECT count(*) FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d), d FROM t1 WHERE a>b AND (e>a AND e<b) ORDER BY 7,2,4,6,1,3,5;
-- ERROR = !SELECT: identifier 'a' ambiguous

SELECT e, (SELECT count() FROM t1 AS x WHERE x.b<t1.b), CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b10 END, CASE a+1 WHEN b THEN 111 WHEN c THEN 222 WHEN d THEN 333 WHEN e THEN 444 ELSE 555 END, a-b, (SELECT count() FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d) FROM t1 WHERE a>b AND (c<=d-2 OR c>=d+2) AND c>d ORDER BY 6,5,4,2,3,1;
-- ERROR = !SELECT: identifier 'e' ambiguous

SELECT b, a-b, c, abs(b-c), d-e, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b10 END, b-c FROM t1 WHERE EXISTS(SELECT 1 FROM t1 AS x WHERE x.b<t1.b) ORDER BY 1,6,4,5,2,7,3;
-- ERROR = !SELECT: identifier 'b' ambiguous

SELECT (SELECT count() FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d), b, a, a+b2+c3+d4+e5, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b10 END, CASE a+1 WHEN b THEN 111 WHEN c THEN 222 WHEN d THEN 333 WHEN e THEN 444 ELSE 555 END, a+b2+c*3 FROM t1 WHERE a>b AND (e>c OR e<d) ORDER BY 3,7,2,5,6,4,1;
-- ERROR = !SELECT: identifier 'a' ambiguous

SELECT c-d, a-b, b, b-c, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b10 END, c, a+b*2 FROM t1 ORDER BY 1,5,4,3,2,6,7;
-- ERROR = !SELECT: identifier 'b' ambiguous

SELECT a+b2+c3+d4, a, c-d, abs(b-c), b, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b*10 END FROM t1 WHERE (e>c OR e<d) OR (c<=d-2 OR c>=d+2) ORDER BY 4,3,2,5,1,6;
-- ERROR = !SELECT: identifier 'a' ambiguous

SELECT a, a+b2+c3+d4+e5, b, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b10 END, e, a-b FROM t1 ORDER BY 1,4,5,3,6,2;
-- ERROR = !SELECT: identifier 'a' ambiguous

SELECT d, d-e, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b10 END, a+b2, a+b2+c3+d4+e5, CASE WHEN a<b-3 THEN 111 WHEN a<=b THEN 222 WHEN a<b+3 THEN 333 ELSE 444 END, a+b2+c*3 FROM t1 ORDER BY 3,2,4,5,7,1,6;
-- ERROR = !SELECT: identifier 'd' ambiguous

SELECT a, CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b10 END, (SELECT count() FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d), a+b2+c3+d4, b FROM t1 WHERE c>d OR d>e ORDER BY 2,5,1,3,4;
-- ERROR = !SELECT: identifier 'a' ambiguous

SELECT c, (SELECT count() FROM t1 AS x WHERE x.c>t1.c AND x.d<t1.d), CASE WHEN c>(SELECT avg(c) FROM t1) THEN a2 ELSE b10 END, a+b2+c3+d4 FROM t1 WHERE b>c OR (e>c OR e<d) OR d NOT BETWEEN 110 AND 150 ORDER BY 3,2,1,4;
-- ERROR = !SELECT: identifier 'c' ambiguous

DROP TABLE t1;

Actual Results:

All the SELECT queries have a CASE .. END in the select list and all return an Error like "!SELECT: identifier 'x' ambiguous".

Expected Results:

No parse errors.
The SELECT queries are valid and accepted by other RDBMS.

Comment 21273

Date: 2015-09-19 17:33:39 +0200
From: MonetDB Mercurial Repository <>

Changeset 0bec27aa6d8e 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=0bec27aa6d8e

Changeset description:

fixed bug #3803, ie make sure we add only not yet added projection columns

Comment 21274

Date: 2015-09-19 19:27:25 +0200
From: @njnes

fixed. complex subqueries require a projection of al inner columns, but except those already projected.

Comment 21443

Date: 2015-11-03 10:18:24 +0100
From: @sjoerdmullender

Jul2015 SP1 has been released.