yiisoft/yii — pull request #1318 by Diego-Rocha (2012-08-31 17:54:49+00:00)
Title: fix to issue #166

Fix wrong offset in SQL Server.

Simple SQL test script:

--test table
CREATE TABLE [test_offset]([id] INTEGER);
--test data
INSERT INTO [test_offset]([id]) VALUES(2);
INSERT INTO [test_offset]([id]) VALUES(4);
INSERT INTO [test_offset]([id]) VALUES(6);
INSERT INTO [test_offset]([id]) VALUES(8);
INSERT INTO [test_offset]([id]) VALUES(10);
INSERT INTO [test_offset]([id]) VALUES(12);
INSERT INTO [test_offset]([id]) VALUES(14);

--which LIMIT = 3
--and OFFSET = 2
--expected values are: 6,8,10

--current implementation
SELECT * FROM (
SELECT TOP 3 * FROM (
SELECT TOP 5 [id] FROM [test_offset]
) as [__inner__]
) as [__outer__];
--returned values: 2,4,6
--issue: wrong range of values

--proposed implementation
SELECT * FROM (
SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS [__RowNumber__],
*
FROM (
SELECT [id] FROM [test_offset]
) as [__inner__]
) as [__outer__]
WHERE [__RowNumber__] BETWEEN 3 AND 5;
--returned values: 6,8,10
--issue: the column "__RowNumber__" is added to result