Description: ------------ MDB2_Driver_mysql assumes LIMIT always comes last. however there are statements that must come after LIMIT: PROCEDURE, INTO OUTFILE/DUMPFILE, FOR UPDATE and LOCK IN SHARE MODE. see http://dev.mysql.com/doc/refman/5.0/en/select.html for more details the following patch fixes the problem for FOR UPDATE and LOCK IN SHARE MODE, so those two always come after LIMIT. i didn't include support for PROCEDURE and INTO OUTFILE because it would involve a very loooooooong regex and i'm not quite familiar with either PROCEDURE or INTO OUTFILE. ps. mysqli also suffers the same problem Test script: --------------- Index: MDB2/Driver/mysql.php =================================================================== --- MDB2/Driver/mysql.php (revision 88) +++ MDB2/Driver/mysql.php (working copy) @@ -667,10 +667,18 @@ if (substr($query, -1) == ';') { $query = substr($query, 0, -1); } + $after = ''; + if (preg_match('/(\s+FOR\s+UPDATE\s*)$/i', $query, $matches)) { + $after = $matches[0]; + $query = preg_replace('/(\s+FOR\s+UPDATE\s*)$/i', '', $query); + } elseif (preg_match('/(\s+LOCK\s+IN\s+SHARE\s+MODE\s*)$/i', $query, $matches)) { + $after = $matches[0]; + $query = preg_replace('/(\s+LOCK\s+IN\s+SHARE\s+MODE\s*)$/i', '', $query); + } if ($is_manip) { - return $query . " LIMIT $limit"; + return $query . " LIMIT $limit " . $after; } else { - return $query . " LIMIT $offset, $limit"; + return $query . " LIMIT $offset, $limit " . $after; } } return $query;

Thanks, committed. I've also added a regexp for "INTO OUTFILE" and "INTO DUMPFILE".

There's a problem with assuming that LIMIT always comes last - what about cases where you have a subquery that uses LIMIT? For example, SELECT u.name, (SELECT date FROM diary d WHERE d.id = u.id ORDER BY date DESC LIMIT 1) as date FROM user u (That's a simplified version of a real query which was much more complicated but that's the crux of it right there). Doesn't work with LIMIT in there, works if you take it out.