[MonetDB-users] Subquery with INNER JOIN

20 Feb 2012

      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

George Garbis

Stefan Manegold

tags

participants (2)