INSERT with RETURNING causes duplicate rows
(1) By achernow on 2021-06-13 13:11:30 [source]
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, 'email@example.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,
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 firstname.lastname@example.org 47 2021-06-13 14:54:08 2 Adam Fry email@example.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, 'firstname.lastname@example.org', '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
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