SqlServerCompiler Limit generates different query on compile compared to previous versions

[gerneio]

Using the limit example from the docs, and comparing to the online playground tool (see here), the below query generates a different query in the latest release of SqlKata when using SqlServerCompiler. Matter of fact, the change in behavior happened sometime after 2.3.7 (same issue still in v3 beta).

Query:
var query = new Query("Posts").OrderByDesc("Date").Limit(10)

Results (>= 2.3.9):
SELECT * FROM [Posts] ORDER BY [Date] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Expected Results (<= 2.3.7):

SELECT TOP (10) * FROM [Posts] ORDER BY [Date] DESC

The "new" generated query is valid and will run, however my expectation would be to receive the previous generated query over the new one first. Offset/Fetch usage only makes sense when I have a range of records that I want to return (i.e. for paging). For simple cases, I would expect the usage of TOP. Additionally, the docs also dictate that the TOP example is what should be returned for this example query.

[ahmad-moussawi]

Hi @gerneio, the change is documented here https://sqlkata.com/release/v2.3.8#sqlserver-is-now-using-the-new-offset-pagination-by-default

You can use the old behavior by setting new SqlServerCompiler { UseLegacyPagination = true }

[gerneio]

Ah got ya, the correlation wasn't super obvious to me when looking through the docs and changelog. Actually the change you linked to kind of introduced what could be considered a breaking change (i.e. since generated sql of simple query changes between versions), but I understand why it was done. Looks like the docs I linked to could also be updated for slightly better clarification on that first example (i.e. just show legacy & non-legacy outputs).

In my case, there is a sql parser middleware between what I generate and what is actually executed on the actual db server, and the parser can be a little picky about what is allowed (basically just doesn't have 100% compatability with all sql conventions). So I just needed to have a way to have predictable output, so that flag should help I believe.

I think the docs code is hosted on this repo, but couldn't find the playground code. Is that stored separately somewhere? I noticed that it's using an older version of the library, which further lead to more of my confusion.

[ahmad-moussawi]

You are right about the breaking change thing, this should be handled more carefully next time,
The site/playground code is not public yet, and the playground version does not reflect the latest release yet, something should be done here.