Diesel error when using a subquery: expected struct `diesel::query_source::MoreThanOnce`, found struct `diesel::query_source::Once`

1

A couple months ago I was updating some old (pre-1.0 Diesel) code to Diesel 1.4. I came to some code that no longer compiled but I was never able to figure out how to get it to work, because I didn’t understand the implications of the error message.

Here is a reduced code sample (assume the stuff below the table definition is inside a function):

use diesel::prelude::*;
use diesel::dsl::{any, max};

table! {
    events {
        id -> Text,
        ordering -> BigSerial,
        room_id -> Nullable<Text>,
        sender -> Text,
        event_type -> Text,
        state_key -> Nullable<Text>,
        content -> Text,
        created_at -> Timestamp,
    }
}

let state_events: Vec<String> = vec!["foo".to_string(), "bar".to_string(), "baz".to_string()];
let some_specific_ordering: i64 = 1234;
let room_id: String = "room_1";

let ordering = events::table
    .select(max(events::ordering))
    .filter(events::room_id.eq(room_id))
    .filter(events::event_type.eq(any(state_events)))
    .filter(events::ordering.lt(some_specific_ordering))
    .group_by(events::event_type);

events::table
    .filter(events::ordering.nullable().eq(any(ordering)))
    .get_results(connection)

In the older version of Diesel I was using, this worked for running a query with a subquery. On Diesel 1.4, it results in this error (forgive the line numbering, this is the output from the real application):

error[E0271]: type mismatch resolving `<diesel::query_source::joins::Join<schema::events::table, schema::events::table, diesel::query_source::joins::Inner> as diesel::query_source::AppearsInFromClause<schema::events::table>>::Count == diesel::query_source::Once`
   --> src/models/event.rs:188:14
    |
188 |             .get_results(connection)
    |              ^^^^^^^^^^^ expected struct `diesel::query_source::MoreThanOnce`, found struct `diesel::query_source::Once`
    |
    = note: expected type `diesel::query_source::MoreThanOnce`
               found type `diesel::query_source::Once`
    = note: required because of the requirements on the impl of `diesel::AppearsOnTable<diesel::query_source::joins::Join<schema::events::table, schema::events::table, diesel::query_source::joins::Inner>>` for `schema::events::columns::ordering`
    = note: required because of the requirements on the impl of `diesel::AppearsOnTable<diesel::query_source::joins::Join<schema::events::table, schema::events::table, diesel::query_source::joins::Inner>>` for `diesel::expression::operators::Lt<schema::events::columns::ordering, diesel::expression::bound::Bound<diesel::sql_types::BigInt, i64>>`
    = note: required because of the requirements on the impl of `diesel::AppearsOnTable<diesel::query_source::joins::Join<schema::events::table, schema::events::table, diesel::query_source::joins::Inner>>` for `diesel::expression::operators::And<diesel::expression::operators::And<diesel::expression::operators::Eq<schema::events::columns::room_id, diesel::expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Text>, &ruma_identifiers::RoomId>>, diesel::expression::operators::Eq<schema::events::columns::event_type, diesel::pg::expression::array_comparison::Any<diesel::expression::bound::Bound<diesel::sql_types::Array<diesel::sql_types::Text>, std::vec::Vec<std::string::String>>>>>, diesel::expression::operators::Lt<schema::events::columns::ordering, diesel::expression::bound::Bound<diesel::sql_types::BigInt, i64>>>`
    = note: required because of the requirements on the impl of `diesel::query_builder::where_clause::ValidWhereClause<diesel::query_source::joins::Join<schema::events::table, schema::events::table, diesel::query_source::joins::Inner>>` for `diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::And<diesel::expression::operators::And<diesel::expression::operators::Eq<schema::events::columns::room_id, diesel::expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Text>, &ruma_identifiers::RoomId>>, diesel::expression::operators::Eq<schema::events::columns::event_type, diesel::pg::expression::array_comparison::Any<diesel::expression::bound::Bound<diesel::sql_types::Array<diesel::sql_types::Text>, std::vec::Vec<std::string::String>>>>>, diesel::expression::operators::Lt<schema::events::columns::ordering, diesel::expression::bound::Bound<diesel::sql_types::BigInt, i64>>>>`
    = note: required because of the requirements on the impl of `diesel::expression::subselect::ValidSubselect<schema::events::table>` for `diesel::query_builder::SelectStatement<schema::events::table, diesel::query_builder::select_clause::SelectClause<diesel::expression::functions::aggregate_ordering::max::max<diesel::sql_types::BigInt, schema::events::columns::ordering>>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::And<diesel::expression::operators::And<diesel::expression::operators::Eq<schema::events::columns::room_id, diesel::expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Text>, &ruma_identifiers::RoomId>>, diesel::expression::operators::Eq<schema::events::columns::event_type, diesel::pg::expression::array_comparison::Any<diesel::expression::bound::Bound<diesel::sql_types::Array<diesel::sql_types::Text>, std::vec::Vec<std::string::String>>>>>, diesel::expression::operators::Lt<schema::events::columns::ordering, diesel::expression::bound::Bound<diesel::sql_types::BigInt, i64>>>>, diesel::query_builder::order_clause::NoOrderClause, diesel::query_builder::limit_clause::NoLimitClause, diesel::query_builder::offset_clause::NoOffsetClause, diesel::query_builder::group_by_clause::GroupByClause<schema::events::columns::event_type>>`
    = note: required because of the requirements on the impl of `diesel::AppearsOnTable<schema::events::table>` for `diesel::expression::subselect::Subselect<diesel::query_builder::SelectStatement<schema::events::table, diesel::query_builder::select_clause::SelectClause<diesel::expression::functions::aggregate_ordering::max::max<diesel::sql_types::BigInt, schema::events::columns::ordering>>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::And<diesel::expression::operators::And<diesel::expression::operators::Eq<schema::events::columns::room_id, diesel::expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Text>, &ruma_identifiers::RoomId>>, diesel::expression::operators::Eq<schema::events::columns::event_type, diesel::pg::expression::array_comparison::Any<diesel::expression::bound::Bound<diesel::sql_types::Array<diesel::sql_types::Text>, std::vec::Vec<std::string::String>>>>>, diesel::expression::operators::Lt<schema::events::columns::ordering, diesel::expression::bound::Bound<diesel::sql_types::BigInt, i64>>>>, diesel::query_builder::order_clause::NoOrderClause, diesel::query_builder::limit_clause::NoLimitClause, diesel::query_builder::offset_clause::NoOffsetClause, diesel::query_builder::group_by_clause::GroupByClause<schema::events::columns::event_type>>, diesel::sql_types::Array<diesel::sql_types::Nullable<diesel::sql_types::BigInt>>>`
    = note: required because of the requirements on the impl of `diesel::AppearsOnTable<schema::events::table>` for `diesel::pg::expression::array_comparison::Any<diesel::expression::subselect::Subselect<diesel::query_builder::SelectStatement<schema::events::table, diesel::query_builder::select_clause::SelectClause<diesel::expression::functions::aggregate_ordering::max::max<diesel::sql_types::BigInt, schema::events::columns::ordering>>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::And<diesel::expression::operators::And<diesel::expression::operators::Eq<schema::events::columns::room_id, diesel::expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Text>, &ruma_identifiers::RoomId>>, diesel::expression::operators::Eq<schema::events::columns::event_type, diesel::pg::expression::array_comparison::Any<diesel::expression::bound::Bound<diesel::sql_types::Array<diesel::sql_types::Text>, std::vec::Vec<std::string::String>>>>>, diesel::expression::operators::Lt<schema::events::columns::ordering, diesel::expression::bound::Bound<diesel::sql_types::BigInt, i64>>>>, diesel::query_builder::order_clause::NoOrderClause, diesel::query_builder::limit_clause::NoLimitClause, diesel::query_builder::offset_clause::NoOffsetClause, diesel::query_builder::group_by_clause::GroupByClause<schema::events::columns::event_type>>, diesel::sql_types::Array<diesel::sql_types::Nullable<diesel::sql_types::BigInt>>>>`
    = note: required because of the requirements on the impl of `diesel::AppearsOnTable<schema::events::table>` for `diesel::expression::operators::Eq<diesel::expression::nullable::Nullable<schema::events::columns::ordering>, diesel::pg::expression::array_comparison::Any<diesel::expression::subselect::Subselect<diesel::query_builder::SelectStatement<schema::events::table, diesel::query_builder::select_clause::SelectClause<diesel::expression::functions::aggregate_ordering::max::max<diesel::sql_types::BigInt, schema::events::columns::ordering>>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::And<diesel::expression::operators::And<diesel::expression::operators::Eq<schema::events::columns::room_id, diesel::expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Text>, &ruma_identifiers::RoomId>>, diesel::expression::operators::Eq<schema::events::columns::event_type, diesel::pg::expression::array_comparison::Any<diesel::expression::bound::Bound<diesel::sql_types::Array<diesel::sql_types::Text>, std::vec::Vec<std::string::String>>>>>, diesel::expression::operators::Lt<schema::events::columns::ordering, diesel::expression::bound::Bound<diesel::sql_types::BigInt, i64>>>>, diesel::query_builder::order_clause::NoOrderClause, diesel::query_builder::limit_clause::NoLimitClause, diesel::query_builder::offset_clause::NoOffsetClause, diesel::query_builder::group_by_clause::GroupByClause<schema::events::columns::event_type>>, diesel::sql_types::Array<diesel::sql_types::Nullable<diesel::sql_types::BigInt>>>>>`
    = note: required because of the requirements on the impl of `diesel::query_builder::where_clause::ValidWhereClause<schema::events::table>` for `diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::Eq<diesel::expression::nullable::Nullable<schema::events::columns::ordering>, diesel::pg::expression::array_comparison::Any<diesel::expression::subselect::Subselect<diesel::query_builder::SelectStatement<schema::events::table, diesel::query_builder::select_clause::SelectClause<diesel::expression::functions::aggregate_ordering::max::max<diesel::sql_types::BigInt, schema::events::columns::ordering>>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::And<diesel::expression::operators::And<diesel::expression::operators::Eq<schema::events::columns::room_id, diesel::expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Text>, &ruma_identifiers::RoomId>>, diesel::expression::operators::Eq<schema::events::columns::event_type, diesel::pg::expression::array_comparison::Any<diesel::expression::bound::Bound<diesel::sql_types::Array<diesel::sql_types::Text>, std::vec::Vec<std::string::String>>>>>, diesel::expression::operators::Lt<schema::events::columns::ordering, diesel::expression::bound::Bound<diesel::sql_types::BigInt, i64>>>>, diesel::query_builder::order_clause::NoOrderClause, diesel::query_builder::limit_clause::NoLimitClause, diesel::query_builder::offset_clause::NoOffsetClause, diesel::query_builder::group_by_clause::GroupByClause<schema::events::columns::event_type>>, diesel::sql_types::Array<diesel::sql_types::Nullable<diesel::sql_types::BigInt>>>>>>`
    = note: required because of the requirements on the impl of `diesel::query_builder::Query` for `diesel::query_builder::SelectStatement<schema::events::table, diesel::query_builder::select_clause::DefaultSelectClause, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::Eq<diesel::expression::nullable::Nullable<schema::events::columns::ordering>, diesel::pg::expression::array_comparison::Any<diesel::expression::subselect::Subselect<diesel::query_builder::SelectStatement<schema::events::table, diesel::query_builder::select_clause::SelectClause<diesel::expression::functions::aggregate_ordering::max::max<diesel::sql_types::BigInt, schema::events::columns::ordering>>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::And<diesel::expression::operators::And<diesel::expression::operators::Eq<schema::events::columns::room_id, diesel::expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Text>, &ruma_identifiers::RoomId>>, diesel::expression::operators::Eq<schema::events::columns::event_type, diesel::pg::expression::array_comparison::Any<diesel::expression::bound::Bound<diesel::sql_types::Array<diesel::sql_types::Text>, std::vec::Vec<std::string::String>>>>>, diesel::expression::operators::Lt<schema::events::columns::ordering, diesel::expression::bound::Bound<diesel::sql_types::BigInt, i64>>>>, diesel::query_builder::order_clause::NoOrderClause, diesel::query_builder::limit_clause::NoLimitClause, diesel::query_builder::offset_clause::NoOffsetClause, diesel::query_builder::group_by_clause::GroupByClause<schema::events::columns::event_type>>, diesel::sql_types::Array<diesel::sql_types::Nullable<diesel::sql_types::BigInt>>>>>>>`
    = note: required because of the requirements on the impl of `diesel::query_dsl::LoadQuery<_, _>` for `diesel::query_builder::SelectStatement<schema::events::table, diesel::query_builder::select_clause::DefaultSelectClause, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::Eq<diesel::expression::nullable::Nullable<schema::events::columns::ordering>, diesel::pg::expression::array_comparison::Any<diesel::expression::subselect::Subselect<diesel::query_builder::SelectStatement<schema::events::table, diesel::query_builder::select_clause::SelectClause<diesel::expression::functions::aggregate_ordering::max::max<diesel::sql_types::BigInt, schema::events::columns::ordering>>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::And<diesel::expression::operators::And<diesel::expression::operators::Eq<schema::events::columns::room_id, diesel::expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Text>, &ruma_identifiers::RoomId>>, diesel::expression::operators::Eq<schema::events::columns::event_type, diesel::pg::expression::array_comparison::Any<diesel::expression::bound::Bound<diesel::sql_types::Array<diesel::sql_types::Text>, std::vec::Vec<std::string::String>>>>>, diesel::expression::operators::Lt<schema::events::columns::ordering, diesel::expression::bound::Bound<diesel::sql_types::BigInt, i64>>>>, diesel::query_builder::order_clause::NoOrderClause, diesel::query_builder::limit_clause::NoLimitClause, diesel::query_builder::offset_clause::NoOffsetClause, diesel::query_builder::group_by_clause::GroupByClause<schema::events::columns::event_type>>, diesel::sql_types::Array<diesel::sql_types::Nullable<diesel::sql_types::BigInt>>>>>>>`

I was able to work around the issue for the time being by just doing two round trips to the database:

let ordering: Vec<Option<i64>> = events::table
    .select(max(events::ordering))
    .filter(events::room_id.eq(room_id))
    .filter(events::event_type.eq(any(state_events)))
    .filter(events::ordering.lt(some_specific_ordering))
    .group_by(events::event_type)
    .get_results(connection)
    .map_err(ApiError::from)?;

events::table
    .filter(events::ordering.nullable().eq(any(ordering)))
    .get_results(connection)
    .map_err(ApiError::from)

But I’d like to understand the original error and to get a version with a subquery working.

Thanks in advance for any help!

2

The error message is telling you that the same table appears in a query more than once, which is unsound without aliasing. I don’t see anything at first glance in your example that violates this, but it was perhaps omitted by your attempts to minify? The most likely cause is a subselect to the same table

3

Thanks for the reply!

You can find the original source that this came from here, in case it offers enough context to see why this was an error: https://github.com/ruma/ruma/blob/d88b5894b32fe6e2c495ae55a350878db0a7be7f/src/models/event.rs#L183

What’s the intended way to do a subselect on the same table? I haven’t run into a need to “alias” a table before so I’m not sure what this entails, or why it’s unsound not to.

Probably also worth mentioning that I think it would be super useful to have guides for Diesel about how to do various types of selects, and a high level overview of how joins work. Both of these things required quite a bit of spelunking through docs to figure out. I imagine guides on these topics would be very helpful to people—new users especially.