Edit report at https://bugs.php.net/bug.php?id=64531&edit=1
ID: 64531
Comment by: phplists at stanvassilev 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:
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
...
Previous Comments:
------------------------------------------------------------------------
[2013-03-27 16:44:23] frozenf...@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"
------------------------------------------------------------------------
[2013-03-27 09:20:31] phplists at stanvassilev dot com
Description:
------------
I was quite surprised to find that, but SQLite3 results cause the query to
execute
a second time when you try to fetch from them (after the query() call).
This is not just a harmless performance issue. When the query is an INSERT
query,
it causes duplicate rows, and creates all kinds of other messes.
IMPORTANT: If you perform the same tests with the SQLite3 driver for PDO, it
doesn't have this issue (fetching results won't cause the query to run again).
The
issue is specific to the SQLite3 extension.
Test script:
---------------
EXAMPLE1:
I caught that when I run an INSERT query through a generic routine which always
uses query() and then runs fetchArray() in a loop to see if something was
returned. Naturally INSERT queries return nothing, but trying to fetch on an
empty result set should NOT produce side effects:
$conn = new SQLite3('Log.sqlite', \SQLITE3_OPEN_READWRITE);
$res = $conn->query('INSERT INTO Table VALUES(null, 1, 2, 3'); // Inserts a row
here (the null is an auto-incrementing PK).
$res->fetchArray()); // Inserts the *same* row data as above, again (with the
next auto-incrementing PK number).
$res->fetchArray()); // And yet again...
EXAMPLE2:
Another way to prove that something is fishy, is by registering a PHP function
for use in SQLite3. Let's say we have a table with a column "id", and we have
three rows, with "id" values 1, 2, 3.
function testing($val) {
echo 'Testing with: ' . $val . '<br>';
return true;
}
$conn = new SQLite3('Log.sqlite', \SQLITE3_OPEN_READWRITE);
$conn->createFunction('testing', 'testing', 1);
$res = $conn->query('SELECT * FROM Table WHERE testing(id)'); // "Testing with:
1"
$arr = $res->fetchArray(); // "Testing with: 1" (notice the repetition of 1
with the query above, this shouldn't occur).
$arr = $res->fetchArray(); // "Testing with: 2"
$arr = $res->fetchArray(); // "Testing with: 3"
// At this point the next call to fetchArray() will return false, as it should.
But what's happening internally? Something else:
$arr = $res->fetchArray(); // "Testing with: 1" again. Huh? Why is it running
that again?
$arr = $res->fetchArray(); // "Testing with: 2"
$arr = $res->fetchArray(); // "Testing with: 3"
$arr = $res->fetchArray(); // "Testing with: 1"
$arr = $res->fetchArray(); // "Testing with: 2"
$arr = $res->fetchArray(); // "Testing with: 3"
// ...and so on forever.
Another used has encountered an issue like this over 6 months ago (!) which
means this bug has been around for a while:
http://stackoverflow.com/questions/12981332/php-sqlite3resultfetcharray-re-executes-query
Expected result:
----------------
The SQLite3 extension should execute a query once, and each row of the result
set
should be executed once, like PDO's SQLite3 driver does.
Fetching should cause duplicate INSERTS and twice computed results.
Actual result:
--------------
The SQLite3 extension executes the query again when fetching from the result
set.
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=64531&edit=1
