Using RETURNING Clause in sql causes the insert method to return error

[nimdeveloper]

calling following query using the execute:

INSERT INTO location (name,created_at,updated_at) VALUES (?,?,?)

will return 1 changes , but when I call

INSERT INTO location (name,created_at,updated_at) VALUES (?,?,?) RETURNING id

It returns 0 changes and causes the insert function to return error!

[void-ripper]

Did you give your table a sequence for your primary ?
I had the same error and that was the issue.

CREATE SEQUENCE user_id_sequence START 1;

CREATE TABLE user (
    id INTEGER PRIMARY KEY DEFAULT nextval ('user_id_sequence'),
    pubkey BLOB NOT NULL,
    created DATETIME NOT NULL DEFAULT current_timestamp
);

Remember SQLite just creates a sequence for convinience behind your back for the primary.
Without a sequence for the primary key, the primary key will be NULL and if you try returning the NULL value it will lead to an error.

[nimdeveloper]

Did you give your table a sequence for your primary ? I had the same error and that was the issue.

CREATE SEQUENCE user_id_sequence START 1;

CREATE TABLE user (
id INTEGER PRIMARY KEY DEFAULT nextval ('user_id_sequence'),
pubkey BLOB NOT NULL,
created DATETIME NOT NULL DEFAULT current_timestamp
);

Remember SQLite just creates a sequence for convinience behind your back for the primary. Without a sequence for the primary key, the primary key will be NULL and if you try returning the NULL value it will lead to an error.

Yes I did,

CREATE SEQUENCE location_id_seq START WITH 1 INCREMENT BY 1;

        CREATE TABLE location (
            id INTEGER PRIMARY KEY DEFAULT nextval('location_id_seq'),
            name TEXT NOT NULL,
            created_at TIMESTAMP,
            updated_at TIMESTAMP
        )

The problem is that it returns the location, and it's correct, but it returns wrong number for return. as I mentioned, it works fine when just inserting, but when I try RETURNING it fails.

[void-ripper]

that is strange, maybe it is a duckdb version thing.
i have version 1.3.0 on Arch Linux.
using the freature "bundled" works fine aswell.

this code works:

src/schema.sql

CREATE SEQUENCE location_id_seq START WITH 1 INCREMENT BY 1;
CREATE TABLE location (
  id INTEGER PRIMARY KEY DEFAULT nextval('location_id_seq'),
  name TEXT NOT NULL,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

src/main.rs

use std::time::SystemTime;

fn main() {
  let db = duckdb::Connection::open_in_memory().unwrap();
  let schema = include_str!("schema.sql");

  db.execute_batch(schema).unwrap();

  let sql = "INSERT INTO location (name,created_at,updated_at) VALUES (?,?,?) RETURNING id";

  let now = chrono::Utc::now();
  let id: i64 = db
    .query_row(sql, duckdb::params!["dave", now, now], |r| r.get(0))
    .unwrap();

  println!("dave has id {}", id);
}

output:

dave has id 1

[nimdeveloper]

that is strange, maybe it is a duckdb version thing. i have version 1.3.0 on Arch Linux. using the freature "bundled" works fine aswell.

this code works:

src/schema.sql

CREATE SEQUENCE location_id_seq START WITH 1 INCREMENT BY 1;
CREATE TABLE location (
id INTEGER PRIMARY KEY DEFAULT nextval('location_id_seq'),
name TEXT NOT NULL,
created_at TIMESTAMP,
updated_at TIMESTAMP
);

src/main.rs

use std::time::SystemTime;

fn main() {
let db = duckdb::Connection::open_in_memory().unwrap();
let schema = include_str!("schema.sql");

db.execute_batch(schema).unwrap();

let sql = "INSERT INTO location (name,created_at,updated_at) VALUES (?,?,?) RETURNING id";

let now = chrono::Utc::now();
let id: i64 = db
.query_row(sql, duckdb::params!["dave", now, now], |r| r.get(0))
.unwrap();

println!("dave has id {}", id);
}

output:

dave has id 1

I think you get it wrong, what I mean was that based on the code in the lib, when you call the execute method (I'm not talking about any other methods, just execute for now) when you use RETURNING, the lib returns wrong number on rows changed. See the code below:

You can find it in the crates\duckdb\src\raw_statement.rs file, the RawStatement struct has a method named execute that when you call it, it will return the number if rows changed, for example if you insert 2 rows, it will return 2, if you delete 3 rows, it will return 3. now if you go to the crates\duckdb\src\statement.rs file, and look at the insert method in Statement struct, you can see that it calls the `execute function that I mentioned above, that returns rows changed (so basically if you insert 1 rows, it will return 1 (which we expect to happen) then the

match changes {
            1 => Ok(()),
            _ => Err(Error::StatementChangedRows(changes)),
        }

part detects whether the statement failed or not based on the rows changed, but because the execute returns wrong number of rows changed, it caused the insert method return Errr

[void-ripper]

1. yes, i miss understood the problem.
2. why would you use a returning expression, when you only execute, that is a failure and should fail.
3. in general a error should be returned in Rust, because it is simply the wrong use case for that statement.
4. for the 'insert` method, i would expect that the row id or insert id gets returned !

to make things even more clear:

fn main() {
    let db = duckdb::Connection::open("test.db").unwrap();
    let schema = include_str!("schema.sql");

    db.execute_batch(schema).unwrap();

    let sql = "INSERT INTO location (name,created_at,updated_at) VALUES (?,?,?) RETURNING id";

    let now = chrono::Utc::now();
    let id: i64 = db
        .query_row(sql, duckdb::params!["dave", now, now], |r| r.get(0))
        .unwrap();

    println!("dave has id {}", id);

    let now = chrono::Utc::now();
    let changes = db
        .execute(sql, duckdb::params!["yasmin", now, now])
        .unwrap();

    println!("yasmin has changes {}", changes);

    let now = chrono::Utc::now();
    let mut stmnt = db.prepare(sql).unwrap();
    stmnt.insert(duckdb::params!["veronica", now, now]).unwrap();

    println!("veronica was successfull");
}

output:

dave has id 1
yasmin has changes 0

thread 'main' panicked at src/main.rs:25:57:
called `Result::unwrap()` on an `Err` value: StatementChangedRows(0)
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

The database:

>>> duckdb test.db
DuckDB v1.3.0 (Ossivalis) 71c5c07cdd
Enter ".help" for usage hints.
D select * from location;
┌───────┬──────────┬────────────────────────────┬────────────────────────────┐
│  id   │   name   │         created_at         │         updated_at         │
│ int32 │ varchar  │         timestamp          │         timestamp          │
├───────┼──────────┼────────────────────────────┼────────────────────────────┤
│     1 │ dave     │ 2025-05-28 14:19:10.779046 │ 2025-05-28 14:19:10.779046 │
│     2 │ yasmin   │ 2025-05-28 14:19:10.786836 │ 2025-05-28 14:19:10.786836 │
│     3 │ veronica │ 2025-05-28 14:19:10.794914 │ 2025-05-28 14:19:10.794914 │
└───────┴──────────┴────────────────────────────┴────────────────────────────┘

1. well, i would expect an error returned from the execute statement in Rust, because SQL wants to return a value which, i never checked.
2. you are absolutely correct, the 0 changes are highly missleading, because the action got performed.
3. the insert methos is fubar !! Why have an extra insert statement in the first place if the insert id is not returned. And if it fails, one would not expect the entry to be written in the database.

I had the same error as you, thats why initial answered the post. But i assumed i just use the API wrong and opted out for another approach, which i posted in my first answer.

So yes, this is a very missleading behavior of this Rust crate.
But i think your previous post, @nimdeveloper, and my little example here, should give the crate author a very big clue on what is wrong. I mean, you pointed litterely to the point in the code, where the error happens.