php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #64531 SQLite3Result::fetchArray runs the query again.
Submitted: 2013-03-27 09:20 UTC Modified: 2018-09-24 15:49 UTC
Votes:38
Avg. Score:4.6 ± 0.7
Reproduced:37 of 37 (100.0%)
Same Version:17 (45.9%)
Same OS:6 (16.2%)
From: phplists at stanvassilev dot com Assigned:
Status: Verified Package: SQLite related
PHP Version: 5.4.13 OS: Windows XP SP3 32bit
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2013-03-27 09:20 UTC] 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.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2013-03-27 09:25 UTC] 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 16:44 UTC] 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 16:59 UTC] 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 17:23 UTC] 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...
 [2014-12-27 11:21 UTC] 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;
        }
    }
 [2015-07-18 13:49 UTC] dupa at dupa dot com
Same problem here, duplicate inserts with "query" method.
 [2016-06-27 15:43 UTC] cmb@php.net
-Status: Open +Status: Verified
 [2016-06-27 15:43 UTC] cmb@php.net
Confirmed: <https://3v4l.org/MMGio>. And yes, this is a bug, and
not a documentation problem (nonetheless, DDL and DML statements
should use ::exec()).
 [2016-06-27 18:07 UTC] cmb@php.net
-Summary: SQLite3's SQLite3Result::fetchArray runs the query again. +Summary: SQLite3Result::fetchArray runs the query again.
 [2018-01-31 16:37 UTC] thomas dot loch at fusion-core dot net
I've just encountered this issue on Linux and Version 5.6.30 while using a similar generic wrapper around prepare/bind*/execute(). Is there still intent to fix this after five years?
 [2018-09-24 15:49 UTC] cmb@php.net
While the fix for SQLite3::query() is trivial, a general fix for
SQLite3Stmt::execute() is impossible, since it is allowed to
::execute() the same statement multiple times, which might trigger
sqlite3_reset()s at unexpected times (see, for instance, bug
#73530).
 
PHP Copyright © 2001-2018 The PHP Group
All rights reserved.
Last updated: Fri Nov 16 11:01:25 2018 UTC