INSERT with RETURNING causes duplicate rows
(1) By achernow on 2021-06-13 13:11:30 [source]
Problem
Using the php SQLite3 driver (3.35.5), when issuing this command:
CREATE TABLE profile
(
profile_id INTEGER NOT NULL PRIMARY KEY autoincrement,
name VARCHAR(64),
email VARCHAR(255),
age INT NOT NULL DEFAULT 0,
created VARCHAR(20) NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO "profile" ("age", "email", "name")
VALUES (47, 'a@gmail.com', 'Adam Fry')
RETURNING "profile_id", "email";
I get duplicate rows. So if the autoinc seq is at 10, I get a row with 11 and 12 with the same data. If I remove the RETURNING clause, only one record is inserted and the sequence is only incremented once. I tested this to ensure a second insert without returning gave me the next sequence value without gaps.
The example is soo simple, I can't really see what I am doing wrong. I have tracked my code and I am certain I do not issue TWO insert statements.
Thank you, achernow
(2) By Larry Brasfield (larrybr) on 2021-06-13 14:26:54 in reply to 1 [link] [source]
Using your SQL with the v3.35.5 SQLite shell, I get just one row of results, just as we should expect.
I do not know what you, your PHP code, or the PHP driver are doing to produce results which puzzle you, and I am not willing to engage in a guessing session to find out. I doubt that anybody else will be willing either. In other words, I cannot see what you are doing wrong and expect nobody else can.
To get your problem resolved here, it would be better to provide a sequence of SQL statements which replicate your problem in the CLI shell. There is a good chance that, in the course of doing that, you will discover the problem. If not, you will give others here a chance to solve it. Until then, you should expect no replies that resolve it.
Perhaps unrelatedly: I would change the first table column from
profile_id INTEGER NOT NULL PRIMARY KEY autoincrement,
to
profile_id INTEGER PRIMARY KEY,
unless you truly need to avoid ever reusing id values after a row using one of them has been deleted.
(3) By Rado (antlor) on 2021-06-13 14:52:12 in reply to 2 [link] [source]
I have found this also.
I think problem is when a prepare function is executed. I have SQLite manager like program that must detect if statement will return data or not. So I call prepare to get column count. In this case with returning I get the same result (rows inserted twice).
Maybe PHP driver is doing the same.
I did not report this I think no one will care because as you sad in CLI shell (I tested also) this works without a problem. So this is not a bug just should not be used with prepare.
(4) By achernow on 2021-06-13 14:57:35 in reply to 2 [link] [source]
This is all I am doing. This is how to reproduce what I am seeing.
SQLite Shell - 3.35.5 2021-04-19 18:32:05
sqlite> CREATE TABLE profile
(
profile_id INTEGER PRIMARY KEY autoincrement,
name VARCHAR(64),
email VARCHAR(255),
age INT NOT NULL DEFAULT 0,
created VARCHAR(20) NOT NULL DEFAULT CURRENT_TIMESTAMP
);
sqlite> select * from profile;
-- run the below php code
sqlite> select * from profile;
profile_id name email age created
---------- -------- ----------- --- -------------------
1 Adam Fry a@gmail.com 47 2021-06-13 14:54:08
2 Adam Fry a@gmail.com 47 2021-06-13 14:54:08
sqlite>
PHP Code - 7.4.20 w/SQLite 3.35.5
$db = new SQLite3('tests/file.db');
$result = $db->query("INSERT INTO profile (age, email, name) VALUES (47, 'a@gmail.com', 'Adam Fry')
RETURNING profile_id, email");
print_r($result->fetchArray(SQLITE3_ASSOC));
$db->close();
(5) By achernow on 2021-06-13 15:01:26 in reply to 3 [link] [source]
Correct, works from shell. The PHP extension is obviously busted because my PHP code could not get simpler if I really tried.
(6) By Adrian Ho (lexfiend) on 2021-06-13 15:42:23 in reply to 4 [link] [source]
It's a known and VERY old issue, and the current thinking seems to be "CANTFIX due to fundamental design issues, use PDO_SQLite
instead".
In a nutshell, it's fetchArray()
that steps through the query again, so DML statements in query()
will be re-executed. If you want to stick with the SQLite3
class, it looks like you'll need to use exec()
for your INSERT
instead, and forego the use of RETURNING
.