Using `single_value` for non-nullable value in `replace_into` statement

[ashleyrdudek]

Setup

Versions

• Rust: rustc 1.39.0 (4560ea788 2019-11-04)
• Diesel: 1.4.3
• Database: SQLite
• Operating System macos

Feature Flags

• diesel: sqlite, r2d2

Problem Description

What are you trying to accomplish?

I am trying to use single_value() on a replace statement for a non-nullable value. I understand why this is an issue with insert statements (seen in #1918) but replace_into() is an upsert statement for sqlite. Using the following SQL table and command successfully upserts a table entry in SQLite:

CREATE TABLE subscriptions (
  id INTEGER NOT NULL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  timestamp INTEGER NOT NULL
);

INSERT OR UPDATE INTO subscriptions (id, name, timestamp)
VALUES (
   (SELECT id FROM subscriptions WHERE name = "example_name"),
   "example_name",
   9000
);

Here are some snippets of my code (again, very similar to #1918)

schema.rs

table! {
  subscription (id) {
    id -> Integer,
    name -> Text,
    timestamp -> Integer,
  }
}

mod.rs

replace_into(subscription)
  .values((
    id.eq(
      subscription
        .select(id)
        .filter(
          name.eq(&sub_name)
        ).single_value(),
    ),
    name.eq(&sub_name),
    timestamp.eq(&sub_ts)
  )).execute(&conn)

The error I get is:

error[E0271]: type mismatch resolving `<diesel::expression::nullable::Nullable<diesel::expression::grouped::Grouped<diesel::expression::subselect::Subselect<diesel::query_builder::SelectStatement<test::schema::subscriptions::table, diesel::query_builder::select_clause::SelectClause<diesel::expression::nullable::Nullable<azure::resources::sqlite::schema::subscriptions::columns::subsc_id>>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::Eq<azure::resources::sqlite::schema::subscriptions::columns::subsc_name, diesel::expression::bound::Bound<diesel::sql_types::Text, &str>>>, diesel::query_builder::order_clause::NoOrderClause, diesel::query_builder::limit_clause::LimitClause<diesel::expression::bound::Bound<diesel::sql_types::BigInt, i64>>>, diesel::sql_types::Nullable<diesel::sql_types::Integer>>>> as diesel::Expression>::SqlType == diesel::sql_types::Integer`
  --> src/azure/resources/sqlite/mod.rs:26:26
   |
26 |                 id.eq(subscriptions.select(id).filter(name.eq(sub_name)).single_value()), 
   |                    ^^ expected struct `diesel::sql_types::Nullable`, found struct `diesel::sql_types::Integer`
   |
   = note: expected type `diesel::sql_types::Nullable<diesel::sql_types::Integer>`
              found type `diesel::sql_types::Integer`

At first, I was confused because the expected type is Nullable. I proceeded to add the nullable() function to the single_value statement, but I got the same issue. When I replace the single_value statement to a reference to a random integer (&42 for example). The code compiles.

Checklist

• [x] I have already looked over the issue tracker for similar issues.
• [x] This issue can be reproduced on Rust's stable channel. (Your issue will be closed if this is not the case)

[weiznich]

The issue here is that .single_value returns a nullable value, because the subquery could return no value. You id column is not nullable, therefore the error. The same workaround as mentioned in #1918 also applies here.

[ashleyrdudek]

In an upsert query, a null index would result in inserting a new entry with a newly generated index. The documentation/guide says that replace_into is equivalent to an upsert statement, but is lacking this functionality. Are there any plans on expanding replace_into's functionality in the future?

[weiznich]

Could you link the relevant Sqlite documentation here? I did not found anything about this yesterday on a quick search.

[ashleyrdudek]

Apologies, I totally misspoke! I meant INSERT OR REPLACE, not upsert 🤦‍♀, here is some documentation for that. It looks like there was an issue for postgres and sqlite INSERT OR REPLACE statements a loooong time ago, but they didn't mention anything about nullable values back then. Here's a little playground I made with this example. Sorry again for saying the wrong thing! I'll definitely be cringing at that mistake for many days to come 😅

[weiznich]

The linked example does not contain any sort of specification saying what is the behaviour in the null case. Is there anything in the sqlite documentation about this?

[ashleyrdudek]

Here is the replace page, it links to the on conflict page that shows its functionality.