Pagination Errors when Using SQL Server 2008

[travy]

Hi, I am not sure whether this is a glitch with CakePHP Pagination/ORM plugin or simply a misconfiguration in my code as I am still getting used to CakePHP 3. However, I am currently writting a fairly simple software application which uses SQL Server 2008 for the Database backend and IIS Windows 7 Web Server running PHP 5_6_6 x64 Non-Thread Safe. I am trying to output a list of all the users that are stored in the database using the built in Pagination component. Once I've added the component to the UsersController and attempt to pass the paginated results to the view using $this->set('users', $this->paginate()) I receive the error below.

To be sure that this was being caused by the UsersController and not by some code in the view I actually completely erased all the lines of code, to isolate the code, but still received the same error. I've also tested to make sure that my model-controller settings were properly configured by receiving non-paginated results, which worked perfectly. The code for my controller is shown below.

I feel its worth mentioning that I've also tested this out with MySQL and pagination worked which is leading me to believe that its potentially a SQL Server glitch of some sort. Also...(and this is the mind boggling part to me) I've copied the generated SQL queries directly into SQL Server Management Studio and they ran perfectly fine.

Any help on this issue would be greatly appreciated.

[markstory]

Do you happen to have 0 rows in the table?

[travy]

No there are actually 56 rows contained within the table

[markstory]

Darn, well I have a good idea of where this is going wrong. Deep in the guts of the ORM there is a query transformer that converts normal limit/offset queries into sub-queries that are compatible with SQLServer 2008's lack of limit offset. This process binds an additional parameter which is obviously making PDO sad.

[travy]

Ok that seems to make sense being that the error mentions binding a 0 somewhere. Good to know where I should look in case I get the chance to actually debug Cake myself :)

[travy]

So I've managed to find a temporary solution to my problem, albeit inefficient. Basically everything with the Pagination utility was working up to the part where it needed to compute the count() of all rows within my users table, which is the code in the attached picture.

My understanding of this code, is that it attempts to differentiate COMPLEX sql operations from basic operations in order to make the resulting select count(*) ... statement more efficient. For what I am assuming to be the reasons you described above, PDO_SQLSRV does not like the more simple sql query:

SELECT (COUNT(*)) AS [count]
FROM [lgn_User] [Users]

and instead prefers the syntax of the ugly and less efficient:

SELECT (COUNT(*)) AS [count]
FROM (
SELECT [Users].[id] AS [Users__id],
[Users].[username] AS [Users__username],
[Users].[password] AS [Users__password],
[Users].[firstname] AS [Users__firstname],
[Users].[lastname] AS [Users__lastname],
[Users].[branch] AS [Users__branch],
[Users].[email] AS [Users__email],
[Users].[active] AS [Users__active],
[Users].[groupAccess] AS [Users__groupAccess]
FROM [lgn_User] [Users]
) [count_source]

As a work around I simply commented out all of the sql optimization code found in /vendor/cakephp/cakephp/src/ORM/query.php until a patch/update is placed to remedy this.

[markstory]

Thanks for looking at this, I'm planning on getting to this issue later this week.

[markstory]

Pull request up now.