From: domze dot sa at gmail dot com Operating system: FreeBSD 7 PHP version: 5.2.6 PHP Bug Type: MSSQL related Bug description: Results are modified when passed back to the server
Description: ------------ I have a query here that gives the right results in SQL Management Studio but wrong results in PHP (using mssql libraries under FreeBSD using FreeTDS) The query is as follow: SELECT ROW_NUMBER() OVER (ORDER BY count DESC), s.* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY t.field1), t.blah, ...., (SELECT COUNT(field2) FROM x WHERE x.fieldx=t.fieldx) AS count FROM table t WHERE CONTAINS(t.field3,'"some stuff"') ) AS s In this query, in management studio the results are the desired results however in PHP, the field count is like incremented and doesn't give the right results.. I'm using the following in a CTE because I need double ordering. (that is ordering on field1 and then ordering on count) Using freetds-msdblib-0.64_2 and mssql extensions Wrapping this query in a stored procedures gives back the proper results. Reproduce code: --------------- <?php $query = "WITH CTE AS ( SELECT ROW_NUMBER() OVER (ORDER BY count DESC) rowNumber, s.* FROM ( SELECT ROW_NUMBER() OVER (ORDER BY t.field1) rowNumber2, t.blah, (SELECT COUNT(field2) FROM x WHERE x.fieldx=t.fieldx) AS count FROM table t WHERE CONTAINS(t.field3,'"some stuff"') ) AS s ) SELECT TOP 30 * FROM [CTE] WHERE rowNumber>=1"; $ressource = mssql_query($query); while ($row = _fetch_array($ressource)) { echo "<pre>"; print_r($row); echo "</pre>"; } ?> Expected result: ---------------- management studio: count for many records go from 1 1 1 0 0 0 0 ... Actual result: -------------- count for many records go from 3 3 3 2 2 1 1 0 -- Edit bug report at http://bugs.php.net/?id=46387&edit=1 -- Try a CVS snapshot (PHP 5.2): http://bugs.php.net/fix.php?id=46387&r=trysnapshot52 Try a CVS snapshot (PHP 5.3): http://bugs.php.net/fix.php?id=46387&r=trysnapshot53 Try a CVS snapshot (PHP 6.0): http://bugs.php.net/fix.php?id=46387&r=trysnapshot60 Fixed in CVS: http://bugs.php.net/fix.php?id=46387&r=fixedcvs Fixed in CVS and need be documented: http://bugs.php.net/fix.php?id=46387&r=needdocs Fixed in release: http://bugs.php.net/fix.php?id=46387&r=alreadyfixed Need backtrace: http://bugs.php.net/fix.php?id=46387&r=needtrace Need Reproduce Script: http://bugs.php.net/fix.php?id=46387&r=needscript Try newer version: http://bugs.php.net/fix.php?id=46387&r=oldversion Not developer issue: http://bugs.php.net/fix.php?id=46387&r=support Expected behavior: http://bugs.php.net/fix.php?id=46387&r=notwrong Not enough info: http://bugs.php.net/fix.php?id=46387&r=notenoughinfo Submitted twice: http://bugs.php.net/fix.php?id=46387&r=submittedtwice register_globals: http://bugs.php.net/fix.php?id=46387&r=globals PHP 4 support discontinued: http://bugs.php.net/fix.php?id=46387&r=php4 Daylight Savings: http://bugs.php.net/fix.php?id=46387&r=dst IIS Stability: http://bugs.php.net/fix.php?id=46387&r=isapi Install GNU Sed: http://bugs.php.net/fix.php?id=46387&r=gnused Floating point limitations: http://bugs.php.net/fix.php?id=46387&r=float No Zend Extensions: http://bugs.php.net/fix.php?id=46387&r=nozend MySQL Configuration Error: http://bugs.php.net/fix.php?id=46387&r=mysqlcfg