"TypeException: algebra.join undefined" when evaluating EXISTS (SELECT ...) predicate

[monetdb-team]

Date: 2011-11-16 15:03:01 +0100
From: Viktor Rosenfeld <<24hesk>>
To: SQL devs <>
Version: 11.7.5 (Dec2011) [obsolete]
CC: @njnes

Last updated: 2013-06-09 14:17:39 +0200

Comment 16560

Date: 2011-11-16 15:03:01 +0100
From: Viktor Rosenfeld <<24hesk>>

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

The following query results in an error message:

SELECT
count(*)
FROM
_rank AS _rank1,
_rank AS _rank2
WHERE
NOT _rank1.pre = _rank2.pre AND
EXISTS (SELECT pre FROM _rank AS ancestor WHERE
ancestor.pre < _rank1.pre AND _rank1.pre < ancestor.post AND
ancestor.pre < _rank2.pre AND _rank2.pre < ancestor.post);

Reproducible: Always

Steps to Reproduce:

The following table can be used to reproduce the error:

CREATE TABLE _rank (pre INTEGER, post INTEGER);
INSERT INTO _rank VALUES (22, 37);
INSERT INTO _rank VALUES (23, 24);
INSERT INTO _rank VALUES (33, 34);

Actual Results:

The following error message:

TypeException:user.s0_1[77]:'algebra.join' undefined in: _100:any := algebra.join(_99:bat[:oid,:oid], _80:bat[:int,:oid])
SQLException:SQLengine:Program contains errors

Expected Results:

+------+
| L147 |
+======+
| 2 |
+------+
1 tuple

If actual data is used inside the EXISTS clause then the query works, e.g.

SELECT
count(*)
FROM
_rank AS _rank1,
_rank AS _rank2
WHERE
_rank2.pre = 33 AND
NOT _rank1.pre = _rank2.pre AND
EXISTS (SELECT pre FROM _rank AS ancestor WHERE
ancestor.pre < _rank1.pre AND _rank1.pre < ancestor.post AND
ancestor.pre < 33 AND 33 < ancestor.post);

MonetDB is installed from the OS X download package.

mserver5 --version follows:

MonetDB 5 server v11.5.7 "Aug2011-SP2" (64-bit, 64-bit oids)
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2011 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 4.0GiB available memory, 2 available cpu cores
Libraries:
libpcre: 8.11 2010-12-10 (compiled with 8.11)
openssl: OpenSSL 1.0.0d 8 Feb 2011 (compiled with OpenSSL 1.0.0d 8 Feb 2011)
libxml2: 2.7.8 (compiled with 2.7.8)
Compiled by: monetdb-bin-builder (x86_64-apple-darwin9)
Compilation: x86_64-apple-darwin9-gcc -fast -pipe
Linking : /Volumes/scratch/gentoo/x86_64/usr/bin/x86_64-apple-darwin9-ld -Wl,-dead_strip_dylibs

Comment 16561

Date: 2011-11-16 15:06:42 +0100
From: Viktor Rosenfeld <<24hesk>>

Not sure, if this is related, but if I try to rewrite the query as follows:

SELECT
count(*)
FROM
_rank AS _rank1,
_rank AS _rank2
WHERE
NOT _rank1.pre = _rank2.pre AND
EXISTS (SELECT pre FROM _rank AS ancestor WHERE
ancestor.pre < _rank1.pre AND _rank1.pre < ancestor.post
INTERSECT SELECT pre FROM _rank AS ancestor WHERE
ancestor.pre < _rank2.pre AND _rank2.pre < ancestor.post);

I get another error message: SELECT: no such column '_rank1.pre'

Comment 16638

Date: 2011-12-08 17:17:43 +0100
From: @sjoerdmullender

Changeset 0244e96a2c5e made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=0244e96a2c5e

Changeset description:

Added test for bug #2933.

Comment 16700

Date: 2011-12-27 15:28:34 +0100
From: @njnes

Changeset 57700c6efacb 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=57700c6efacb

Changeset description:

fix bug with semijoins. This partialy fixes bug #2933 and bug #2915.

Comment 16810

Date: 2012-01-25 10:43:41 +0100
From: Viktor Rosenfeld <<24hesk>>

The original query now works on the latest Dec2011 release. However the version with INTERSECT now throws a new error message: 21000!zero_or_one: cardinality violation (2>1).

I'm using the following query.

SELECT count(*)
FROM rank AS rank1, rank AS rank2
WHERE
rank1.pre IN (1, 3, 5, 6, 8, 13) AND
rank2.pre IN (1, 3, 5, 6, 8, 13) AND
NOT rank1.pre = rank2.pre AND
EXISTS (
SELECT ancestor.pre
FROM rank AS ancestor
WHERE ancestor.pre < rank1.pre AND rank1.pre < ancestor.post
INTERSECT SELECT ancestor.pre
FROM rank AS ancestor
WHERE ancestor.pre < rank2.pre AND rank2.pre < ancestor.post
);

Comment 18799

Date: 2013-06-09 14:17:39 +0200
From: MonetDB Mercurial Repository <>

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

Changeset description:

fixed (again) bug exists-select.Bug-2933.stable.out