IntoUpdateTarget not implemented for a delete operation

[Libbum]

Setup

Versions

• Rust: 1.24.0-nightly (2d4df9584 2017-12-10)
• Diesel: 1.0.0-beta1
• Database: sqlite
• Operating System Arch Linux

Feature Flags

• diesel: sqlite, chrono
• diesel_codegen: not using it

Problem Description

I have a similar issue to #998 in that my delete operation is failing to build in a portion of my query.

What are you trying to accomplish?

The following sql:

DELETE FROM comments WHERE mode=2 AND id NOT IN
    (SELECT parent FROM comments WHERE parent IS NOT NULL);

implemented with the following query:

let child = comments::table.select(comments::parent).filter(comments::parent.is_not_null());
let target = comments::table.filter(comments::mode.eq(2)).filter(comments::id.ne_any(child));
let query = diesel::delete(target);

on a table:

table! {
    comments (id) {
        id -> Integer,
        tid -> Integer,
        parent -> Nullable<Integer>,
        created -> Timestamp,
        modified -> Nullable<Timestamp>,
        mode -> Integer,
        remote_addr -> Nullable<Text>,
        text -> Text,
        author -> Nullable<Text>,
        email -> Nullable<Text>,
        website -> Nullable<Text>,
        hash -> Text,
        likes -> Nullable<Integer>,
        dislikes -> Nullable<Integer>,
        voters -> Text,
    }
}

What is the expected output?

query should successfully compile.

What is the actual output?

A compilation failure stating that IntoUpdateTarget is not implemented for the query.

Are you seeing any additional errors?

The complete error output is below:

error[E0277]: the trait bound diesel::query_builder::SelectStatement<schema::comments::table, diesel::query_builder::select_clause::DefaultSelectClause, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::And<diesel::expression::operators::Eq<schema::comments::columns::mode, diesel::expression::bound::Bound<diesel::types::Integer, i32>>, diesel::expression::array_comparison::NotIn<schema::comments::columns::id, diesel::expression::array_comparison::Subselect<diesel::query_builder::SelectStatement<schema::comments::table, diesel::query_builder::select_clause::SelectClause<schema::comments::columns::parent>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::dsl::IsNotNull<schema::comments::columns::parent>>>, diesel::types::Integer>>>>>: diesel::query_builder::IntoUpdateTarget is not satisfied
--> src/models/comments/mod.rs:196:21
|
196 | let query = diesel::delete(target);
| ^^^^^^^^^^^^^^ the trait diesel::query_builder::IntoUpdateTarget is not implemented for diesel::query_builder::SelectStatement<schema::comments::table, diesel::query_builder::select_clause::DefaultSelectClause, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::And<diesel::expression::operators::Eq<schema::comments::columns::mode, diesel::expression::bound::Bound<diesel::types::Integer, i32>>, diesel::expression::array_comparison::NotIn<schema::comments::columns::id, diesel::expression::array_comparison::Subselect<diesel::query_builder::SelectStatement<schema::comments::table, diesel::query_builder::select_clause::SelectClause<schema::comments::columns::parent>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::dsl::IsNotNull<schema::comments::columns::parent>>>, diesel::types::Integer>>>>>
|
= help: the following implementations were found:
<diesel::query_builder::SelectStatement<F, diesel::query_builder::select_clause::DefaultSelectClause, diesel::query_builder::distinct_clause::NoDistinctClause, W> as diesel::query_builder::IntoUpdateTarget>
= note: required by diesel::delete

error[E0277]: the trait bound diesel::query_builder::SelectStatement<schema::comments::table, diesel::query_builder::select_clause::DefaultSelectClause, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::And<diesel::expression::operators::Eq<schema::comments::columns::mode, diesel::expression::bound::Bound<diesel::types::Integer, i32>>, diesel::expression::array_comparison::NotIn<schema::comments::columns::id, diesel::expression::array_comparison::Subselect<diesel::query_builder::SelectStatement<schema::comments::table, diesel::query_builder::select_clause::SelectClause<schema::comments::columns::parent>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::dsl::IsNotNull<schema::comments::columns::parent>>>, diesel::types::Integer>>>>>: diesel::query_builder::IntoUpdateTarget is not satisfied
--> src/models/comments/mod.rs:196:21
|
196 | let query = diesel::delete(target);
| ^^^^^^^^^^^^^^^^^^^^^^ the trait diesel::query_builder::IntoUpdateTarget is not implemented for diesel::query_builder::SelectStatement<schema::comments::table, diesel::query_builder::select_clause::DefaultSelectClause, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::And<diesel::expression::operators::Eq<schema::comments::columns::mode, diesel::expression::bound::Bound<diesel::types::Integer, i32>>, diesel::expression::array_comparison::NotIn<schema::comments::columns::id, diesel::expression::array_comparison::Subselect<diesel::query_builder::SelectStatement<schema::comments::table, diesel::query_builder::select_clause::SelectClause<schema::comments::columns::parent>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::dsl::IsNotNull<schema::comments::columns::parent>>>, diesel::types::Integer>>>>>
|
= help: the following implementations were found:
<diesel::query_builder::SelectStatement<F, diesel::query_builder::select_clause::DefaultSelectClause, diesel::query_builder::distinct_clause::NoDistinctClause, W> as diesel::query_builder::IntoUpdateTarget>

error: aborting due to 2 previous errors

Steps to reproduce

Checklist

• I have already looked over the issue tracker for similar issues.

[sgrif]

Thanks for the issue. It's unfortunate that Rust was not able to provide a better error message here. The reason that this occurs becomes more clear if you try to load target. The reason that this occurs is that we do not allow the same table to appear in a query more than once. The reason for this restriction is that it's ambiguous whether comments.parent is referencing the inner query or the outer one, unless the table is aliased. Unfortunately we don't have support for table aliasing at this time.

You can work around this by running your subselect as a separate query. (Since you're using SQLite, there's no round-trip time to worry about). Your code works if changed to the following:

let child = comments.select(parent).filter(parent.is_not_null()).load::<Option<i32>>()?;
let target = comments.filter(mode.eq(2)).filter(id.nullable().ne_any(child));

[Libbum]

Oh, I see! Thanks for the reasoning and suggested changes -- can confirm they do indeed work as expected.