INSERT with RETURNING causes duplicate rows
(1) By achernow on 2021-06-13 13:11:30 [link]
## Problem Using the php SQLite3 driver (3.35.5), when issuing this command: ```sql 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]
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<code> profile_id INTEGER NOT NULL PRIMARY KEY autoincrement, </code>to<code> profile_id INTEGER PRIMARY KEY, </code>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]
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]
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 ```sql 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 ```php $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
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]
It's a [known and VERY old issue](https://bugs.php.net/bug.php?id=64531), and the current thinking seems to be ["CANTFIX due to fundamental design issues, use `PDO_SQLite` instead"](https://github.com/php/php-src/pull/5204). 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`.