Bug #64531 [Com]: SQLite3's SQLite3Result::fetchArray runs the query again.

Edit report at https://bugs.php.net/bug.php?id=64531&edit=1 ID: 64531 Comment by: dupa at dupa dot com Reported by: phplists at stanvassilev dot com Summary: SQLite3's SQLite3Result::fetchArray runs the query again. Status: Open Type: Bug Package: SQLite related Operating System: Windows XP SP3 32bit PHP Version: 5.4.13 Block user comment: N Private report: N New Comment: Same problem here, duplicate inserts with "query" method. Previous Comments: ------------------------------------------------------------------------ [2014-12-27 11:21:36] yuri dot kanivetsky at gmail dot com I've run into it trying to do: function sq($query, $params = array()) { if ($params) { $stmt = sdb()->prepare($query); if ( ! $stmt) { die("sqlite: " . sdb()->lastErrorMsg()); } foreach ($params as $param) { $r = call_user_func_array(array($stmt, 'bindValue'), $param); if ( ! $r) { die("sqlite: " . sdb()->lastErrorMsg()); } } $res = $stmt->execute(); if ( ! $res) { die("sqlite: " . sdb()->lastErrorMsg()); } } else { $res = sdb()->query($query); if ( ! $res) { die("sqlite: " . sdb()->lastErrorMsg()); } } $r = array(); while ($row = $res->fetchArray(SQLITE3_ASSOC)) { $r[] = $row; } return $r; } Made it work by wrapping the last part in if ($res->numColumns()) {: ... } if ($res->numColumns()) { $r = array(); while ($row = $res->fetchArray(SQLITE3_ASSOC)) { $r[] = $row; } return $r; } } ------------------------------------------------------------------------ [2013-03-27 17:23:59] phplists at stanvassilev dot com I've been discussing this in #PHP.PECL, with auroraeos and others and basically the problem is caused by the OOP interface in the binding. The binding introduces extra logic so query() in PHP can return a resultset object or false etc. In order to know what to return, query() calls sqlite3_step() which fetches the first row of the result. So far so good. Here's the problem: The binding then *resets* the query so the first call to fetchArray() returns row1 again. This is not library behavior, it's binding behavior that's additional logic. This causes row1 to be computed twice, and queries to run twice etc. The solution for solving this without introducing BC breaks and interface breaks, is for the binding to store the row it fetched during query() with the resultset, as outlined in my previous comment, and return it on first call to fetchArray, without calling step. On subsequent calls to fetchArray(), step is called to fetch the other rows. Either way you look at it, the binding added behavior that isn't in the original library, and this is causing performance issues and side effects. The responsible thing is to keep the PHP OOP interface compatible, but fix the performance issues and side effects. And what I described is how to do it... ------------------------------------------------------------------------ [2013-03-27 16:59:58] phplists at stanvassilev dot com Before people start recommending a documentation "fix", be aware that I have two examples. The first use case is solved by using exec(). The second is solved by nothing, the query is evaluated twice. Those are just two examples demonstrating the same issue. Don't try to fix my examples, try to fix the issue. The culprit seems to be that _step is executed for the query once on query(), but then it's executed all over again on first fetch, starting at row1 again. A proposed solution fixing all side effects would be to run step() on query() and cache the fetched result, then return it on first fetch, then call step() on second fetch etc.: query(...); // call sqlite3_step, save row1 fetchArray(...); // return saved row1 fetchArray(...); // call sqlite3_step, return row2 fetchArray(...); // call sqlite3_step, return row3 fetchArray(...); // call sqlite3_step, return row4 ... ------------------------------------------------------------------------ [2013-03-27 16:44:23] frozenfire@php.net The related source is: https://github.com/php/php- src/blob/master/ext/sqlite3/sqlite3.c#L1725 The solution to this bug might simply have to be a documentation note indicating the SQLite3::exec should be used for inserts instead of SQLite3::query, as ::query will necessarily re-execute to "step" through. ------------------------------------------------------------------------ [2013-03-27 09:25:40] phplists at stanvassilev dot com I hate when that happens, although I guess I'm clear: Typo in Expected Result: "Fetching should cause duplicate"; should be:"Fetching should NOT cause duplicate"; Typo in EXAMPLE2: "Another used has encountered an issue like" should be: "Another user has encountered an issue like" ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at https://bugs.php.net/bug.php?id=64531 -- Edit this bug report at https://bugs.php.net/bug.php?id=64531&edit=1