Why does Query builder add `where 1 = 0` to empty `whereIn('col', [])` clause?

Hi,

Just noticed that when I do something like this:

\DB::enableQueryLog();
User::whereIn('id', [])->update(['updated_at' => now()]);
return \DB::getQueryLog();

The actual query is:

update `users` set `updated_at` = ? where 0 = 1

What's the reasoning behind that? Obviously, there's some check whether the passed-in array is empty. What's the benefit of executing a query with where 0 = 1 over not executing it at all?

bobbybouwmann

6 years ago

Best Answer

Level 88

@devk If it would leave of the where statement it would execute the following query

update `users` set `updated_at` = ?

This basically means update my whole database. You really don't want that. So for that a save check is build in to make sure that the query doesn't perform anything.

Your query might exist out of more where statement than just this one. So there would need to be a very specific check to check if this is the only where statement and if the array is empty. And only then not perform the query. We can build this stuff.

However, your log would also show no query. For a lot of people that would be weird, because you're talking to the database but there is no query. So for the users of the framework this is the clearest way of showing how this works.

Let me know if that makes sense!

2 likes

devk

6 years ago

Level 3

Ah yeah, that makes sense. Didn't think about all the possibilities of where this can be used.

I can imagine that it would be extremely hard for the query builder to know whether this is used as it was in my example, or maybe it's part of an orWhere statement, where you can't just not execute the query. And I imagine it would get even harder to predict the effect if it was used in a deeply nested query.

Thanks!

bobbybouwmann

6 years ago

Level 88

It could be a small performance increase if we could skip the query though :P

Please or to participate in this conversation.