Problem/Motivation
When working with this module, I ran into a few issues where the SQLSRV connection class was not handling RANGE correctly for the MSSQL engine version / compatibility level. For whatever reason, the code base was ignoring the possibility the module might interact with an older DB and expected a particular OFFSET approach.
On my particular problem, I was see an error around this format of a select:
SELECT p.[productName] AS [productName], p.[productTypeId] AS [productTypeId], p.[checkInId] AS [checkInId] FROM [Product] [p] ORDER BY (SELECT NULL) OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
More specifically, the Incorrect syntax near 'OFFSET' error was killing my request.
Proposed resolution
The proposed resolution is to have my range request look something more like this:
SELECT *
FROM (
SELECT p.[productName] AS [productName], p.[productTypeId] AS [productTypeId], p.[checkInId] AS [checkInId],
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
FROM [Product] [p]
) AS sub
WHERE sub.RowNum BETWEEN 11 AND 20;
Or so, this is how the request might be formatted.
What I did was review some of the older code that existed in the code base for version 8.x-2.x:
public function addRangeToQuery($query, $from, $count)
{
if ($from == 0) {
// Easy case: just use a TOP query if we don't have to skip any rows.
$query = preg_replace('/^\s*SELECT(\s*DISTINCT)?/Dsi', 'SELECT$1 TOP(' . $count . ')', $query);
} else {
if ($this->connection->Scheme()->EngineVersionNumber() >= 11) {
if (strripos($query, 'ORDER BY') === false) {
$query = "SELECT Q.*, 0 as TempSort FROM ({$query}) as Q ORDER BY TempSort OFFSET {$from} ROWS FETCH NEXT {$count} ROWS ONLY";
} else {
$query = "{$query} OFFSET {$from} ROWS FETCH NEXT {$count} ROWS ONLY";
}
} else {
// More complex case: use a TOP query to retrieve $from + $count rows, and
// filter out the first $from rows using a window function.
$query = preg_replace('/^\s*SELECT(\s*DISTINCT)?/Dsi', 'SELECT$1 TOP(' . ($from + $count) . ') ', $query);
$query = '
SELECT * FROM (
SELECT sub2.*, ROW_NUMBER() OVER(ORDER BY sub2.__line2) AS __line3 FROM (
SELECT sub1.*, 1 AS __line2 FROM (' . $query . ') AS sub1
) as sub2
) AS sub3
WHERE __line3 BETWEEN ' . ($from + 1) . ' AND ' . ($from + $count);
}
}
return $query;
}
This was quite promising, so I've written a patch to fix the current approach and bring this back in.