table with more than one primary_key can not use on_conflict().do_update()

Question

table with more than one primary_key can not use on_conflict().do_update()

istudyatuni opened this issue a month ago · 1 comments

This is similar to #3872

Setup

Code that doesn't work

diesel::table! {
    categories (id, user_id) {
        id -> BigInt,
        created_at -> BigInt,
        sort_key -> Integer,
        title -> Text,
        order -> Text,
        user_id -> Integer,
        track -> Bool,
        show_in_lib -> Bool,
        deleted_at -> BigInt,
    }
}

#[derive(Queryable, Selectable, Insertable, Identifiable, AsChangeset)]
#[diesel(
    table_name = crate::db::schema::categories,
    check_for_backend(diesel::mysql::Mysql),
    primary_key(id, user_id)
)]
pub struct Category {
    pub id: i64,
    pub created_at: Time,
    pub sort_key: i32,
    pub title: String,
    pub order: String,
    pub user_id: UserID,
    pub track: bool,
    pub show_in_lib: bool,
    pub deleted_at: Time,
}

diesel::insert_into(categories)
    .values(&category)
    .on_conflict(diesel::dsl::DuplicatedKeys)
    .do_update()
    .set(&category)
    .execute(conn)?;

// this is also doesn't work
diesel::insert_into(categories)
    .values(&category)
    .on_conflict_do_nothing()
    .execute(conn)?;

// and this
diesel::insert_into(categories)
    .values(&category)
    .on_conflict(diesel::dsl::DuplicatedKeys)
    .do_nothing()
    .execute(conn)?;

// and this
diesel::insert_into(categories)
    .values(&category)
    .on_conflict((id, user_id))
    .do_nothing()
    .execute(conn)?;

First 3 cases compiles when I define only one-column primary key, except 4th:

diesel::insert_into(categories)
    .values(&category)
    .on_conflict(id)
    .do_nothing()
    .execute(conn)?;

Schema is in src/db/schema.rs, models in src/models/db.rs, code, that work with db, in src/db/conn.rs::add_category()

Versions

Rust: 1.77.2
Diesel: 2.1.6
Database: MySQL
Operating System Linux

Feature Flags

diesel: r2d2, mysql

Problem Description

What are you trying to accomplish?

Insert value, and if it exists, update values, except primary keys. I use insert_into + on_conflict because replace_into doesn't work in MySQL (it works in SQLite)

What is the expected output?

Compiles

What is the actual output?

Log

For first case

error[E0277]: the trait bound `query_builder::upsert::on_conflict_clause::OnConflictValues<query_builder::insert_statement::ValuesClause<(DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::id, expression::bound::Bound<diesel::sql_types::BigInt, &i64>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::created_at, expression::bound::Bound<diesel::sql_types::BigInt, &i64>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::sort_key, expression::bound::Bound<diesel::sql_types::Integer, &i32>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::title, expression::bound::Bound<diesel::sql_types::Text, &std::string::String>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::order, expression::bound::Bound<diesel::sql_types::Text, &std::string::String>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::user_id, expression::bound::Bound<diesel::sql_types::Integer, &i32>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::track, expression::bound::Bound<diesel::sql_types::Bool, &bool>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::show_in_lib, expression::bound::Bound<diesel::sql_types::Bool, &bool>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::deleted_at, expression::bound::Bound<diesel::sql_types::BigInt, &i64>>>), categories::table>, query_builder::upsert::on_conflict_target::ConflictTarget<DuplicatedKeys>, query_builder::upsert::on_conflict_actions::DoUpdate<(query_builder::update_statement::changeset::Assign<query_builder::update_statement::changeset::ColumnWrapperForUpdate<categories::columns::created_at>, expression::bound::Bound<diesel::sql_types::BigInt, &i64>>, query_builder::update_statement::changeset::Assign<query_builder::update_statement::changeset::ColumnWrapperForUpdate<categories::columns::sort_key>, expression::bound::Bound<diesel::sql_types::Integer, &i32>>, query_builder::update_statement::changeset::Assign<query_builder::update_statement::changeset::ColumnWrapperForUpdate<categories::columns::title>, expression::bound::Bound<diesel::sql_types::Text, &std::string::String>>, query_builder::update_statement::changeset::Assign<query_builder::update_statement::changeset::ColumnWrapperForUpdate<categories::columns::order>, expression::bound::Bound<diesel::sql_types::Text, &std::string::String>>, query_builder::update_statement::changeset::Assign<query_builder::update_statement::changeset::ColumnWrapperForUpdate<categories::columns::track>, expression::bound::Bound<diesel::sql_types::Bool, &bool>>, query_builder::update_statement::changeset::Assign<query_builder::update_statement::changeset::ColumnWrapperForUpdate<categories::columns::show_in_lib>, expression::bound::Bound<diesel::sql_types::Bool, &bool>>, query_builder::update_statement::changeset::Assign<query_builder::update_statement::changeset::ColumnWrapperForUpdate<categories::columns::deleted_at>, expression::bound::Bound<diesel::sql_types::BigInt, &i64>>), categories::table>>: QueryFragment<Mysql, mysql::backend::MysqlOnConflictClause>` is not satisfied
    --> src/db/conn.rs:217:22
     |
217  |             .execute(conn)?;
     |              ------- ^^^^ the trait `QueryFragment<Mysql, mysql::backend::MysqlOnConflictClause>` is not implemented for `OnConflictValues<ValuesClause<(DefaultableColumnInsertValue<...>, ..., ..., ..., ..., ..., ..., ..., ...), ...>, ..., ...>`, which is required by `InsertStatement<categories::table, query_builder::upsert::on_conflict_clause::OnConflictValues<query_builder::insert_statement::ValuesClause<(DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::id, expression::bound::Bound<diesel::sql_types::BigInt, &i64>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::created_at, expression::bound::Bound<diesel::sql_types::BigInt, &i64>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::sort_key, expression::bound::Bound<diesel::sql_types::Integer, &i32>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::title, expression::bound::Bound<diesel::sql_types::Text, &std::string::String>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::order, expression::bound::Bound<diesel::sql_types::Text, &std::string::String>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::user_id, expression::bound::Bound<diesel::sql_types::Integer, &i32>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::track, expression::bound::Bound<diesel::sql_types::Bool, &bool>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::show_in_lib, expression::bound::Bound<diesel::sql_types::Bool, &bool>>>, DefaultableColumnInsertValue<ColumnInsertValue<categories::columns::deleted_at, expression::bound::Bound<diesel::sql_types::BigInt, &i64>>>), categories::table>, query_builder::upsert::on_conflict_target::ConflictTarget<DuplicatedKeys>, query_builder::upsert::on_conflict_actions::DoUpdate<(query_builder::update_statement::changeset::Assign<query_builder::update_statement::changeset::ColumnWrapperForUpdate<categories::columns::created_at>, expression::bound::Bound<diesel::sql_types::BigInt, &i64>>, query_builder::update_statement::changeset::Assign<query_builder::update_statement::changeset::ColumnWrapperForUpdate<categories::columns::sort_key>, expression::bound::Bound<diesel::sql_types::Integer, &i32>>, query_builder::update_statement::changeset::Assign<query_builder::update_statement::changeset::ColumnWrapperForUpdate<categories::columns::title>, expression::bound::Bound<diesel::sql_types::Text, &std::string::String>>, query_builder::update_statement::changeset::Assign<query_builder::update_statement::changeset::ColumnWrapperForUpdate<categories::columns::order>, expression::bound::Bound<diesel::sql_types::Text, &std::string::String>>, query_builder::update_statement::changeset::Assign<query_builder::update_statement::changeset::ColumnWrapperForUpdate<categories::columns::track>, expression::bound::Bound<diesel::sql_types::Bool, &bool>>, query_builder::update_statement::changeset::Assign<query_builder::update_statement::changeset::ColumnWrapperForUpdate<categories::columns::show_in_lib>, expression::bound::Bound<diesel::sql_types::Bool, &bool>>, query_builder::update_statement::changeset::Assign<query_builder::update_statement::changeset::ColumnWrapperForUpdate<categories::columns::deleted_at>, expression::bound::Bound<diesel::sql_types::BigInt, &i64>>), categories::table>>>: ExecuteDsl<_, _>`
     |              |
     |              required by a bound introduced by this call
     |
     = help: the following other types implement trait `QueryFragment<DB, SP>`:
               <query_builder::upsert::on_conflict_clause::OnConflictValues<Values, Target, Action> as QueryFragment<Mysql, mysql::backend::MysqlOnConflictClause>>
               <query_builder::upsert::on_conflict_clause::OnConflictValues<Values, Target, Action> as QueryFragment<DB>>
               <query_builder::upsert::on_conflict_clause::OnConflictValues<Values, Target, Action> as QueryFragment<DB, SD>>
               <query_builder::upsert::on_conflict_clause::OnConflictValues<Values, Target, Action, query_builder::where_clause::WhereClause<Expr>> as QueryFragment<DB>>
     = note: required for `OnConflictValues<ValuesClause<(DefaultableColumnInsertValue<...>, ..., ..., ..., ..., ..., ..., ..., ...), ...>, ..., ...>` to implement `QueryFragment<Mysql>`
     = note: the full type name has been written to '/[..]/kotync/target/debug/deps/kotync-dba6149db5684457.long-type-16164201235515559885.txt'
     = note: 1 redundant requirement hidden
     = note: required for `InsertStatement<table, OnConflictValues<ValuesClause<(..., ..., ..., ..., ..., ..., ..., ..., ...), ...>, ..., ...>>` to implement `QueryFragment<Mysql>`
     = note: the full type name has been written to '/[..]/kotync/target/debug/deps/kotync-dba6149db5684457.long-type-16831793125519393571.txt'
     = note: required for `InsertStatement<table, OnConflictValues<ValuesClause<(..., ..., ..., ..., ..., ..., ..., ..., ...), ...>, ..., ...>>` to implement `ExecuteDsl<PooledConnection<ConnectionManager<MysqlConnection>>, Mysql>`
     = note: the full type name has been written to '/[..]/kotync/target/debug/deps/kotync-dba6149db5684457.long-type-16831793125519393571.txt'
note: required by a bound in `diesel::RunQueryDsl::execute`
    --> /[..]/src/index.crates.io-6f17d22bba15001f/diesel-2.1.6/src/query_dsl/mod.rs:1431:15
     |
1428 |     fn execute(self, conn: &mut Conn) -> QueryResult<usize>
     |        ------- required by a bound in this associated function
...
1431 |         Self: methods::ExecuteDsl<Conn>,
     |               ^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `RunQueryDsl::execute`

For more information about this error, try `rustc --explain E0277`.

Are you seeing any additional errors?

No

Steps to reproduce

git clone -b mysql https://github.com/istudyatuni/kotync.git
cd kotync
cargo c

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. (Your issue will be
closed if this is not the case)
This issue can be reproduced without requiring a third party crate (I'm not sure, but I don't use third-party crate for DB, except diesel_migrations)

Answer 1 · 2024-05-02T11:54:56.000Z

Thanks for opening this bug report. This is literally a duplicate of #3872 as this error does not happen with the master branch anymore. It seems like nobody cared enough about that bug to get the fix backported into some recent patch release, so it will be part of the next feature release by default.

As a site note: It's expected that this will not work:

diesel::insert_into(categories)
    .values(&category)
    .on_conflict((id, user_id))
    .do_nothing()
    .execute(conn)?;

as mysql just does not allow to list explicit keys as conflict target.