Use sql_function! with aliased field

[Ticki84]

Hi,

I want to be able to order by a boxed query using coalesce. I've followed this tutorial, but I can't make it work using aliases:

sql_function! { fn coalesce(x: Nullable<Text>, y: Nullable<Text>, z: Nullable<Text>) -> Text; }

query = query.order_by(coalesce(a_spot_base.field(assets::code).asc(), a_future_contract.field(assets::code).asc(), a_option_contract.field(assets::code).asc()));

I get this kind of error:

error[E0271]: type mismatch resolving `<diesel::expression::operators::Asc<AliasedField<a_spot_base, schema::assets::columns::code>> as diesel::Expression>::SqlType == diesel::sql_types::Nullable<diesel::sql_types::Text>`
   --> src/data_access.rs:597:43
    |
597 | ...   .order_by(coalesce(a_spot_base.field(assets::code).asc(), a_future_contract.field(assets::code).asc(), a_option_contract.field(asse...
    |                 ^^^^^^^^ expected struct `diesel::sql_types::Nullable`, found struct `NotSelectable`
    |
    = note: expected struct `diesel::sql_types::Nullable<diesel::sql_types::Text>`
               found struct `NotSelectable`
    = note: required because of the requirements on the impl of `AsExpression<diesel::sql_types::Nullable<diesel::sql_types::Text>>` for `diesel::expression::operators::Asc<AliasedField<a_spot_base, schema::assets::columns::code>>`
note: required by a bound in `coalesce`
   --> src/data_access.rs:593:21
    |
593 |                     sql_function! { fn coalesce(x: Nullable<Text>, y: Nullable<Text>, z: Nullable<Text>) -> Text; }
    |                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `coalesce`
    = note: this error originates in the macro `sql_function` (in Nightly builds, run with -Z macro-backtrace for more info)

How could I fix this? Thank you!

[weiznich]

Please provide at least the following information:

• The diesel version you are using
• The rustc version you are using
• Information about your database backend
• All relevant code to reproduce the issue. This normally includes at least:
  • The relevant part of your schema.rs file
  • Definitions of all types/traits/functions not defined in diesel/rust-std

[Ticki84]

Diesel 2.0.0-rc.0
rustc 1.61.0
PostgreSQL 14.5

Schema.rs:

diesel::table! {
    assets (id) {
        id -> Int4,
        code -> Varchar,
    }
}

diesel::table! {
    futures (id) {
        id -> Int4,
        contract_asset_id -> Int4,
    }
}

diesel::table! {
    instruments (id) {
        id -> Int4,
        spot_id -> Nullable<Int4>,
        future_id -> Nullable<Int4>,
        option_id -> Nullable<Int4>,
    }
}

diesel::table! {
    options (id) {
        id -> Int4,
        contract_asset_id -> Int4,
    }
}

diesel::table! {
    spots (id) {
        id -> Int4,
        base_asset_id -> Int4,
    }
}

diesel::joinable!(instruments -> options (option_id));
diesel::joinable!(instruments -> spots (spot_id));

diesel::allow_tables_to_appear_in_same_query!(
    assets,
    futures,
    instruments,
    options,
    spots,
);

Complete code with the query definition:

let (
    a_spot_base,
    a_future_contract,
    a_option_contract,
) = alias!(
    assets as a_spot_base,
    assets as a_future_contract,
    assets as a_option_contract,
);

let mut query = instruments::table
.inner_join(venues::table)
.left_join(
    spots::table
        .on(instruments::spot_id.eq(spots::id.nullable()))
        .inner_join(
            a_spot_base.on(spots::base_asset_id.eq(a_spot_base.field(assets::id))),
        )
)
.left_join(
    futures::table
        .on(instruments::future_id.eq(futures::id.nullable()))
        .inner_join(
            a_future_contract
                .on(futures::contract_asset_id.eq(a_future_contract.field(assets::id))),
        )
)
.left_join(
    options::table
        .on(instruments::option_id.eq(options::id.nullable()))
        .inner_join(
            a_option_contract
                .on(options::contract_asset_id.eq(a_option_contract.field(assets::id))),
        )
)
.into_boxed();

sql_function! { fn coalesce(x: Nullable<Text>, y: Nullable<Text>, z: Nullable<Text>) -> Text; }

query = query.order_by(coalesce(a_spot_base.field(assets::code).asc(), a_future_contract.field(assets::code).asc(), a_option_contract.field(assets::code).asc()));

[weiznich]

This is not related to alias! at all. In fact I can reproduce the same error message with the following minimal example:

diesel::table! {
    users (id) {
        id -> Integer,
        name -> Nullable<Text>,
    }
}

sql_function! { fn coalesce(x: Nullable<Text>, y: Nullable<Text>, z: Nullable<Text>) -> Text; }

fn main() {
    users::table.order_by(coalesce(
        users::name.asc(),
        users::name.asc(),
        users::name.asc(),
    ));
}

This points to the underlying problem: users::name.asc() is not anymore an expression that could be passed to the sql function. In fact the literal translation of such an expression wouldn't be valid SQL. You can fix this by just moving the .asc() call to after the coalesce call. coalesce(users::name, users::name, users::name).asc() works fine for me.