Repository: diesel-rs/diesel (language: rust)

Issue:
`eq_any()` is currently unsupported for `CombineDsl` subqueries
<!--
If you want to report a bug, we added some points below which help us track down the problem faster.
-->

## Setup

### Versions

- **Rust:** rustc 1.66.0-nightly (6b3ede3f7 2022-10-13)
- **Diesel:** 2.0.2
- **Database:** sqlite
- **Operating System** Linux

### Feature Flags

- **diesel:** = { version = "2.0.2", features = ["sqlite", "chrono"] }

## Problem Description

Using `eq_any()` fails in this code snippet:
```
 use crate::schema::*;

    users::dsl::users.distinct().filter(
        users::id.eq_any(
            topics::dsl::topics.select(topics::user_id).filter(topics::id.eq(t_id)).union(
                posts::dsl::posts
                    .select(posts::user_id)
                    .filter(posts::topic_id.eq(t_id)),
            ),
        ),
    ).load::<User>(&mut connection)
```

With this error:
```
nix-shell ❯ cargo check                                                                                                                                                                                                                          impure ❄
    Checking vpub-rust v0.1.0 (/home/pinpox/code/github.com/pinpox/vpub-rust)
warning: unused import: `crate::schema::posts`
  --> src/models/user.rs:22:5
   |
22 | use crate::schema::posts;
   |     ^^^^^^^^^^^^^^^^^^^^
   |
   = note: `#[warn(unused_imports)]` on by default

warning: unused import: `crate::schema::topics`
  --> src/models/user.rs:23:5
   |
23 | use crate::schema::topics;
   |     ^^^^^^^^^^^^^^^^^^^^^

error[E0277]: `diesel::query_builder::combination_clause::CombinationClause<diesel::query_builder::combination_clause::Union, diesel::query_builder::combination_clause::Distinct, SelectStatement<FromClause<schema::topics::table>, diesel::query_builder::select_clause::SelectClause<schema::topics::columns::user_id>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::Eq<schema::topics::columns::id, expression::bound::Bound<diesel::sql_types::Integer, i32>>>>>, SelectStatement<FromClause<schema::posts::table>, diesel::query_builder::select_clause::SelectClause<schema::posts::columns::user_id>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::Eq<schema::posts::columns::topic_id, expression::bound::Bound<diesel::sql_types::Integer, i32>>>>>>` is not an iterator
   --> src/models/user.rs:107:19
    |
107 |         users::id.eq_any(
    |                   ^^^^^^ `diesel::query_builder::combination_clause::CombinationClause<diesel::query_builder::combination_clause::Union, diesel::query_builder::combination_clause::Distinct, SelectStatement<FromClause<schema::topics::table>, diesel::query_builder::select_clause::SelectClause<schema::topics::columns::user_id>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::Eq<schema::topics::columns::id, expression::bound::Bound<diesel::sql_types::Integer, i32>>>>>, SelectStatement<FromClause<schema::posts::table>, diesel::query_builder::select_clause::SelectClause<schema::posts::columns::user_id>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::Eq<schema::posts::columns::topic_id, expression::bound::Bound<diesel::sql_types::Integer, i32>>>>>>` is not an iterator
    |
    = help: the trait `Iterator` is not implemented for `diesel::query_builder::combination_clause::CombinationClause<diesel::query_builder::combination_clause::Union, diesel::query_builder::combination_clause::Distinct, SelectStatement<FromClause<schema::topics::table>, diesel::query_builder::select_clause::SelectClause<schema::topics::columns::user_id>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::Eq<schema::topics::columns::id, expression::bound::Bound<diesel::sql_types::Integer, i32>>>>>, SelectStatement<FromClause<schema::posts::table>, diesel::query_builder::select_clause::SelectClause<schema::posts::columns::user_id>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::Eq<schema::posts::columns::topic_id, expression::bound::Bound<diesel::sql_types::Integer, i32>>>>>>`
    = help: the following other types implement trait `expression::array_comparison::AsInExpression<T>`:
              BoxedSelectStatement<'a, ST, QS, DB, GB>
              SelectStatement<F, S, D, W, O, LOf, G, H, LC>
    = note: required for `diesel::query_builder::combination_clause::CombinationClause<diesel::query_builder::combination_clause::Union, diesel::query_builder::combination_clause::Distinct, SelectStatement<FromClause<schema::topics::table>, diesel::query_builder::select_clause::SelectClause<schema::topics::columns::user_id>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::Eq<schema::topics::columns::id, expression::bound::Bound<diesel::sql_types::Integer, i32>>>>>, SelectStatement<FromClause<schema::posts::table>, diesel::query_builder::select_clause::SelectClause<schema::posts::columns::user_id>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::Eq<schema::posts::columns::topic_id, expression::bound::Bound<diesel::sql_types::Integer, i32>>>>>>` to implement `IntoIterator`
    = note: required for `diesel::query_builder::combination_clause::CombinationClause<diesel::query_builder::combination_clause::Union, diesel::query_builder::combination_clause::Distinct, SelectStatement<FromClause<schema::topics::table>, diesel::query_builder::select_clause::SelectClause<schema::topics::columns::user_id>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::Eq<schema::topics::columns::id, expression::bound::Bound<diesel::sql_types::Integer, i32>>>>>, SelectStatement<FromClause<schema::posts::table>, diesel::query_builder::select_clause::SelectClause<schema::posts::columns::user_id>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<expression::grouped::Grouped<expression::operators::Eq<schema::posts::columns::topic_id, expression::bound::Bound<diesel::sql_types::Integer, i32>>>>>>` to implement `expression::array_comparison::AsInExpression<diesel::sql_types::Text>`

For more information about this error, try `rustc --explain E0277`.
warning: `vpub-rust` (lib) generated 2 warnings
error: could not compile `vpub-rust` due to previous error; 2 warnings emitted
```

@weiznich mentioned over on Matrix that there needs to be added a imple simlar to [this one](https://github.com/diesel-rs/diesel/blob/5dc6b2a9e681c2bf0880b9b75c84edd784e8f221/diesel/src/expression/array_comparison.rs#L205-L217) for [this type](https://github.com/diesel-rs/diesel/blob/5dc6b2a9e681c2bf0880b9b75c84edd784e8f221/diesel/src/query_builder/combination_clause.rs#L26) for this to be fixed 

### What are you trying to accomplish?

I'm trying to use diesel's DSL to "translate" this query
```
select * from users where id in (select user_id from topics where id = ? union select user_id from posts where topic_id = ?)
```
### What is the expected output?

The users from the users table, who's ID matches either:
- user_id from the topic from the topics table with id = ?
- user_id from any of the posts of the posts table that have topic_id = ?

### What is the actual output?
See error above

### Are you seeing any additional errors?
Only the one above

### Steps to reproduce

Try to run above code snippet with a sqlite DB with these migrations: https://github.com/pinpox/rust-forum/tree/00f5e3d167d839c8280ea1182ecbb853e767d2da/migrations

<!--
Please include as much of your codebase as needed to reproduce the error.  If the relevant files are large, please consider linking to a public repository or a [Gist](https://gist.github.com/). This includes normally the following parts:

* The exact code where your hit the problem
* Relevant parts your schema, so any `table!` macro calls required for the
* Any other type definitions involved in the code, which produces your problem
-->

## Checklist

- [x] I have already looked over the [issue tracker](https://github.com/diesel-rs/diesel/issues)  and the [disussion forum](https://github.com/diesel-rs/diesel/discussions)  for similar possible closed issues.
<!--
If you are unsure if your issue is a duplicate of an existing issue please link the issue in question here
--> 
- [x] This issue can be reproduced on Rust's stable channel. (Your issue will be
  closed if this is not the case)
- [x] This issue can be reproduced without requiring a third party crate

<!--
Thank you for your submission!  You're helping make Diesel more robust 🎉

We'll try to respond as quickly as possible.
-->



Discussion / hints:

Fix patch:
diff --git a/diesel/src/expression/array_comparison.rs b/diesel/src/expression/array_comparison.rs
index 9b914d96c9cd..19a40bacfc35 100644
--- a/diesel/src/expression/array_comparison.rs
+++ b/diesel/src/expression/array_comparison.rs
@@ -9,6 +9,7 @@ use crate::expression::{
     AppearsOnTable, AsExpression, Expression, SelectableExpression, TypedExpressionType,
     ValidGrouping,
 };
+use crate::query_builder::combination_clause::CombinationClause;
 use crate::query_builder::{
     AstPass, BoxedSelectStatement, QueryFragment, QueryId, SelectQuery, SelectStatement,
 };
@@ -228,6 +229,20 @@ where
     }
 }
 
+impl<ST, Combinator, Rule, Source, Rhs> AsInExpression<ST>
+    for CombinationClause<Combinator, Rule, Source, Rhs>
+where
+    ST: SqlType + TypedExpressionType,
+    Self: SelectQuery<SqlType = ST>,
+    Subselect<Self, ST>: Expression<SqlType = ST>,
+{
+    type InExpression = Subselect<Self, ST>;
+
+    fn as_in_expression(self) -> Self::InExpression {
+        Subselect::new(self)
+    }
+}
+
 /// Query dsl node for a `IN (values)` clause containing
 /// a variable number of bind values.
 ///
diff --git a/diesel/src/query_builder/combination_clause.rs b/diesel/src/query_builder/combination_clause.rs
index 23a7002e0a6c..39e68128230a 100644
--- a/diesel/src/query_builder/combination_clause.rs
+++ b/diesel/src/query_builder/combination_clause.rs
@@ -2,9 +2,10 @@
 //! with or without `ALL` rule for duplicates
 
 use crate::backend::{Backend, DieselReserveSpecialization};
+use crate::expression::subselect::ValidSubselect;
 use crate::expression::NonAggregate;
 use crate::query_builder::insert_statement::InsertFromSelect;
-use crate::query_builder::{AsQuery, AstPass, Query, QueryFragment, QueryId};
+use crate::query_builder::{AsQuery, AstPass, Query, QueryFragment, QueryId, SelectQuery};
 use crate::{CombineDsl, Insertable, QueryResult, RunQueryDsl, Table};
 
 #[derive(Debug, Clone, Copy, QueryId)]
@@ -55,6 +56,22 @@ where
     type SqlType = Source::SqlType;
 }
 
+impl<Combinator, Rule, Source, Rhs> SelectQuery for CombinationClause<Combinator, Rule, Source, Rhs>
+where
+    Source: SelectQuery,
+    Rhs: SelectQuery<SqlType = Source::SqlType>,
+{
+    type SqlType = Source::SqlType;
+}
+
+impl<Combinator, Rule, Source, Rhs, QS> ValidSubselect<QS>
+    for CombinationClause<Combinator, Rule, Source, Rhs>
+where
+    Source: ValidSubselect<QS>,
+    Rhs: ValidSubselect<QS>,
+{
+}
+
 impl<Combinator, Rule, Source, Rhs, Conn> RunQueryDsl<Conn>
     for CombinationClause<Combinator, Rule, Source, Rhs>
 {


Test patch:
diff --git a/diesel_cli/tests/migration_redo.rs b/diesel_cli/tests/migration_redo.rs
index 07809e6060f0..d309f349a569 100644
--- a/diesel_cli/tests/migration_redo.rs
+++ b/diesel_cli/tests/migration_redo.rs
@@ -337,7 +337,7 @@ fn migration_redo_n_with_a_string_should_throw_an_error() {
 
     assert!(
         result.stderr()
-            == "error: Invalid value \"infinite\" for '--number <REDO_NUMBER>': \
+            == "error: Invalid value 'infinite' for '--number <REDO_NUMBER>': \
                 invalid digit found in string\n\n\
                 For more information try '--help'\n",
         "Unexpected stderr : '{}'",
diff --git a/diesel_cli/tests/migration_revert.rs b/diesel_cli/tests/migration_revert.rs
index b21e43ed3615..67293fc80035 100644
--- a/diesel_cli/tests/migration_revert.rs
+++ b/diesel_cli/tests/migration_revert.rs
@@ -288,7 +288,7 @@ fn migration_revert_n_with_a_string_should_throw_an_error() {
 
     assert!(
         result.stderr()
-            == "error: Invalid value \"infinite\" for '--number <REVERT_NUMBER>': \
+            == "error: Invalid value 'infinite' for '--number <REVERT_NUMBER>': \
                 invalid digit found in string\n\n\
                 For more information try '--help'\n",
         "Unexpected stderr : {}",
diff --git a/diesel_compile_tests/Cargo.lock b/diesel_compile_tests/Cargo.lock
index 0da3f61c940d..ee009f56d994 100644
--- a/diesel_compile_tests/Cargo.lock
+++ b/diesel_compile_tests/Cargo.lock
@@ -58,7 +58,7 @@ dependencies = [
 
 [[package]]
 name = "diesel"
-version = "2.0.0"
+version = "2.0.2"
 dependencies = [
  "bigdecimal",
  "bitflags",
@@ -92,7 +92,7 @@ dependencies = [
 
 [[package]]
 name = "diesel_derives"
-version = "2.0.0"
+version = "2.0.1"
 dependencies = [
  "proc-macro-error",
  "proc-macro2",
diff --git a/diesel_compile_tests/tests/fail/exists_can_only_take_subselects.stderr b/diesel_compile_tests/tests/fail/exists_can_only_take_subselects.stderr
index 0f6c80255e9d..60fac020c7cc 100644
--- a/diesel_compile_tests/tests/fail/exists_can_only_take_subselects.stderr
+++ b/diesel_compile_tests/tests/fail/exists_can_only_take_subselects.stderr
@@ -7,6 +7,7 @@ error[E0277]: the trait bound `bool: SelectQuery` is not satisfied
    = help: the following other types implement trait `SelectQuery`:
              BoxedSelectStatement<'a, ST, QS, DB, GB>
              SelectStatement<F, S, D, W, O, LOf, G, H, LC>
+             diesel::query_builder::combination_clause::CombinationClause<Combinator, Rule, Source, Rhs>
    = note: required because of the requirements on the impl of `diesel::Expression` for `diesel::expression::subselect::Subselect<bool, Bool>`
    = note: 1 redundant requirement hidden
    = note: required because of the requirements on the impl of `diesel::Expression` for `diesel::expression::exists::Exists<bool>`
@@ -21,6 +22,7 @@ error[E0277]: the trait bound `users::columns::id: SelectQuery` is not satisfied
    = help: the following other types implement trait `SelectQuery`:
              BoxedSelectStatement<'a, ST, QS, DB, GB>
              SelectStatement<F, S, D, W, O, LOf, G, H, LC>
+             diesel::query_builder::combination_clause::CombinationClause<Combinator, Rule, Source, Rhs>
    = note: required because of the requirements on the impl of `diesel::Expression` for `diesel::expression::subselect::Subselect<users::columns::id, Bool>`
    = note: 1 redundant requirement hidden
    = note: required because of the requirements on the impl of `diesel::Expression` for `diesel::expression::exists::Exists<users::columns::id>`
diff --git a/diesel_tests/tests/combination.rs b/diesel_tests/tests/combination.rs
index 262fcde7ebb5..ea6a05e68cdf 100644
--- a/diesel_tests/tests/combination.rs
+++ b/diesel_tests/tests/combination.rs
@@ -146,3 +146,33 @@ fn union_with_order() {
 
     assert_eq!(vec![String::from("Jim"), "Sean".into()], users);
 }
+
+#[test]
+fn as_subquery_for_eq_in() {
+    let conn = &mut connection_with_sean_and_tess_in_users_table();
+
+    insert_into(posts::table)
+        .values(&[
+            (posts::user_id.eq(1), posts::title.eq("First post")),
+            (posts::user_id.eq(2), posts::title.eq("Second post")),
+        ])
+        .execute(conn)
+        .unwrap();
+
+    let subquery = users::table
+        .select(users::id)
+        .filter(users::name.eq("Sean"))
+        .union(
+            users::table
+                .select(users::id)
+                .filter(users::name.ne("Sean")),
+        );
+
+    let out = posts::table
+        .filter(posts::user_id.eq_any(subquery))
+        .select(posts::title)
+        .load::<String>(conn)
+        .unwrap();
+
+    assert_eq!(out, vec!["First post", "Second post"]);
+}