Hi George, can you please file a bug report about this issue? Thanks! Stefan On Mon, Feb 20, 2012 at 04:09:54PM +0200, George Garbis wrote:
Hello to all,
I have some problems trying to use subqueries with INNER JOIN.
Assume the following schema:
CREATE TABLE table1 (a INTEGER);
CREATE TABLE table2 (a INTEGER, b INTEGER);
INSERT INTO table1 VALUES(1); INSERT INTO table1 VALUES(2); INSERT INTO table2 VALUES(2, 2);
I pose this query
SELECT * FROM (SELECT a, 2 AS b FROM table1) t1 INNER JOIN table2 t2 ON (t1.b = t2.b);
and I get the following mal errors:
TypeException:user.s5_1[44]:'bat.mirror' undefined in: _62:any := bat.mirror(_61:int) TypeException:user.s5_1[46]:'algebra.join' undefined in: _64:any := algebra.join(_62:any, _63:bat[:oid,:oid]) TypeException:user.s5_1[47]:'algebra.markH' undefined in: _65:any := algebra.markH(_64:any, _66:oid) TypeException:user.s5_1[48]:'algebra.markT' undefined in: _67:any := algebra.markT(_64:any, _66:oid) TypeException:user.s5_1[49]:'bat.reverse' undefined in: _68:any := bat.reverse(_67:any) TypeException:user.s5_1[50]:'algebra.join' undefined in: _69:any := algebra.join(_68:any, _61:int) TypeException:user.s5_1[51]:'algebra.join' undefined in: _70:any := algebra.join(_65:any, _59:bat[:oid,:int]) TypeException:user.s5_1[55]:'algebra.join' undefined in: _77:any := algebra.join(_76:bat[:void,:oid], _65:any) TypeException:user.s5_1[56]:'bat.reverse' undefined in: _78:any := bat.reverse(_77:any) TypeException:user.s5_1[57]:'algebra.markT' undefined in: _79:any := algebra.markT(_78:any, _43:oid) TypeException:user.s5_1[58]:'bat.reverse' undefined in: _80:any := bat.reverse(_79:any) TypeException:user.s5_1[59]:'algebra.leftjoin' undefined in: _81:any := algebra.leftjoin(_80:any, _42:bat[:oid,:int]) TypeException:user.s5_1[64]:'algebra.project' undefined in: _86:any := algebra.project(_81:any, _83:int) TypeException:user.s5_1[65]:'algebra.join' undefined in: _87:any := algebra.join(_86:any, _85:bat[:int,:oid]) TypeException:user.s5_1[66]:'bat.reverse' undefined in: _88:any := bat.reverse(_87:any) TypeException:user.s5_1[67]:'algebra.markT' undefined in: _89:any := algebra.markT(_88:any, _21:oid) TypeException:user.s5_1[68]:'bat.reverse' undefined in: _90:any := bat.reverse(_89:any) TypeException:user.s5_1[69]:'algebra.leftjoin' undefined in: _91:any := algebra.leftjoin(_90:any, _20:bat[:oid,:int]) TypeException:user.s5_1[76]:'algebra.markT' undefined in: _103:any := algebra.markT(_87:any, _101:oid) TypeException:user.s5_1[77]:'bat.reverse' undefined in: _104:any := bat.reverse(_103:any) TypeException:user.s5_1[78]:'algebra.leftjoin' undefined in: _105:any := algebra.leftjoin(_104:any, _81:any) TypeException:user.s5_1[79]:'algebra.project' undefined in: _106:any := algebra.project(_105:any, _96:bte) TypeException:user.s5_1[86]:'algebra.leftjoin' undefined in: _113:any := algebra.leftjoin(_80:any, _59:bat[:oid,:int]) TypeException:user.s5_1[87]:'algebra.leftjoin' undefined in: _114:any := algebra.leftjoin(_104:any, _113:any) 39000!program contains errors
If i don't use a subquery and i first create the table i need, the query executes flawlessly and it returns the expected results.
CREATE TABLE table3 (a INTEGER, b INTEGER);
INSERT INTO table3(a,b) (SELECT a, 2 AS b FROM table1); SELECT * FROM (SELECT a, b FROM table3) t1 INNER JOIN table2 t2 ON (t1.b = t2.b);
Results:
+------+------+------+----+ | a | b | a | b | +==+===+===+===+ | 2 | 2 | 2 | 2 | | 1 | 2 | 2 | 2 | +------+------+------+----+
Can anyone help? Is there something wrong in my first query or it is a bug?
Thank you in advance, George Garbis
------------------------------------------------------------------------------ Try before you buy = See our experts in action! The most comprehensive online learning library for Microsoft developers is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3, Metro Style Apps, more. Free future releases when you subscribe now! http://p.sf.net/sfu/learndevnow-dev2
_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Stefan.Manegold @ CWI.nl | DB Architectures (INS1) | | http://CWI.nl/~manegold/ | Science Park 123 (L321) | | Tel.: +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |