Home » Database » MDB2_Driver_mssql » Bug #8206

[2006-07-13 13:48 UTC] ioz at ionosfera dot com

Description:
------------
MSSQL MDB2 doesn't support DISTINCT clause on SELECT statements when using limitQuery function.

If I execute a query like this one:
"SELECT DISTINCT column FROM table"
using limitQuery it generates an error because it adds the TOP cluse before the DISCTINCT.

Test script:
---------------
This new "_modifyQuery" function on mssql driver solves the problem:

function _modifyQuery($query, $is_manip, $limit, $offset)
{
if ($limit > 0) {
$fetch = $offset + $limit;
if (!$is_manip) {
if (preg_match('/^([\s(])*SELECT DISTINCT(?!\s*TOP\s*\()/i', $query)) {
return preg_replace('/^([\s(])*SELECT DISTINCT(?!\s*TOP\s*\()/i', "\\1SELECT DISTINCT TOP $fetch", $query);
} elseif(preg_match('/^([\s(])*SELECT(?!\s*TOP\s*\()/i', $query)) {
return preg_replace('/^([\s(])*SELECT(?!\s*TOP\s*\()/i', "\\1SELECT TOP $fetch", $query);
}
}
}
return $query;
}

[2006-07-13 16:12 UTC] nrf at php dot net

This bug has been fixed in CVS.

If this was a documentation problem, the fix will appear on pear.php.net by the end of next Sunday (CET).

If this was a problem with the pear.php.net website, the change should be live shortly.

Otherwise, the fix will appear in the package's next release.

Thank you for the report and for helping us make PEAR better.