Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Invalid BAT if left join condition has only columns from right table #2946

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

Invalid BAT if left join condition has only columns from right table #2946

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant