SQL Server syntax error after upgrade to Laravel 9

[beard7]

• Laravel Version: 9.41.0
• PHP Version: 8.0.18
• Database Driver & Version: ODBC Driver 17 for SQL Server

Description:

Code which worked prior to updrading to Laravel 9 is now producing an error when querying the database.

Steps To Reproduce:

In Laravel 8, this works as expected: $posts = Post::Approved()->latest()->with('category', 'user')->where('inside_track', 0)->skip(4)->get()->groupBy('category_id')

The same code in Laravel 9 results in the following error: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'only'. (SQL: select * from [posts] where [approved_at] is not null and [inside_track] = 0 order by [created_at] desc offset 4 rows fetch next rows only)

Removing the skip(4) fixes the problem, but I need to skip the records.

[github-actions[bot]]

Thank you for reporting this issue!

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!

[hafezdivandari]

According to the following lines, select statement is forced to use skip/offset and take/limit together when ordering. Related to PR #39863

https://github.com/laravel/framework/blob/99263998eb4e4b5cd0d536737c00cb4ba5f36ff0/src/Illuminate/Database/Query/Grammars/SqlServerGrammar.php#L40-L42

Following changes may resolve this issue:

if (! empty($components['orders'])) {
+    $fetchClause = $query->limit ? " fetch next {$query->limit} rows only" : '';
+
+    return parent::compileSelect($query)." offset {$query->offset} rows".$fetchClause;
-    return parent::compileSelect($query)." offset {$query->offset} rows fetch next {$query->limit} rows only";
}

However, this is already fixed/handled in a better way on Laravel 10 via #44937

[driesvints]

Looks like this will be done better in Laravel v10. Thanks

[josuechevezUEES]

Illuminate\ Database\ QueryException

SQL SERVER

PHP 8.2.10 10.22.0 SQLSTATE[42000]: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Sintaxis incorrecta cerca de 'offset'. SELECT * FROM [ ED_Evaluaciones ] ORDER BY [ IdEvaluacion ] DESC OFFSET 10 ROWS FETCH next 11 ROWS ONLY