Bug: [SQLSRV] Incorrect syntax near 'OFFSET'

[demirkaric]

PHP Version

7.4

CodeIgniter4 Version

4.1.8

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Windows, Linux

Which server did you use?

apache

Database

MSSQL Server 2008

What happened?

In the documentation it's stated: MSSQL via the SQLSRV driver (version 2005 and above only) This is wrong statement, let's check the next code sample in the Steps to Reproduce.

Steps to Reproduce

$model->builder("tblTest")->select("Barcode")->where("Barcode", $data->Barcode)->first();

This produces next query:

SELECT "Barcode"
FROM "DB"."dbo"."tblTest"
WHERE "Barcode" = '1AP06456 R2104604 6 1'
ORDER BY (SELECT NULL)  OFFSET 0  ROWS FETCH NEXT 1 ROWS ONLY 

Expected Output

Since we use version MSSQL 2008 and OFFSET is supported from version 2012 and above. This should be fixed or the documentation should be corrected.

Anything else?

No response

[sclubricants]

Seems we could or should use LIMIT 1 when using first()

[visserrobin]

MS SQL Server doesn't suppor the LIMIT keyword. Documentation suggests to use TOP 1 * from db_table but OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY should then be removed completely

[kenjis]

@demirkaric Do you still use MSSQL Server 2008? https://learn.microsoft.com/en-us/lifecycle/products/microsoft-sql-server-2008

[kenjis]

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS } Specifies the number of rows to skip before it starts to return rows from the query expression. The value can be an integer constant or expression that is greater than or equal to zero.

Applies to: SQL Server 2012 (11.x) and later and Azure SQL Database.s https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver16&redirectedfrom=MSDN#arguments

[kenjis]

Microsoft SQL Server 2012 also reached the end of life: Jul 12, 2022 https://learn.microsoft.com/en-us/lifecycle/products/microsoft-sql-server-2012

[kenjis]

@demirkaric Thank you for reporting. I sent a PR to fix the docs: #8489