An SQL query with OFFSET/FETCH is returning unexpected results-CodePudding

I have an SQL Server 2019 database table named User with 1,000 rows as follows:

I am having a hard time understanding how this SELECT query with OFFSET/FETCH is returning unexpected results:

SELECT * 
FROM [User]
WHERE (([NameGiven] LIKE '%1%') 
       OR ([NameFamily] LIKE '%2%'))
ORDER BY [Id] ASC 
    OFFSET 200 ROWS FETCH NEXT 100 ROWS ONLY;

Query results:

The results range from 264 to 452 with a total of 100 rows. Why would the records 201, 211, etc. not show up? Am I wrong in my expectations or is there a mistake in the query criteria?

If I remove the OFFSET/FETCH options from the ORDER BY clause, the results are as expected. That makes me think that the WHERE clause is not the problem.

Any advice would be appreciated.

CodePudding user response：

The problem is that you expect the offset to happen before the filter but in actuality it doesn't happen until after the filter. Think about a simpler example where you want all the people named 'sam' and there are more people named 'sam' than your offset:

CREATE TABLE dbo.foo(id int, name varchar(32));

INSERT dbo.foo(id, name) VALUES
(1, 'sam'),
(2, 'sam'),
(3, 'bob'),
(4, 'sam'),
(5, 'sam'),
(6, 'sam');

If you just say:

SELECT id FROM dbo.foo WHERE name = 'sam';

You get:

If you then add an offset of 3,

-- this offsets 3 rows _from the filtered result_,
-- not the full table

SELECT id FROM dbo.foo 
WHERE name = 'sam'
ORDER BY id
OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY;

You get:

5
6

It takes all the rows that match the filter, then skips the first three of those filtered rows (1,2,4) - not 1,2,3 like your question implies that you expect.

Example db<>fiddle

Going back to your case in the question, you are filtering out rows like 77 and 89 because they don't contain a 1 or a 2. So the offset you asked for is 200, but in terms of which rows that means, the offset is actually more like:

200 PLUS the number of rows that *don't* match your filter
         until you hit the 200th row that *does*

You could try to force the filter to happen after, e.g.:

;WITH u AS 
(
  SELECT * 
  FROM [User]
  ORDER BY [Id]
  OFFSET 200 ROWS FETCH NEXT 100 ROWS ONLY
)
SELECT * FROM u
  WHERE (([NameGiven] LIKE '%1%') 
     OR ([NameFamily] LIKE '%2%'))
ORDER BY [Id]; -- yes you still need this one

...but then you would almost certainly never get 100 rows in each page because some of those 100 rows would then be removed by the filter. I don't think this is what you're after.