App_Db_Adapter_Sqlsrv limit function works incorrecly

Jul 13, 2009 at 7:53 AM
Function limit modifies sql query like this:
SELECT * FROM (SELECT TOP $count * FROM (SELECT TOP ($page * $count) MyTable.* FROM MyTable) AS inner_tbl) AS outer_tbl

and this query works incorrectly, it returns first page always.
I modified method and query.

At first, get table name:
preg_match( '/FROM\s(\S+)/', $sql, $matches );

then get primary key for this table:
SELECT     c.name, o.name AS table_name
FROM         sys.all_objects AS o LEFT OUTER JOIN
                      sys.all_columns AS c ON o.object_id = c.object_id LEFT OUTER JOIN
                      sys.index_columns AS ic ON c.column_id = ic.column_id AND ic.object_id = o.object_id LEFT OUTER JOIN
                      sys.indexes AS i ON i.object_id = o.object_id AND ic.index_column_id = i.index_id
WHERE     (i.is_primary_key = 1) and o.name = "MyTable"

at last, modifie main sql query:

$sql = 'declare @position int
                set @position = ' . $offset . '
                
                declare @count int
                set @count = ' . $count . '
                SELECT * FROM ' . $tableName . '
                    where ' . $primaryKey . ' in
                        (SELECT TOP( @position + @count ) ' . $primaryKey . ' FROM ' . $tableName . ' )
                        and ' . $primaryKey . ' not in
                        (SELECT TOP( @position ) ' . $primaryKey . ' FROM ' . $tableName . ' )'

and return sql.


Jul 14, 2009 at 6:30 PM

Thanks for reporting. I'm going to look at it today/tommorow and will get back to you.

Jul 14, 2009 at 8:08 PM

Tried with tests - it somehow works on my machine.

Can you provide exact code sample which coused the problem (then I can add it to tests).

Jul 15, 2009 at 7:49 AM

function limit() gets 3 parameters -

first example: $sql = SELECT MyTable.* FROM MyTable

                     $count = 50

                     $offset = 0

and it returns query -  SELECT * FROM (SELECT TOP 50 * FROM (SELECT TOP 50 MyTable.* FROM MyTable) AS inner_tbl) AS outer_tbl

 

second example: $sql = SELECT MyTable.* FROM MyTable

                         $count = 50

                         $offset = 50

and it returns query -  SELECT * FROM (SELECT TOP 50 * FROM (SELECT TOP 100 MyTable.* FROM MyTable) AS inner_tbl) AS outer_tbl

 

result query gets first page always

Jul 15, 2009 at 12:28 PM

and I think we should ignore limit() function when sql query doesn't contain limits.

Jul 15, 2009 at 8:33 PM

My first though is that your query is invalid.

Limiting in Ms sql is done like this: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=850&lngWId=5. So if you have query like this:

SELECT emp_id,lname,fname FROM employee LIMIT 20,10

you get:

select * from (
 select top 10 emp_id,lname,fname from (
    select top 30 emp_id,lname,fname
    from employee
   order by lname asc
 ) as newtbl order by lname desc
) as newtbl2 order by lname asc
The key element here is sorting - first you sort by desc, select enough rows (offset+limit), then sort asc and select offset.

I coudln't find any other explanation online, but I will try to test ir more and look how it behaves. Clearly problem is sorting, because adapter's limit() method tries to get Order by X and play with asc/desc. And even though your sollution may work, I try to search for better one, because IN () statement is expensive and whole query can become a bottleneck (just a though, haven't tested).

Jul 15, 2009 at 10:06 PM

alexmegus: which version of SQL Server are you using?

Also, can you supply sample PHP code that you are using?

 

Regards,

Rob...

Jul 16, 2009 at 1:11 PM

So, you are right, my query doesn't have ORDER BY command and method limit() doesn't execute code:

$orderby = stristr($sql, 'ORDER BY');
if ($orderby !== false) {
    $sort = (stripos($orderby, ' desc') !== false) ? 'desc' : 'asc';
    $order = str_ireplace('ORDER BY', '', $orderby);
    $order = trim(preg_replace('/\bASC\b|\bDESC\b/i', '', $order));
}

akrabat, I'm using MS SQL 2005


Now I have new trouble :-)
My database have many tables with triggers. Some of triggers have PRINT commands, like this PRINT 'my comment'.
sqlsrv_query method of php_sqlsrv.dll library fails when trigger with comment execute PRINT command
And I can't change triggers...