Offset for sqlsrv breaks queries with row_num column

[dunhamjared]

• Laravel Version: 9.x
• PHP Version: 8.1
• Database Driver & Version: SQLServer

Description

PR #39863 introduces two issues.

If offset is used for SQL Server:

1. You cannot use row_num as a column name or alias
2. An unexpected column row_num is included in the results

Examples

Scenario 1

If you have a row_num column or alias, SQL Server will throw:

SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The column 'row_num' was specified multiple times for 'temp_table'

Scenario 2

If you try to insert the offset results into the same table by using chunk:

db()->connection('db_1')->table('example')->chunk(500, function($chunk){
    db()->connection('db_2')->table('example')->insert($chunk);
});

row_num is added to the results and is an unexpected column, causing SQL server to throw:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'row_num' in 'field list'

[driesvints]

Seems like a PR was sent in so let's see how it goes. Thanks

[dunhamjared]

@driesvints could you reopen the issue since it is still active?

[driesvints]

@dunhamjared it looks like taylor doesn't wants to merge this right now. It's best that you gather some feedback from other SQL Server users first.

[driesvints]

Try to gather some feedback on the draft PR you sent in.

[dunhamjared]

@driesvints will do, thanks!