Why are these two Slick requests not equivalent?

Question

As a result, to make the Slick request more readable , I have this query designer that works

val q = Users.filter(_.id === userId) join People on { case (u, p) => u.personId === p.id } joinLeft Addresses on { case ((u, p), a) => p.addressId === a.id } joinLeft Businesses on { case (((u, p), a), b) => p.businessId === b.id } joinLeft Addresses on { case ((((u, p), a), b), ba) => b.flatMap(_.addressId) === ba.id } map { case ((((u, p), a), b), ba) => (p, a, b, ba) } 

And this one that I thought would be equivalent but not working:

 val q = Users.filter(_.id === userId) join People joinLeft Addresses joinLeft Businesses joinLeft Addresses on { case ((((u, p), a), b), ba) => u.personId === p.id && p.addressId === a.flatMap(_.id) && p.businessId === b.flatMap(_.id) && b.flatMap(_.addressId) === ba.id } map { case ((((u, p), a), b), ba) => (p, a, b, ba) } 

The second seems to return a list of profiles that contains more than the target.

Why aren't they the same?

---

Equivalent SQL (IE is the goal of this construct):

 select p.*, a1.*, b.*, a2.* from Users u innerJoin People p on (u.personId == p.id) leftJoin Addresses a1 on (p.addressId == a1.id) leftJoin Businesses b on (p.businessId == b.id) leftJoin Addresses a2 on ( b.addressId == a2.id) 

Answer 1

The problem is here: slick uses LiteralNode (true) as the default join condition. So the second query will result in something like the following:

  select p.*, a1.*, b.*, a2.* from Users u join People p on 1 = 1 left join Addresses a1 on 1 = 1 left join Businesses b on 1 = 1 left join Addresses a2 on u.personId = p.id and p.addressId = a1.id and p.businessId = b.id and b.addressId = a2.id 

As you can see, all conditions that should be a join condition for each of the joined tables are actually part of the join condition of the last join.

To understand how this will affect the end result, simplify the problem as follows:

 create temporary table A (id int primary key); insert into A values (1), (2); select a1.id, a2.id, a3.id from A a1 join A a2 on 1 = 1 left join A a3 on a1.id = a2.id and a2.id = a3.id; 

In the first connection, a1 and a2 are combined by a condition that is always true, which leads to a temporary result:

 (1, 1) (1, 2) (2, 1) (2, 2) 

Now consider the second connection. We have a1.id = a2.id as part of the join condition, but remember that the join condition is used to determine how to extract rows from table a3, and not to filter the intermediate result of the first join. And we do the left join here, so an extra A3 string of NULL is generated even if the join condition is not met. The end result will be:

 (1, 1, 1) (1, 2, NULL) (2, 1, NULL) (2, 2, 2) 

So, you expect to see much more unexpected results with the columns of the last left joined table, which will be NULL.