[MonetDB-users] Invalid BAT if left join condition has only columns from right table
Hello to all, 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 For ease I attach sql scripts for creating tables and posing the query Best regards, George Garbis
Thanks for the bug report, but can you please enter it in our bugtracker at http://bugs.monedb.org/ ? Thanks in advance. On 2011-12-15 16:03, George Garbis wrote:
Hello to all,
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
For ease I attach sql scripts for creating tables and posing the query
Best regards, George Garbis
------------------------------------------------------------------------------ 10 Tips for Better Server Consolidation Server virtualization is being driven by many needs. But none more important than the need to reduce IT complexity while improving strategic productivity. Learn More! http://www.accelacomm.com/jaw/sdnl/114/51507609/
_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Sjoerd Mullender
participants (2)
-
George Garbis -
Sjoerd Mullender