Framework: SQL Server sql not including specified limit in delete

[Krisell]

• Laravel Version: 7.9.2
• PHP Version: 7.4.0
• Database Driver & Version: Different, see below

Description:

This is the generated SQL from the following Eloquent chain for different database drivers.

Post::limit(50)->delete();

+--------+-------------------------------------------------------------------------------------+
| driver | sql                                                                                 |
+--------+-------------------------------------------------------------------------------------+
| sqlite | delete from "posts" where "rowid" in (select "posts"."rowid" from "posts" limit 50) |
| mysql  | delete from `posts`  limit 50                                                       |
| sqlsrv | delete from [posts]                                                                 |
| pgsql  | delete from "posts" where "ctid" in (select "posts"."ctid" from "posts" limit 50)   |
+--------+-------------------------------------------------------------------------------------+

In the SQL Server case, the limit is ignored. Looking at the source code, this is probably intentional (compileLimit returns empty string in SqlServerGrammar), so this is perhaps more of question than a bug report. As far as I know, limiting is supported in SQL Server, using the top-command, also for delete statements (for select statements, limit is compiled to top correctly). Perhaps this wasn't the case in old versions of SQL Server?

I completely missed that the limit was not taking effect (yes my tests should have caught that). Would it be possible to output a debug log or similar that the effect is not as intended in this way, or even make it not compile, to avoid the incorrect (?) behavior from slipping through?

I'd be happy to try to make a PR, but I might need some help. Is the compileLimit function the correct place, or are there more parts involved?

Steps To Reproduce:

Write a Query Builder or Eloquent chain with limit and delete, using the sqlsrv driver, and the limit clause is ignored in the compiled sql.

Here's an example which compiles the sql without running it (so no actual sql server is required):

$builder = DB::table((new Post)->getTable());

$sql = $builder->getGrammar()->compileDelete(
    $builder->limit(50)
);

dd($sql);

[driesvints]

We'll need someone with knowledge about SqlServer to confirm and fix this.

[taylorotwell]

Fixed in upcoming patch release. Syntax is documented here:

https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql?view=sql-server-ver15