Cannot return ids (or other columns) with returning and get_results when doing a batch insert with SQLite

[ThomasKabelitz]

Rust Version

1.93

Diesel Version

2.3.6

Diesel Features

sqlite, returning_clauses_for_sqlite_3_35

Database Version

sqlite 3.45.1

Operating System Version

Ubuntu 24.04

What happened?

Presently it does not appear to be possible to insert rows in a batch insert to a SQLite database, returning the primary key ID at the same time. Is this a fundamental limitation of SQLite or is this because Diesel does not support it?

I am not sure if this is a bug as #4745 appears to be a similar issue that was apparently fixed in #4616.

I have included the full error and a minimal reproducible example just in case they are of any assistance.

What did you expect to happen?

.

Additional details

Here is a minimal example using the example from the documentation.

CREATE TABLE posts (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  title VARCHAR NOT NULL,
  body TEXT NOT NULL,
  published BOOLEAN NOT NULL DEFAULT 0
)

diesel::table! {
    posts (id) {
        id -> Integer,
        title -> Text,
        body -> Text,
        published -> Bool,
    }
}

pub mod schema;

use diesel::{Connection, RunQueryDsl, SqliteConnection};

fn main() {
    let mut connection = establish_connection();
    insert_many_posts(&mut connection);
}

fn establish_connection() -> SqliteConnection {
    SqliteConnection::establish("./database.sqlite").expect("Failed to connect to database")
}

fn insert_many_posts(connection: &mut SqliteConnection) -> Vec<i32> {
    use crate::schema::posts;

    let new_posts = vec![
        NewPost {
            title: "First Post",
            body: "This is the first post",
            published: true,
        },
        NewPost {
            title: "Second Post",
            body: "This is the second post",
            published: false,
        },
        NewPost {
            title: "Third Post",
            body: "This is the third post",
            published: true,
        },
    ];

    diesel::insert_into(posts::table)
        .values(&new_posts)
        .returning(posts::id)
        .get_results(connection)
        .expect("Failed to insert posts")
}

use diesel::prelude::*;
use crate::schema::posts;

#[derive(Insertable)]
#[diesel(table_name = posts)]
pub struct NewPost<'a> {
    pub title: &'a str,
    pub body: &'a str,
    pub published: bool,
}

Steps to reproduce

See above

Compile time error

error[E0277]: `BatchInsert<Vec<ValuesClause<(DefaultableColumnInsertValue<...>, ..., ...), ...>>, ..., (), false>` is no valid SQL fragment for the `Sqlite` backend
    --> src/main.rs:38:22
     |
  38 |         .get_results(connection)
     |          ----------- ^^^^^^^^^^ the trait `QueryFragment<Sqlite, sqlite::backend::SqliteBatchInsert>` is not implemented for `BatchInsert<Vec<ValuesClause<(DefaultableColumnInsertValue<...>, ..., ...), ...>>, ..., (), false>`
     |          |
     |          required by a bound introduced by this call
     |
     = note: this usually means that the `Sqlite` database system does not support 
             this SQL syntax
help: the following other types implement trait `QueryFragment<DB, SP>`
    --> /home/tommi/.cargo/registry/src/index.crates.io-1949cf8c6b5b557f/diesel-2.3.6/src/query_builder/insert_statement/batch_insert.rs:90:1
     |
  90 | / impl<Tab, DB, V, QId, const HAS_STATIC_QUERY_ID: bool> QueryFragment<DB>
  91 | |     for BatchInsert<V, Tab, QId, HAS_STATIC_QUERY_ID>
  92 | | where
  93 | |     DB: Backend,
  94 | |     Self: QueryFragment<DB, DB::BatchInsertSupport>,
     | |____________________________________________________^ `BatchInsert<V, Tab, QId, HAS_STATIC_QUERY_ID>` implements `QueryFragment<DB>`
...
 101 | / impl<Tab, DB, V, QId, const HAS_STATIC_QUERY_ID: bool>
 102 | |     QueryFragment<DB, sql_dialect::batch_insert_support::PostgresLikeBatchInsertSupport>
 103 | |     for BatchInsert<Vec<ValuesClause<V, Tab>>, Tab, QId, HAS_STATIC_QUERY_ID>
 104 | | where
...    |
 110 | |     ValuesClause<V, Tab>: QueryFragment<DB>,
 111 | |     V: QueryFragment<DB>,
     | |_________________________^ `BatchInsert<Vec<diesel::query_builder::insert_statement::ValuesClause<V, Tab>>, Tab, QId, HAS_STATIC_QUERY_ID>` implements `QueryFragment<DB, PostgresLikeBatchInsertSupport>`
     = note: required for `BatchInsert<Vec<ValuesClause<(DefaultableColumnInsertValue<...>, ..., ...), ...>>, ..., (), false>` to implement `QueryFragment<Sqlite>`
     = note: 1 redundant requirement hidden
     = note: required for `InsertStatement<table, BatchInsert<Vec<ValuesClause<(..., ..., ...), ...>>, ..., (), false>, ..., ...>` to implement `QueryFragment<Sqlite>`
     = note: required for `InsertStatement<table, BatchInsert<Vec<ValuesClause<(..., ..., ...), ...>>, ..., (), false>, ..., ...>` to implement `LoadQuery<'_, SqliteConnection, _>`
note: required by a bound in `get_results`
    --> /home/tommi/.cargo/registry/src/index.crates.io-1949cf8c6b5b557f/diesel-2.3.6/src/query_dsl/mod.rs:1792:15
     |
1790 |     fn get_results<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec<U>>
     |        ----------- required by a bound in this associated function
1791 |     where
1792 |         Self: LoadQuery<'query, Conn, U>,
     |               ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `RunQueryDsl::get_results`
     = note: the full name for the type has been written to '/data/tommi/diesel_test/target/debug/deps/diesel_test-f8d6f16b30645ef0.long-type-7193333247629353182.txt'
     = note: consider using `--verbose` to print the full type name to the console

error[E0271]: type mismatch resolving `<Sqlite as SqlDialect>::InsertWithDefaultKeyword == IsoSqlDefaultKeyword`
    --> src/main.rs:38:22
     |
  38 |         .get_results(connection)
     |          ----------- ^^^^^^^^^^ expected `IsoSqlDefaultKeyword`, found `DoesNotSupportDefaultKeyword`
     |          |
     |          required by a bound introduced by this call
     |
     = note: required for `BatchInsert<Vec<ValuesClause<(DefaultableColumnInsertValue<...>, ..., ...), ...>>, ..., (), false>` to implement `CanInsertInSingleQuery<Sqlite>`
     = note: required for `InsertStatement<table, BatchInsert<Vec<ValuesClause<(..., ..., ...), ...>>, ..., (), false>, ..., ...>` to implement `QueryFragment<Sqlite>`
     = note: required for `InsertStatement<table, BatchInsert<Vec<ValuesClause<(..., ..., ...), ...>>, ..., (), false>, ..., ...>` to implement `LoadQuery<'_, SqliteConnection, _>`
note: required by a bound in `get_results`
    --> /home/tommi/.cargo/registry/src/index.crates.io-1949cf8c6b5b557f/diesel-2.3.6/src/query_dsl/mod.rs:1792:15
     |
1790 |     fn get_results<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec<U>>
     |        ----------- required by a bound in this associated function
1791 |     where
1792 |         Self: LoadQuery<'query, Conn, U>,
     |               ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `RunQueryDsl::get_results`
     = note: the full name for the type has been written to '/data/tommi/diesel_test/target/debug/deps/diesel_test-f8d6f16b30645ef0.long-type-7193333247629353182.txt'
     = note: consider using `--verbose` to print the full type name to the console

Some errors have detailed explanations: E0271, E0277.
For more information about an error, try `rustc --explain E0271`.
error: could not compile `diesel_test` (bin "diesel_test") due to 2 previous errors

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

• This issue can be reproduced on Rust's stable channel. (Please submit the issue in the Rust issue tracker instead if that's not the case)

• This issue can be reproduced without requiring a third party crate