SQLite Forum

INSERT with RETURNING causes duplicate rows
Login

INSERT with RETURNING causes duplicate rows

(1) By achernow on 2021-06-13 13:11:30 [link] [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.

(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.

(4) By achernow on 2021-06-13 14:57:35 in reply to 2 [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();

(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.