From: phplists at stanvassilev dot com
Operating system: Windows XP SP3 32bit
PHP version: 5.4.13
Package: SQLite related
Bug Type: Bug
Bug description:SQLite3's SQLite3Result::fetchArray runs the query again.
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 bug report at https://bugs.php.net/bug.php?id=64531&edit=1
--
Try a snapshot (PHP 5.4):
https://bugs.php.net/fix.php?id=64531&r=trysnapshot54
Try a snapshot (PHP 5.3):
https://bugs.php.net/fix.php?id=64531&r=trysnapshot53
Try a snapshot (trunk):
https://bugs.php.net/fix.php?id=64531&r=trysnapshottrunk
Fixed in SVN: https://bugs.php.net/fix.php?id=64531&r=fixed
Fixed in release: https://bugs.php.net/fix.php?id=64531&r=alreadyfixed
Need backtrace: https://bugs.php.net/fix.php?id=64531&r=needtrace
Need Reproduce Script: https://bugs.php.net/fix.php?id=64531&r=needscript
Try newer version: https://bugs.php.net/fix.php?id=64531&r=oldversion
Not developer issue: https://bugs.php.net/fix.php?id=64531&r=support
Expected behavior: https://bugs.php.net/fix.php?id=64531&r=notwrong
Not enough info:
https://bugs.php.net/fix.php?id=64531&r=notenoughinfo
Submitted twice:
https://bugs.php.net/fix.php?id=64531&r=submittedtwice
register_globals: https://bugs.php.net/fix.php?id=64531&r=globals
PHP 4 support discontinued: https://bugs.php.net/fix.php?id=64531&r=php4
Daylight Savings: https://bugs.php.net/fix.php?id=64531&r=dst
IIS Stability: https://bugs.php.net/fix.php?id=64531&r=isapi
Install GNU Sed: https://bugs.php.net/fix.php?id=64531&r=gnused
Floating point limitations: https://bugs.php.net/fix.php?id=64531&r=float
No Zend Extensions: https://bugs.php.net/fix.php?id=64531&r=nozend
MySQL Configuration Error: https://bugs.php.net/fix.php?id=64531&r=mysqlcfg
