Repository: diesel-rs/diesel (language: rust)

Issue:
`belonging_to().inner_join()` and  `.select()` with two-item tuple causes build error on sqlite backend
## Setup

### Versions

- **Rust: 1.78.0**
- **Diesel: 2.2.0**
- **Database: sqlite**
- **Operating System: debian trixie**

### Feature Flags

- **diesel: sqlite**

## Problem Description

i'm following along with the "relations" tutorial using an sqlite database, and i'm running into a compile error when using a tuple for `.select()` as shown here: https://github.com/diesel-rs/diesel/blob/2.2.x/examples/postgres/relations/src/main.rs#L147

NOTE: i was able to workaround this issue by manually specifying the return value of the `.load()` call, as in:

```rust
let attrs = BookAuthor::belonging_to(&author)
      .inner_join(authors::table)
      .select((BookAuthor::as_select(), Author::as_select()))
      .load::<(BookAuthor, Author)>(conn);
```

**build error**

```rust
error[E0283]: type annotations needed
    --> src/main.rs:155:18
     |
155  |                 .load(conn);
     |                  ^^^^ ---- type must be known at this point
     |                  |
     |                  cannot infer type of the type parameter `U` declared on the method `load`
     |
     = note: cannot satisfy `_: FromSqlRow<(expression::select_by::SelectBy<BookAuthor, Sqlite>, expression::select_by::SelectBy<Author, Sqlite>), Sqlite>`
     = help: the following types implement trait `FromSqlRow<ST, DB>`:
               <(T1, T0) as FromSqlRow<(ST1, Untyped), __DB>>
               <(T1, T2, T0) as FromSqlRow<(ST1, ST2, Untyped), __DB>>
               <(T1, T2, T3, T0) as FromSqlRow<(ST1, ST2, ST3, Untyped), __DB>>
               <(T1, T2, T3, T4, T0) as FromSqlRow<(ST1, ST2, ST3, ST4, Untyped), __DB>>
               <(T1, T2, T3, T4, T5, T0) as FromSqlRow<(ST1, ST2, ST3, ST4, ST5, Untyped), __DB>>
               <(T1, T2, T3, T4, T5, T6, T0) as FromSqlRow<(ST1, ST2, ST3, ST4, ST5, ST6, Untyped), __DB>>
               <(T1, T2, T3, T4, T5, T6, T7, T0) as FromSqlRow<(ST1, ST2, ST3, ST4, ST5, ST6, ST7, Untyped), __DB>>
               <(T1, T2, T3, T4, T5, T6, T7, T8, T0) as FromSqlRow<(ST1, ST2, ST3, ST4, ST5, ST6, ST7, ST8, Untyped), __DB>>
             and 23 others
     = note: required for `(SelectBy<BookAuthor, Sqlite>, SelectBy<Author, Sqlite>)` to implement `load_dsl::private::CompatibleType<_, Sqlite>`
     = note: required for `SelectStatement<FromClause<JoinOn<..., ...>>, ..., ..., ...>` to implement `LoadQuery<'_, diesel::SqliteConnection, _>`
note: required by a bound in `diesel::RunQueryDsl::load`
```

### What are you trying to accomplish?

i'm trying to perform an `inner_join` on a many-to-many relationship with sqlite as the database engine.

### Steps to reproduce

port the `relations` postgres example directly to `SqliteConnection`, using the same schema and model and then try to build.

## Checklist

- [x] I have already looked over the [issue tracker](https://github.com/diesel-rs/diesel/issues) and the [discussion forum](https://github.com/diesel-rs/diesel/discussions) for similar possible closed issues.
<!--
If you are unsure if your issue is a duplicate of an existing issue please link the issue in question here
--> 
- [x] This issue can be reproduced on Rust's stable channel. (Your issue will be
  closed if this is not the case)
- [x] This issue can be reproduced without requiring a third party crate


Discussion / hints:

Fix patch:
diff --git a/Cargo.toml b/Cargo.toml
index d3672a04cdc0..5f1d07cff296 100644
--- a/Cargo.toml
+++ b/Cargo.toml
@@ -28,6 +28,7 @@ members = [
     "examples/sqlite/getting_started_step_1",
     "examples/sqlite/getting_started_step_2",
     "examples/sqlite/getting_started_step_3",
+    "examples/sqlite/relations",
 ]
 
 [workspace.package]
diff --git a/examples/postgres/relations/migrations/2023-02-17-084617_add_books_authors/up.sql b/examples/postgres/relations/migrations/2023-02-17-084617_add_books_authors/up.sql
index bff7186bc15e..937b2f4857d3 100644
--- a/examples/postgres/relations/migrations/2023-02-17-084617_add_books_authors/up.sql
+++ b/examples/postgres/relations/migrations/2023-02-17-084617_add_books_authors/up.sql
@@ -1,6 +1,6 @@
 -- Your SQL goes here
 CREATE TABLE books_authors (
-  book_id INTEGER REFERENCES books(id),
-  author_id INTEGER REFERENCES authors(id),
+  book_id INTEGER NOT NULL REFERENCES books(id),
+  author_id INTEGER NOT NULL REFERENCES authors(id),
   PRIMARY KEY(book_id, author_id)
 );
diff --git a/examples/sqlite/relations/Cargo.toml b/examples/sqlite/relations/Cargo.toml
new file mode 100644
index 000000000000..2a383cd04a1f
--- /dev/null
+++ b/examples/sqlite/relations/Cargo.toml
@@ -0,0 +1,17 @@
+[package]
+name = "relations_sqlite"
+version = "0.1.0"
+edition = "2021"
+publish = false
+
+# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html
+
+[dependencies]
+diesel = { version = "2.1.0", features = ["sqlite", "returning_clauses_for_sqlite_3_35"], path = "../../../diesel" }
+libsqlite3-sys = { version = "0.28.0", features = ["bundled"] }
+dotenvy = "0.15.6"
+
+[[bin]]
+name = "relations"
+path = "src/main.rs"
+doc = false
diff --git a/examples/sqlite/relations/diesel.toml b/examples/sqlite/relations/diesel.toml
new file mode 100644
index 000000000000..35a12ff0dbc6
--- /dev/null
+++ b/examples/sqlite/relations/diesel.toml
@@ -0,0 +1,8 @@
+# For documentation on how to configure this file,
+# see https://diesel.rs/guides/configuring-diesel-cli
+
+[print_schema]
+file = "src/schema.rs"
+
+[migrations_directory]
+dir = "migrations"
diff --git a/examples/sqlite/relations/migrations/2023-02-08-193717_create_books/down.sql b/examples/sqlite/relations/migrations/2023-02-08-193717_create_books/down.sql
new file mode 100644
index 000000000000..2497972be54f
--- /dev/null
+++ b/examples/sqlite/relations/migrations/2023-02-08-193717_create_books/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+DROP TABLE books;
diff --git a/examples/sqlite/relations/migrations/2023-02-08-193717_create_books/up.sql b/examples/sqlite/relations/migrations/2023-02-08-193717_create_books/up.sql
new file mode 100644
index 000000000000..e61475af143f
--- /dev/null
+++ b/examples/sqlite/relations/migrations/2023-02-08-193717_create_books/up.sql
@@ -0,0 +1,5 @@
+-- Your SQL goes here
+CREATE TABLE books (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  title VARCHAR NOT NULL
+);
diff --git a/examples/sqlite/relations/migrations/2023-02-08-193718_create_pages/down.sql b/examples/sqlite/relations/migrations/2023-02-08-193718_create_pages/down.sql
new file mode 100644
index 000000000000..025c8da9e869
--- /dev/null
+++ b/examples/sqlite/relations/migrations/2023-02-08-193718_create_pages/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+DROP TABLE pages;
diff --git a/examples/sqlite/relations/migrations/2023-02-08-193718_create_pages/up.sql b/examples/sqlite/relations/migrations/2023-02-08-193718_create_pages/up.sql
new file mode 100644
index 000000000000..a53e4816b62c
--- /dev/null
+++ b/examples/sqlite/relations/migrations/2023-02-08-193718_create_pages/up.sql
@@ -0,0 +1,7 @@
+-- Your SQL goes here
+CREATE TABLE pages (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  page_number INT NOT NULL,
+  content TEXT NOT NULL,
+  book_id INTEGER NOT NULL REFERENCES books(id)
+);
diff --git a/examples/sqlite/relations/migrations/2023-02-17-084424_add_authors/down.sql b/examples/sqlite/relations/migrations/2023-02-17-084424_add_authors/down.sql
new file mode 100644
index 000000000000..45c9386ddd15
--- /dev/null
+++ b/examples/sqlite/relations/migrations/2023-02-17-084424_add_authors/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+DROP TABLE authors;
diff --git a/examples/sqlite/relations/migrations/2023-02-17-084424_add_authors/up.sql b/examples/sqlite/relations/migrations/2023-02-17-084424_add_authors/up.sql
new file mode 100644
index 000000000000..266c0634238b
--- /dev/null
+++ b/examples/sqlite/relations/migrations/2023-02-17-084424_add_authors/up.sql
@@ -0,0 +1,5 @@
+-- Your SQL goes here
+CREATE TABLE authors (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  name VARCHAR NOT NULL
+);
diff --git a/examples/sqlite/relations/migrations/2023-02-17-084617_add_books_authors/down.sql b/examples/sqlite/relations/migrations/2023-02-17-084617_add_books_authors/down.sql
new file mode 100644
index 000000000000..17c48ca6ff6f
--- /dev/null
+++ b/examples/sqlite/relations/migrations/2023-02-17-084617_add_books_authors/down.sql
@@ -0,0 +1,2 @@
+-- This file should undo anything in `up.sql`
+DROP TABLE books_authors;
diff --git a/examples/sqlite/relations/migrations/2023-02-17-084617_add_books_authors/up.sql b/examples/sqlite/relations/migrations/2023-02-17-084617_add_books_authors/up.sql
new file mode 100644
index 000000000000..937b2f4857d3
--- /dev/null
+++ b/examples/sqlite/relations/migrations/2023-02-17-084617_add_books_authors/up.sql
@@ -0,0 +1,6 @@
+-- Your SQL goes here
+CREATE TABLE books_authors (
+  book_id INTEGER NOT NULL REFERENCES books(id),
+  author_id INTEGER NOT NULL REFERENCES authors(id),
+  PRIMARY KEY(book_id, author_id)
+);
diff --git a/examples/sqlite/relations/src/main.rs b/examples/sqlite/relations/src/main.rs
new file mode 100644
index 000000000000..3820a56edb94
--- /dev/null
+++ b/examples/sqlite/relations/src/main.rs
@@ -0,0 +1,206 @@
+use diesel::prelude::*;
+use dotenvy::dotenv;
+use std::env;
+use std::error::Error;
+
+pub mod model;
+pub mod schema;
+
+use crate::model::*;
+use crate::schema::*;
+
+fn establish_connection() -> SqliteConnection {
+    dotenv().ok();
+
+    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
+    SqliteConnection::establish(&database_url)
+        .unwrap_or_else(|_| panic!("Error connecting to {database_url}"))
+}
+
+fn new_author(
+    conn: &mut SqliteConnection,
+    name: &str,
+) -> Result<Author, Box<dyn Error + Send + Sync>> {
+    let author = diesel::insert_into(authors::table)
+        .values(authors::name.eq(name))
+        .returning(Author::as_returning())
+        .get_result(conn)?;
+    Ok(author)
+}
+
+fn new_book(
+    conn: &mut SqliteConnection,
+    title: &str,
+) -> Result<Book, Box<dyn Error + Send + Sync>> {
+    let book = diesel::insert_into(books::table)
+        .values(books::title.eq(title))
+        .returning(Book::as_returning())
+        .get_result(conn)?;
+    Ok(book)
+}
+
+fn new_books_author(
+    conn: &mut SqliteConnection,
+    book_id: i32,
+    author_id: i32,
+) -> Result<BookAuthor, Box<dyn Error + Send + Sync>> {
+    let book_author = diesel::insert_into(books_authors::table)
+        .values((
+            books_authors::book_id.eq(book_id),
+            books_authors::author_id.eq(author_id),
+        ))
+        .returning(BookAuthor::as_returning())
+        .get_result(conn)?;
+    Ok(book_author)
+}
+
+fn new_page(
+    conn: &mut SqliteConnection,
+    page_number: i32,
+    content: &str,
+    book_id: i32,
+) -> Result<Page, Box<dyn Error + Send + Sync>> {
+    let page = diesel::insert_into(pages::table)
+        .values((
+            pages::page_number.eq(page_number),
+            pages::content.eq(content),
+            pages::book_id.eq(book_id),
+        ))
+        .returning(Page::as_returning())
+        .get_result(conn)?;
+    Ok(page)
+}
+
+fn joins(conn: &mut SqliteConnection) -> Result<(), Box<dyn Error + Send + Sync>> {
+    let page_with_book = pages::table
+        .inner_join(books::table)
+        .filter(books::title.eq("Momo"))
+        .select((Page::as_select(), Book::as_select()))
+        .load::<(Page, Book)>(conn)?;
+
+    println!("Page-Book pairs: {page_with_book:?}");
+
+    let book_without_pages = books::table
+        .left_join(pages::table)
+        .select((Book::as_select(), Option::<Page>::as_select()))
+        .load::<(Book, Option<Page>)>(conn)?;
+
+    println!("Book-Page pairs (including empty books): {book_without_pages:?}");
+    Ok(())
+}
+
+fn one_to_n_relations(conn: &mut SqliteConnection) -> Result<(), Box<dyn Error + Send + Sync>> {
+    let momo = books::table
+        .filter(books::title.eq("Momo"))
+        .select(Book::as_select())
+        .get_result(conn)?;
+
+    // get pages for the book "Momo"
+    let pages = Page::belonging_to(&momo)
+        .select(Page::as_select())
+        .load(conn)?;
+
+    println!("Pages for \"Momo\": \n {pages:?}\n");
+
+    let all_books = books::table.select(Book::as_select()).load(conn)?;
+
+    // get all pages for all books
+    let pages = Page::belonging_to(&all_books)
+        .select(Page::as_select())
+        .load(conn)?;
+
+    // group the pages per book
+    let pages_per_book = pages
+        .grouped_by(&all_books)
+        .into_iter()
+        .zip(all_books)
+        .map(|(pages, book)| (book, pages))
+        .collect::<Vec<(Book, Vec<Page>)>>();
+
+    println!("Pages per book: \n {pages_per_book:?}\n");
+
+    Ok(())
+}
+
+fn m_to_n_relations(conn: &mut SqliteConnection) -> Result<(), Box<dyn Error + Send + Sync>> {
+    let astrid_lindgren = authors::table
+        .filter(authors::name.eq("Astrid Lindgren"))
+        .select(Author::as_select())
+        .get_result(conn)?;
+
+    // get all of Astrid Lindgren's books
+    let books = BookAuthor::belonging_to(&astrid_lindgren)
+        .inner_join(books::table)
+        .select(Book::as_select())
+        .load(conn)?;
+    println!("Asgrid Lindgren books: {books:?}");
+
+    let collaboration = books::table
+        .filter(books::title.eq("Pippi and Momo"))
+        .select(Book::as_select())
+        .get_result(conn)?;
+
+    // get authors for the collaboration
+    let authors = BookAuthor::belonging_to(&collaboration)
+        .inner_join(authors::table)
+        .select(Author::as_select())
+        .load(conn)?;
+    println!("Authors for \"Pipi and Momo\": {authors:?}");
+
+    // get a list of authors with all their books
+    let all_authors = authors::table.select(Author::as_select()).load(conn)?;
+
+    let books = BookAuthor::belonging_to(&authors)
+        .inner_join(books::table)
+        .select((BookAuthor::as_select(), Book::as_select()))
+        .load(conn)?;
+
+    let books_per_author: Vec<(Author, Vec<Book>)> = books
+        .grouped_by(&all_authors)
+        .into_iter()
+        .zip(authors)
+        .map(|(b, author)| (author, b.into_iter().map(|(_, book)| book).collect()))
+        .collect();
+
+    println!("All authors including their books: {books_per_author:?}");
+
+    Ok(())
+}
+
+fn main() -> Result<(), Box<dyn Error + Send + Sync>> {
+    let conn = &mut establish_connection();
+    setup_data(conn)?;
+
+    one_to_n_relations(conn)?;
+    joins(conn)?;
+    m_to_n_relations(conn)?;
+    Ok(())
+}
+
+fn setup_data(conn: &mut SqliteConnection) -> Result<(), Box<dyn Error + Send + Sync>> {
+    // create a book
+    let momo = new_book(conn, "Momo")?;
+
+    // a page in that book
+    new_page(conn, 1, "In alten, alten Zeiten ...", momo.id)?;
+    // a second page
+    new_page(conn, 2, "den prachtvollen Theatern...", momo.id)?;
+
+    // create an author
+    let michael_ende = new_author(conn, "Michael Ende")?;
+
+    // let's add the author to the already created book
+    new_books_author(conn, momo.id, michael_ende.id)?;
+
+    // create a second author
+    let astrid_lindgren = new_author(conn, "Astrid Lindgren")?;
+    let pippi = new_book(conn, "Pippi Långstrump")?;
+    new_books_author(conn, pippi.id, astrid_lindgren.id)?;
+
+    // now that both have a single book, let's add a third book, an imaginary collaboration
+    let collaboration = new_book(conn, "Pippi and Momo")?;
+    new_books_author(conn, collaboration.id, astrid_lindgren.id)?;
+    new_books_author(conn, collaboration.id, michael_ende.id)?;
+
+    Ok(())
+}
diff --git a/examples/sqlite/relations/src/model.rs b/examples/sqlite/relations/src/model.rs
new file mode 100644
index 000000000000..35e6d1d68009
--- /dev/null
+++ b/examples/sqlite/relations/src/model.rs
@@ -0,0 +1,37 @@
+use diesel::prelude::*;
+
+use crate::schema::{authors, books, books_authors, pages};
+
+#[derive(Queryable, Selectable, Identifiable, PartialEq, Debug, Clone)]
+#[diesel(table_name = authors)]
+pub struct Author {
+    pub id: i32,
+    pub name: String,
+}
+
+#[derive(Identifiable, Selectable, Queryable, Associations, Debug, Clone)]
+#[diesel(belongs_to(Book))]
+#[diesel(belongs_to(Author))]
+#[diesel(table_name = books_authors)]
+#[diesel(primary_key(book_id, author_id))]
+pub struct BookAuthor {
+    pub book_id: i32,
+    pub author_id: i32,
+}
+
+#[derive(Queryable, Identifiable, Selectable, Debug, PartialEq, Clone)]
+#[diesel(table_name = books)]
+pub struct Book {
+    pub id: i32,
+    pub title: String,
+}
+
+#[derive(Queryable, Selectable, Identifiable, Associations, Debug, PartialEq)]
+#[diesel(belongs_to(Book))]
+#[diesel(table_name = pages)]
+pub struct Page {
+    pub id: i32,
+    pub page_number: i32,
+    pub content: String,
+    pub book_id: i32,
+}
diff --git a/examples/sqlite/relations/src/schema.rs b/examples/sqlite/relations/src/schema.rs
new file mode 100644
index 000000000000..a4e38e66da1d
--- /dev/null
+++ b/examples/sqlite/relations/src/schema.rs
@@ -0,0 +1,37 @@
+// @generated automatically by Diesel CLI.
+
+diesel::table! {
+    authors (id) {
+        id -> Integer,
+        name -> Text,
+    }
+}
+
+diesel::table! {
+    books (id) {
+        id -> Integer,
+        title -> Text,
+    }
+}
+
+diesel::table! {
+    books_authors (book_id, author_id) {
+        book_id -> Integer,
+        author_id -> Integer,
+    }
+}
+
+diesel::table! {
+    pages (id) {
+        id -> Integer,
+        page_number -> Integer,
+        content -> Text,
+        book_id -> Integer,
+    }
+}
+
+diesel::joinable!(books_authors -> authors (author_id));
+diesel::joinable!(books_authors -> books (book_id));
+diesel::joinable!(pages -> books (book_id));
+
+diesel::allow_tables_to_appear_in_same_query!(authors, books, books_authors, pages,);


Test patch: