SQLite3Result::fetchArray runs the query again. [O: Bug #64531 ]

[frikiluser]

Description

Already reported 11 years ago. Here: https://bugs.php.net/bug.php?id=64531

The following code:

<?php

$conn = new SQLite3('Log.sqlite', \SQLITE3_OPEN_READWRITE);

$res = $conn->query('CREATE TABLE myTable (id INTEGER PRIMARY KEY AUTOINCREMENT, a INTEGER, b INTEGER, c INTEGER)');
//$res = $conn->query('DELETE FROM myTable');

$res = $conn->query('INSERT INTO myTable 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...
$res = $conn->query('SELECT COUNT(id) FROM myTable');
$data = $res->fetchArray();

var_dump($data[0]);

Resulted in this output:

int(3)

But I expected this output instead:

int(1)

PHP Version

PHP 8.2

Operating System

Debian

[SakiTakamachi]

@frikiluser

I might fix this in the near future :)

[Ketrupik]

I would very much like this fixed. Another broken use case is running SQLite query like such:

INSERT INTO test (val) VALUES
('aaa'),
('bbb')
RETURNING id;

with a database created like this:

CREATE TABLE test (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
val VARCHAR NOT NULL
);

It will INSERT both rows and then again, when you try to retrieve both ids, thus returning id 3 and 4, instead of 1 and 2.

There is really no way to insert multiple rows from PHP to SQLite database AND at the same time get their ids back, because the function lastInsertRowID() only returns the last one. Sure, you can assume that the ids are in consecutive order ending with the one returned by that function, but I would rather have certainty than assumption. And it will completely fall apart when you introduce unique key to that table and "ON CONFLICT" clause to the INSERT above.

TLDR: INSERT with a RETURNING clause is a query that does yield a result and should be handled as such in the SQLite3::query() PHP function call.