Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Bug: [SQLSRV] Incorrect syntax near 'OFFSET' #5663

Closed
demirkaric opened this issue Feb 7, 2022 · 6 comments · Fixed by #8489
Closed

Bug: [SQLSRV] Incorrect syntax near 'OFFSET' #5663

demirkaric opened this issue Feb 7, 2022 · 6 comments · Fixed by #8489
Labels
bug Verified issues on the current code behavior or pull requests that will fix them database Issues or pull requests that affect the database layer documentation Pull requests for documentation only

Comments

@demirkaric
Copy link

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

@demirkaric demirkaric added the bug Verified issues on the current code behavior or pull requests that will fix them label Feb 7, 2022
@kenjis kenjis added the database Issues or pull requests that affect the database layer label Feb 7, 2022
@kenjis kenjis changed the title Bug: [SQL Server]Incorrect syntax near 'OFFSET' Bug: [SQLSRV] Incorrect syntax near 'OFFSET' Feb 9, 2022
@sclubricants
Copy link
Member

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

@visserrobin
Copy link

visserrobin commented Sep 25, 2022

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
Copy link
Member

kenjis commented Sep 27, 2022

@kenjis
Copy link
Member

kenjis commented Jan 31, 2024

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
Copy link
Member

kenjis commented Jan 31, 2024

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 kenjis added the documentation Pull requests for documentation only label Jan 31, 2024
@kenjis
Copy link
Member

kenjis commented Jan 31, 2024

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Verified issues on the current code behavior or pull requests that will fix them database Issues or pull requests that affect the database layer documentation Pull requests for documentation only
Projects
None yet
Development

Successfully merging a pull request may close this issue.

4 participants