Date: 2012-06-16 16:25:56 +0200
From: Viktor Rosenfeld <<24hesk>>
To: SQL devs <>
Version: 11.11.5 (Jul2012)
CC: @njnes

Last updated: 2012-07-17 13:46:40 +0200

Comment 17360

Date: 2012-06-16 16:25:56 +0200
From: Viktor Rosenfeld <<24hesk>>

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/536.5 (KHTML, like Gecko) Chrome/19.0.1084.56 Safari/536.5
Build Identifier:

The following query results in the error message "SELECT: identifier 'pre' ambiguous". The error message appears to be triggered by the EXISTS clause.

Query:

WITH
span1 AS ( SELECT pre FROM rank ),
span2 AS ( SELECT pre FROM rank )
SELECT span1.pre AS pre1, span2.pre AS pre2
FROM span1, span2
WHERE EXISTS (
SELECT 1
FROM rank as ancestor
WHERE ancestor.pre < span1.pre
AND span1.pre < ancestor.post
AND ancestor.pre < span2.pre
AND span2.pre < ancestor.post)
AND span1.pre <> span2.pre;

Reproducible: Always

Steps to Reproduce:

Minimal example:

CREATE TABLE rank (pre INTEGER, post INTEGER);
INSERT INTO rank VALUES (1, 6);
INSERT INTO rank VALUES (2, 3);
INSERT INTO rank VALUES (4, 5);

If the pre columns are aliased to unambiguous names the query returns the wrong result. It appears that the EXISTS clause is ignored:

WITH
span1 AS ( SELECT pre AS pre1 FROM rank ),
span2 AS ( SELECT pre AS pre2 FROM rank )
SELECT DISTINCT span1.pre1, span2.pre2
FROM span1, span2
WHERE EXISTS (
SELECT 1
FROM rank as ancestor
WHERE ancestor.pre < span1.pre1
AND span1.pre1 < ancestor.post
AND ancestor.pre < span2.pre2
AND span2.pre2 < ancestor.post)
AND span1.pre1 <> span2.pre2;

Actual Results:

+------+------+
| pre1 | pre2 |
+======+======+
| 1 | 2 |
| 1 | 4 |
| 2 | 1 |
| 2 | 4 |
| 4 | 1 |
| 4 | 2 |
+------+------+

Expected Results:

+------+------+
| pre | pre |
+======+======+
| 2 | 4 |
| 4 | 2 |
+------+------+

The following two rewritten queries produce the expected results. One query omits the WITH clause, the other the EXISTS clause.

SELECT DISTINCT span1.pre, span2.pre
FROM rank AS span1, rank AS span2
WHERE EXISTS (
SELECT ancestor.pre
FROM rank as ancestor
WHERE ancestor.pre < span1.pre
AND span1.pre < ancestor.post
AND ancestor.pre < span2.pre
AND span2.pre < ancestor.post)
AND span1.pre <> span2.pre;

WITH
span1 AS ( SELECT pre FROM rank ),
span2 AS ( SELECT pre FROM rank )
SELECT DISTINCT span1.pre, span2.pre
FROM span1, span2, rank AS ancestor
WHERE ancestor.pre < span1.pre
AND span1.pre < ancestor.post
AND ancestor.pre < span2.pre
AND span2.pre < ancestor.post
AND span1.pre <> span2.pre;

Comment 17422

Date: 2012-07-06 14:42:16 +0200
From: @njnes

Fixed, during the select 1... handling we use span1/span2 from the outer context now (not from the more global with context).

Comment 17425

Date: 2012-07-06 17:12:04 +0200
From: @njnes

Changeset 6a9b850bc58f 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=6a9b850bc58f

Changeset description:

fix bug #3099. ie don't lookup views outside the from.

Comment 17476

Date: 2012-07-17 13:46:40 +0200
From: @grobian

Fix delivered in Jul2012 release