where subquery bug

[emiliogrv]

• Laravel Version: 7.28.3
• PHP Version: 7.3.14
• Database Driver & Version: MySQL 5.7.22

Description:

After upgrade from L6 to L7 some queries begun to fail

Steps To Reproduce:

code:

                 ActionLog:: /*...*/
                 ->where(function ($query) {
                     $query
                         ->where('action', 'ended')
                         ->orWhere('action', 'failed');
                 })->get();

result:

SQLSTATE[HY000]: General error: 1096 No tables used (SQL: select exists(select * from `action_logs` where `user_id` = 903db834-1484-4461-abec-23b17645ec2c and `id` > 17 and (select * where `action` = ended or `action` = failed) is null order by `id` desc) as `exists`)

work around:

ActionLog:: /*...*/
->whereRaw("(action = 'ended' OR action = 'failed')")->get();

//--------------
code:

                 ActionLog:: /*...*/
                 ->where(function ($q) {
                     $q
                         ->where('description', 'like', '%Wizard ended')
                         ->orWhere('description', 'like', '%Wizard failed');
                 })->get();

result:

SQLSTATE[HY000]: General error: 1096 No tables used (SQL: select count(*) as aggregate from `action_logs` where (select * where `description` like %Wizard ended or `description` like %Wizard failed) is null)

work around:

ActionLog:: /*...*/
->whereRaw("(description like '%Wizard ended' OR description like '%Wizard failed')")->get();

Eloquent call without removing any lines.

first one:

ActionLog::where('user_id', $this->userId)
                ->where('id', '>', $start->id)
                ->whereRaw("(action = 'ended' OR action = 'failed')")
                // TODO: review this after upgrade laravel builder
                // ActionLog::->where(function ($query) {
                //     $query
                //         ->where('action', 'ended')
                //         ->orWhere('action', 'failed');
                // })
                ->latest('id')
                ->exists();

second one:

    public function index(IndexRequest $request)
    {
        $userId = request('user_id');
        $logId = request('log_id');

        $log = ActionLog::select(
            'id',
            'user_id',
            'action',
            'created_at',
            'description'
        )
            ->search(request('search'))
            ->when($userId, function ($q, $userId) {
                // Wizard logs by users
                $q->where('user_id', $userId);
            })
            ->when($userId && !$logId, function ($q) {
                $q->whereRaw(
                    "(description like '%Wizard ended' OR description like '%Wizard failed')"
                );
                // TODO: review this after upgrade laravel builder
                // $q->where(function ($q) {
                //     $q
                //         ->where('description', 'like', '%Wizard ended')
                //         ->orWhere('description', 'like', '%Wizard failed');
                // });
            })
            ->when($logId, function ($q, $logId) {
                $start = ActionLog::select('id')
                    ->where('id', '<=', $logId)
                    ->where('description', 'like', '%Wizard started')
                    ->orderBy('id', 'desc')
                    ->first();

                if ($start) {
                    // Wizard logs details by users by log id
                    $q->whereBetween('id', [$start->id, $logId]);
                } else {
                    $q->where('id', $logId);
                }
            })
            ->orderBy(request('order_by', 'id'), request('order', 'asc'))
            ->paginate(request('paginate', 15));

        return ActionLogResource::collection($log);
    }

NOTE: after removing elquence trait the queries work properly

[marnickmenting]

I got the same problem after upgrading to L7, thanks for the workaround @emiliogrv !

[C10ne]

@jarektkaczyk This behaviour is introduced with addition of where subqueries Subquery Where Clauses I don't have much time on my hands at the moment to make a better solution, but I could confirm that this change in https://github.com/jarektkaczyk/hookable/blob/master/src/Builder.php is a quick fix:

    {
        if (!in_array(strtolower($operator), $this->operators, true) && !($column instanceof Closure)) {
            list($value, $operator) = [$operator, '='];
        }

        $bag = $this->packArgs(compact('column', 'operator', 'value', 'boolean'));

        return $this->callHook(__FUNCTION__, $bag);
    }

[marnickmenting]

Related to jarektkaczyk/eloquence#261, and pull request jarektkaczyk/hookable#27