innerJoin and leftOuterJoin silently fail when predicate uses a column without a foreign key constraint

[digital-synapse]

for example given a table "item" and a table "task" with a "itemId"

var item = db.getSchema().table('item');
var task = db.getSchema().table('task');
db.select().from(item).innerJoin(task, task.itemId.eq(item.id))

will fail silently without schemaBuilder.addForeignKey() is it not possible to join on an arbitrary column or am I doing somthing wrong?

[arthurhsu]

Full code? It has nothing to do with addForeignKey().

What's the output of

db.select().from(item).innerJoin(task, task.itemId.eq(item.id)).explain()

explain() will give execution plan and can quickly figure out if something is done wrong.

[freshp86]

@digital-synapse Strange. I received a notification from github with your reply, but the post is not found on this discussion (maybe it was deleted afterwards).

[digital-synapse]

Ok. The problem seems to be related to building queries dynamically. and not related to foreign keys. although I am not sure what I am doing wrong. This works as expected.

    var item = db.getSchema().table('item');
    var task = db.getSchema().table('task');
    var user = db.getSchema().table('user');
    var query = db.select().from(item)

    query = query.innerJoin(task, task['itemId'].eq(item['id']))
    query = query.innerJoin(user, item['userId'].eq(user['id']))

but when i try to execute the joins with the predicates built in advance like this

    var query = db.select().from(table);
    for (var i=0; i< join.length; i++){
        query = query.innerJoin(join[i].table, join[i].predicateleft.eq(join[i].predicateright))            
    }

the result is an error

project()
-select(join_pred(item.id, task.itemId))
--join(type: inner, impl: hash, join_pred(item.userId, user.id))
---cross_product
----table_access(task)
----table_access(user)
---table_access(task)

lovefield.js:2074 Uncaught AssertionError: Assertion failed: Mismatch between join predicate left operand and right relation.

the strange thing is that looking at the data in the join table reveals that the first and seconds queries should be exactly the same.

[freshp86]

Agreed the problem seems to be with the query building part of your code.

Can't re-use the db.select().from() part of a query to build two separate queries. It adds new clauses to the same query. db.select() returns a SelectBuilder instance. Every method you call on this instance (except exec and explain) returns itself, not a clone. Therefore the following code does not do what you expect.

var q1 = db.select().from(item);
var q2 = q1.innerJoin(.....);
q1 === q2 // true!!!

If I understand what you are trying to do, then the correct query building code is as follows

var q1 = db.select().from(item).innerJoin(task, task['itemId'].eq(item['id']));
var q2 = db.select().from(item).innerJoin(user, item['userId'].eq(user['id']));

[digital-synapse]

no i'm not trying to build 2 seperate queries. im trying to build one query with a variable number of joins

[freshp86]

Ok, I now understand what you were trying to do. I can't tell what is wrong without seeing how your join variable looks like. Your query execution plans implies that your table references are wrong, there is no "table_access(item)" anywhere, and "table_access(task)" appears twice, which is wrong.

[digital-synapse]

Not sure why this does not work. task has an itemId and item has a userId

the table variable contains

lf.schema.TableBuilder.generateTableClass_.tableClass (name_: "task")

the join array contains

0: Object
predicateleft: lf.schema.BaseColumn (name_: "userId", table_.name_: "item")
predicateright: lf.schema.BaseColumn (name_: "id", table_.name_: "user")
table: lf.schema.TableBuilder.generateTableClass_.tableClass (name_: "user")

1: Object
predicateleft: lf.schema.BaseColumn (name_: "id", table_.name_: "item")
predicateright: lf.schema.BaseColumn (name_: "itemId", table_.name_: "task")
table: lf.schema.TableBuilder.generateTableClass_.tableClass (name_: "task")

[freshp86]

your table var should be the "item" table not the 'task' table. The join array seems fine.

BTW, I think you would be less confused if you were using the implict join syntax.

db.select().
    from(task, item, user).
    where(lf.op.and(
        task['itemId'].eq(item['id']),
        item['userId'].eq(user['id'])));

[digital-synapse]

oh i see it now. the table variable is being overwritten when i build the tablemap. now i feel silly.

    var tblmap = {};
    var table = db.getSchema().table(this.tableName);
    tblmap[this.tableName] = table;
    for (var i=0; i<this.navigationTables.length; i++){        
        var tableName = this.navigationTables[i];
        var table =  db.getSchema().table(tableName);            
        tblmap[tableName] = table;                        
    }