Framework: QueryBuilder does not handle leftJoin() with update() correctly

[seanthebean]

• Laravel Version: 5.3.25
• PHP Version: 7.1.1
• Database Driver & Version: postgres 9.6.1

Description:

First of all, it is nice that the QueryBuilder supports using join() with update() queries (correctly adds a "from" list and a "where" clause to the query). However, it does not seem to handle leftJoin() correctly - it produces the exact same query as join(), thus incorrectly excluding rows where the join condition doesn't match any rows in the joined table.

Steps To Reproduce:

1. enable query logging if needed by running DB::enableQueryLog()

2. execute the following two Fluent queries:

   DB::table('table1 as t1')
       ->join('table2 as t2', 't2.table1_id', '=', 't1.id')
       ->join('table3 as t3', 't3.table2_id', '=', 't2.id')
       ->update(['some_column' => 'val']);
   
   DB::table('table1 as t1')
       ->leftJoin('table2 as t2', 't2.table1_id', '=', 't1.id')
       ->leftJoin('table3 as t3', 't3.table2_id', '=', 't2.id')
       ->update(['some_column' => 'val']);
   

3. view the executed queries (e.g., by running DB::getQueryLog())

Notice that both statements produce the exact same query:

update "table1" as "t1" 
set "some_column" = ?
from "table2" as "t2", "table3" as "t3" 
where "t2"."table1_id" = "t1"."id" 
and "t3"."table2_id" = "t2"."id"

(formatted for readability)

This is obviously not a correct left join. I would expect the second Fluent query to at least produce something equivalent to a left join:

update "table1" as "t1" 
set "some_column" = ?
from "table2" as "t2", "table3" as "t3" 
where ("t2"."table1_id" = "t1"."id" or "t2".* is null)
and ("t3"."table2_id" = "t2"."id" or "t3".* is null)

Or better yet, the second left join could be an actual left join:

update "table1" as "t1" 
set "some_column" = ?
from "table2" as "t2" 
left join "table3" as "t3" on "t3"."table2_id" = "t2"."id"
where ("t2"."table1_id" = "t1"."id" or "t2".* is null)

(Postgres does support this, but perhaps not all flavors of SQL do.)

If neither of these options is feasible for some reason, then it would be better for the leftJoin() to fail and throw an exception than to execute an incorrect query.

[elynnaie]

Confirmed same behavior for 5.4.17 by inserting a var_dump($sql) in Illuminate\Database\Query\Builder@update.

[lancepioch]

Does this only occur with Postgres and does it still happen on the lastest 5.6?

[elynnaie]

Brand new 5.6.3 application, no changes from laravel new app whatsoever, driver set to pgsql:

update "table1" as "t1"
set "some_column" = val
from "table2" as "t2", "table3" as "t3"
where "t2"."table1_id" = "t1"."id"
and "t3"."table2_id" = "t2"."id"

So yes, still happens.

Driver set to mysql:

update `table1` as `t1`
left join `table2` as `t2` on `t2`.`table1_id` = `t1`.`id`
left join `table3` as `t3` on `t3`.`table2_id` = `t2`.`id`
set `some_column` = val

Seems to work fine, though I admit that I'm not intimately familiar with MySQL syntax.

[staudenmeir]

This will be fixed in Laravel 6.0: #29393

[driesvints]

Sweet. Thanks!