Date: 2011-12-15 16:47:52 +0100
From: George Garbis <>
To: SQL devs <>
Version: -- development
CC: @drstmane

Last updated: 2012-02-29 10:34:28 +0100

Comment 16653

Date: 2011-12-15 16:47:52 +0100
From: George Garbis <>

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/535.1 (KHTML, like Gecko) Ubuntu/11.04 Chromium/14.0.835.202 Chrome/14.0.835.202 Safari/535.1
Build Identifier:

A left join which have only right table columns in join condition seems to produce invalid bat.
Assume we have the tables below:

CREATE TABLE tableA(x integer, y integer);
INSERT INTO tableA values(1,10);
INSERT INTO tableA values(2,20);
CREATE TABLE tableB(x integer, y integer);
INSERT INTO tableB values(1,-10);
INSERT INTO tableB values(2,-20);

and pose the query:
SELECT A.x, A.y, B.x, B.y
FROM tableA A
LEFT JOIN tableB B ON ( B.y < -10 ) ;

the expected results are:
+-------+------+-------+-------+
| x | y | x | y |
+=======+======+=======+=======+
| 1 | 10 | 2 | -20 |
| 2 | 20 | 2 | -20 |
+-------+------+-------+-------+

but mclient prints this message:
TypeException:user.s1_1[36]:'algebra.kdifference' undefined in: _55:any := algebra.kdifference(_54:bat[:oid,:int], _44:bat[:void,:oid])
TypeException:user.s1_1[37]:'algebra.markT' undefined in: _56:any := algebra.markT(_55:any, _21:oid)
TypeException:user.s1_1[38]:'bat.reverse' undefined in: _57:any := bat.reverse(_56:any)
TypeException:user.s1_1[39]:'algebra.join' undefined in: _58:any := algebra.join(_57:any, _20:bat[:oid,:int])
TypeException:user.s1_1[41]:'algebra.leftjoin' undefined in: _60:any := algebra.leftjoin(_59:bat[:oid,:void], _54:bat[:oid,:int])
TypeException:user.s1_1[43]:'bat.append' undefined in: _64:any := bat.append(_61:bat[:oid,:int], _60:any, _65:bit)
TypeException:user.s1_1[44]:'bat.append' undefined in: _66:any := bat.append(_64:any, _58:any, _65:bit)
TypeException:user.s1_1[55]:'algebra.join' undefined in: _80:any := algebra.join(_57:any, _79:bat[:oid,:int])
TypeException:user.s1_1[61]:'algebra.leftjoin' undefined in: _90:any := algebra.leftjoin(_59:bat[:oid,:void], _89:bat[:oid,:int])
TypeException:user.s1_1[63]:'bat.append' undefined in: _92:any := bat.append(_91:bat[:oid,:int], _90:any, _65:bit)
TypeException:user.s1_1[64]:'bat.append' undefined in: _93:any := bat.append(_92:any, _80:any, _65:bit)
TypeException:user.s1_1[70]:'algebra.project' undefined in: _102:any := algebra.project(_57:any, _100:int)
TypeException:user.s1_1[83]:'bat.append' undefined in: _119:any := bat.append(_117:bat[:oid,:int], _102:any, _118:bit)
TypeException:user.s1_1[89]:'algebra.project' undefined in: _126:any := algebra.project(_57:any, _125:int)
TypeException:user.s1_1[96]:'bat.append' undefined in: _133:any := bat.append(_132:bat[:oid,:int], _126:any, _118:bit)
SQLException:SQLengine:Program contains errors

Reproducible: Always

Steps to Reproduce:

1. CREATE TABLE tableA(x integer, y integer);
2. INSERT INTO tableA values(1,10);
3. INSERT INTO tableA values(2,20);
4. CREATE TABLE tableB(x integer, y integer);
5. INSERT INTO tableB values(1,-10);
6. INSERT INTO tableB values(2,-20);
7. SELECT A.x, A.y, B.x, B.y FROM tableA A LEFT JOIN tableB B ON ( B.y < -10 ) ;

Actual Results:

TypeException:user.s1_1[36]:'algebra.kdifference' undefined in: _55:any := algebra.kdifference(_54:bat[:oid,:int], _44:bat[:void,:oid])
TypeException:user.s1_1[37]:'algebra.markT' undefined in: _56:any := algebra.markT(_55:any, _21:oid)
TypeException:user.s1_1[38]:'bat.reverse' undefined in: _57:any := bat.reverse(_56:any)
TypeException:user.s1_1[39]:'algebra.join' undefined in: _58:any := algebra.join(_57:any, _20:bat[:oid,:int])
TypeException:user.s1_1[41]:'algebra.leftjoin' undefined in: _60:any := algebra.leftjoin(_59:bat[:oid,:void], _54:bat[:oid,:int])
TypeException:user.s1_1[43]:'bat.append' undefined in: _64:any := bat.append(_61:bat[:oid,:int], _60:any, _65:bit)
TypeException:user.s1_1[44]:'bat.append' undefined in: _66:any := bat.append(_64:any, _58:any, _65:bit)
TypeException:user.s1_1[55]:'algebra.join' undefined in: _80:any := algebra.join(_57:any, _79:bat[:oid,:int])
TypeException:user.s1_1[61]:'algebra.leftjoin' undefined in: _90:any := algebra.leftjoin(_59:bat[:oid,:void], _89:bat[:oid,:int])
TypeException:user.s1_1[63]:'bat.append' undefined in: _92:any := bat.append(_91:bat[:oid,:int], _90:any, _65:bit)
TypeException:user.s1_1[64]:'bat.append' undefined in: _93:any := bat.append(_92:any, _80:any, _65:bit)
TypeException:user.s1_1[70]:'algebra.project' undefined in: _102:any := algebra.project(_57:any, _100:int)
TypeException:user.s1_1[83]:'bat.append' undefined in: _119:any := bat.append(_117:bat[:oid,:int], _102:any, _118:bit)
TypeException:user.s1_1[89]:'algebra.project' undefined in: _126:any := algebra.project(_57:any, _125:int)
TypeException:user.s1_1[96]:'bat.append' undefined in: _133:any := bat.append(_132:bat[:oid,:int], _126:any, _118:bit)
SQLException:SQLengine:Program contains errors

Expected Results:

+-------+------+-------+-------+
| x | y | x | y |
+=======+======+=======+=======+
| 1 | 10 | 2 | -20 |
| 2 | 20 | 2 | -20 |
+-------+------+-------+-------+

Comment 16973

Date: 2012-02-22 23:51:03 +0100
From: @drstmane

Since this left join is a Cartesian product, consider this work around:

SELECT A.x, A.y, B.x, B.y FROM tableA A, ( select * from tableB B where B.y < -10 ) as B;

Comment 16978

Date: 2012-02-23 14:44:24 +0100
From: @njnes

Changeset 86d94f21e92c 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=86d94f21e92c

Changeset description:

fixed bugs 3034, 2964 2946 and 3017

The rel2bin_join didn't handle a bunch of corner cases