Issue: Joining with no ON condition and no FK.
Author: joaodlf
State: closed
Created: 2018-09-08 21:41:26

(Postgres 10 and peewee version 3.7.0)

To make this simple to test, I've assembled some ready to use SQL:
```
create table table1(
  id serial primary key
);

create table table2(
  id serial primary key
);

create table table3(
  id serial primary key,
  table1_id int,
  table2_id int
);

insert into table1 (id) values (1), (2), (3);
insert into table2 (id) values (1), (2);
insert into table3 (id, table1_id, table2_id) values (1, 1, 1), (2, 1, 2), (3, 2, 1);
```
I want to execute the following query, using Peewee:
```
SELECT t1.id, t2.id, t3.id
FROM table1 t1
CROSS JOIN table2 t2
LEFT JOIN table3 t3
on t3.table1_id = t1.id and t3.table2_id = t2.id
where t3.id is null;
```

Note the CROSS JOIN with no "on" condition. 

I've tried the following:

```
results = (table1.select(table1.id.alias("table1_id"), table2.id.alias("table2_id"), table3.id.alias("table3_id"))
                   .join(table2, JOIN.CROSS)
                   .join(table3, JOIN.LEFT_OUTER, on=((table3.table1_id == table1.id) & (table3.table2_id == table2.id)))
                   .where(table3.id.is_null()))
```

This is returning `ValueError: Unable to find foreign key between <Model: Table1> and <Model: Table2>. Please specify an explicit join condition.`

I've also tried replacing the CROSS JOIN with some raw sql (helps in many other edge cases with peewee):
`.join(SQL("CROSS JOIN table2"))`

This is in turn returning `UnboundLocalError: local variable 'constructor' referenced before assignment`

Is there any way to get this query to work?




Would this query be equivalent?

```python
results = (table1.select(...)
           .from_(table1, table2)
           .join_from(table1, table3, JOIN.LEFT_OUTER, ((table3.table1_id == table1.id) & ...))
           .where(table3.is.is_null()))
```

That produces a different query, and also errors with `invalid reference to FROM-clause entry for table "t1" Hint: There is an entry for table "t1", but it cannot be referenced from this part of the query.`

I've managed to get around it with a raw query.

Well, I get that the SQL is different, but semantically aren't they equivalent? i.e. multiple tables in a FROM clause is the same as a cross join, right?

I'm unsure in this case because postgres returns the error I mentioned, whereas the query in my  initial message returns successfully, with results.

I think you must have a typo or something, because it works fine for me:

```python
from peewee import *


db = SqliteDatabase(':memory:')

class Base(Model):
    class Meta:
        database = db

class A(Base):
    id = AutoField()

class B(Base):
    id = AutoField()

class C(Base):
    id = AutoField()
    a = ForeignKeyField(A)
    b = ForeignKeyField(B)

db.create_tables([A, B, C])

A.insert_many([(1,), (2,), (3,)], fields=[A.id]).execute()
B.insert_many([(1,), (2,)], fields=[B.id]).execute()
C.insert_many([
    (1, 1, 1),
    (2, 1, 2),
    (3, 2, 1)], fields=[C.id, C.a, C.b]).execute()

query = (A
         .select(A.id.alias('aid'), B.id.alias('bid'), C.id.alias('cid'))
         .from_(A, B)
         .join_from(A, C, JOIN.LEFT_OUTER, on=(
             (C.a == A.id) &
             (C.b == B.id)))
         .where(C.id.is_null()))

for row in query.tuples():
    print(row)

print('\n-----\n')

query = ('select a.id, b.id, c.id '
         'from a '
         'cross join b '
         'left join c on c.a_id = a.id and c.b_id = b.id '
         'where c.id is null')
for row in db.execute_sql(query):
    print(row)
```

In both cases it prints:

```
(2, 2, None)
(3, 1, None)
(3, 2, None)

-----

(2, 2, None)
(3, 1, None)
(3, 2, None)
```

> in this case because postgres returns the error I mentioned

You're right -- it worked fine in SQLite but Postgresql does not allow mixing multiple "from"-clause items with a JOIN.

Working up a fix.

Thanks for your patience, I tested on SQLite and assumed it was good-to-go. It's fixed for real now, your original query should be working with the latest commit.

No worries. You're an absolute star for getting on this so quickly!