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

Issue:
Can not select from joined table in query with self referential count subquery
## Setup

### Versions
Rust: rustc 1.63.0 (4b91a6ea7 2022-08-08)
Diesel: 2.0.0
Database: PostgreSQL
Operating System: Linux 5.10.136-1-MANJARO 

### Feature Flags
`diesel = { version = "2.0.0", features = ["postgres", "r2d2",] }`


## Problem Description
I'm getting a type mismatch when trying to include fields from another table in the same query as a count subquery to the first table.

### What are you trying to accomplish?
I got two tables: `script_tag` `tag` and I am trying to join these while also counting the number of occurances of `tag_id` for each row. This is a somewhat boiled down version SQL I'm aiming to generate:
```
SELECT
	tags.name,
	a.script_id,
	a.is_output,
	(
		SELECT COUNT(*)
		FROM script_tag_assoc AS b
		WHERE ((b.tag_id = a.tag_id)
		AND (b.is_output = false))
		LIMIT 1
	)
FROM (script_tag_assoc AS a
INNER JOIN tags
ON (a.tag_id = tag.id))
```

### What is the expected output?
I expect to be able to select fields from table `tags` with alias `c` (see code in reproduction repo)

### What is the actual output?
Compile error
```
rror[E0277]: the trait bound `AliasedField<c, tags::columns::name>: SelectableExpression<Alias<a>>` is not satisfied
  --> src/main.rs:34:14
   |
34 |             .select((
   |              ^^^^^^ the trait `SelectableExpression<Alias<a>>` is not implemented for `AliasedField<c, tags::columns::name>`
   |
   = help: the following other types implement trait `SelectableExpression<QS>`:
             <AliasedField<S, C> as SelectableExpression<Alias<S>>>
             <AliasedField<S, C> as SelectableExpression<JoinOn<Join, On>>>
             <AliasedField<S, C> as SelectableExpression<SelectStatement<FromClause<From>>>>
             <AliasedField<S, C> as SelectableExpression<query_source::joins::Join<Left, Right, Inner>>>
             <AliasedField<S, C> as SelectableExpression<query_source::joins::Join<Left, Right, LeftOuter>>>
   = note: required for `(AliasedField<c, tags::columns::name>, AliasedField<a, script_tag_assoc::columns::tag_id>, AliasedField<a, script_tag_assoc::columns::is_output>, diesel::expression::grouped::Grouped<diesel::expression::subselect::Subselect<SelectStatement<FromClause<Alias<b>>, query_builder::select_clause::SelectClause<CountStar>, query_builder::distinct_clause::NoDistinctClause, query_builder::where_clause::WhereClause<diesel::expression::grouped::Grouped<diesel::expression::operators::And<diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<AliasedField<b, script_tag_assoc::columns::tag_id>, AliasedField<a, script_tag_assoc::columns::tag_id>>>, diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<AliasedField<b, script_tag_assoc::columns::is_output>, diesel::expression::bound::Bound<diesel::sql_types::Bool, bool>>>>>>, query_builder::order_clause::NoOrderClause, query_builder::limit_offset_clause::LimitOffsetClause<query_builder::limit_clause::LimitClause<diesel::expression::bound::Bound<BigInt, i64>>, query_builder::offset_clause::NoOffsetClause>>, diesel::sql_types::Nullable<BigInt>>>)` to implement `SelectableExpression<Alias<a>>`
   = note: required for `SelectStatement<FromClause<Alias<a>>>` to implement `SelectDsl<(AliasedField<c, tags::columns::name>, AliasedField<a, script_tag_assoc::columns::tag_id>, AliasedField<a, script_tag_assoc::columns::is_output>, diesel::expression::grouped::Grouped<diesel::expression::subselect::Subselect<SelectStatement<FromClause<Alias<b>>, query_builder::select_clause::SelectClause<CountStar>, query_builder::distinct_clause::NoDistinctClause, query_builder::where_clause::WhereClause<diesel::expression::grouped::Grouped<diesel::expression::operators::And<diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<AliasedField<b, script_tag_assoc::columns::tag_id>, AliasedField<a, script_tag_assoc::columns::tag_id>>>, diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<AliasedField<b, script_tag_assoc::columns::is_output>, diesel::expression::bound::Bound<diesel::sql_types::Bool, bool>>>>>>, query_builder::order_clause::NoOrderClause, query_builder::limit_offset_clause::LimitOffsetClause<query_builder::limit_clause::LimitClause<diesel::expression::bound::Bound<BigInt, i64>>, query_builder::offset_clause::NoOffsetClause>>, diesel::sql_types::Nullable<BigInt>>>)>`

error[E0271]: type mismatch resolving `<Alias<a> as AppearsInFromClause<Alias<c>>>::Count == diesel::query_source::Once`
  --> src/main.rs:34:14
   |
34 |             .select((
   |              ^^^^^^ expected struct `diesel::query_source::Never`, found struct `diesel::query_source::Once`
   |
   = note: required for `AliasedField<c, tags::columns::name>` to implement `AppearsOnTable<Alias<a>>`
   = note: 1 redundant requirement hidden
   = note: required for `(AliasedField<c, tags::columns::name>, AliasedField<a, script_tag_assoc::columns::tag_id>, AliasedField<a, script_tag_assoc::columns::is_output>, diesel::expression::grouped::Grouped<diesel::expression::subselect::Subselect<SelectStatement<FromClause<Alias<b>>, query_builder::select_clause::SelectClause<CountStar>, query_builder::distinct_clause::NoDistinctClause, query_builder::where_clause::WhereClause<diesel::expression::grouped::Grouped<diesel::expression::operators::And<diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<AliasedField<b, script_tag_assoc::columns::tag_id>, AliasedField<a, script_tag_assoc::columns::tag_id>>>, diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<AliasedField<b, script_tag_assoc::columns::is_output>, diesel::expression::bound::Bound<diesel::sql_types::Bool, bool>>>>>>, query_builder::order_clause::NoOrderClause, query_builder::limit_offset_clause::LimitOffsetClause<query_builder::limit_clause::LimitClause<diesel::expression::bound::Bound<BigInt, i64>>, query_builder::offset_clause::NoOffsetClause>>, diesel::sql_types::Nullable<BigInt>>>)` to implement `AppearsOnTable<Alias<a>>`
   = note: required for `(AliasedField<c, tags::columns::name>, AliasedField<a, script_tag_assoc::columns::tag_id>, AliasedField<a, script_tag_assoc::columns::is_output>, diesel::expression::grouped::Grouped<diesel::expression::subselect::Subselect<SelectStatement<FromClause<Alias<b>>, query_builder::select_clause::SelectClause<CountStar>, query_builder::distinct_clause::NoDistinctClause, query_builder::where_clause::WhereClause<diesel::expression::grouped::Grouped<diesel::expression::operators::And<diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<AliasedField<b, script_tag_assoc::columns::tag_id>, AliasedField<a, script_tag_assoc::columns::tag_id>>>, diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<AliasedField<b, script_tag_assoc::columns::is_output>, diesel::expression::bound::Bound<diesel::sql_types::Bool, bool>>>>>>, query_builder::order_clause::NoOrderClause, query_builder::limit_offset_clause::LimitOffsetClause<query_builder::limit_clause::LimitClause<diesel::expression::bound::Bound<BigInt, i64>>, query_builder::offset_clause::NoOffsetClause>>, diesel::sql_types::Nullable<BigInt>>>)` to implement `SelectableExpression<Alias<a>>`
   = note: required for `SelectStatement<FromClause<Alias<a>>>` to implement `SelectDsl<(AliasedField<c, tags::columns::name>, AliasedField<a, script_tag_assoc::columns::tag_id>, AliasedField<a, script_tag_assoc::columns::is_output>, diesel::expression::grouped::Grouped<diesel::expression::subselect::Subselect<SelectStatement<FromClause<Alias<b>>, query_builder::select_clause::SelectClause<CountStar>, query_builder::distinct_clause::NoDistinctClause, query_builder::where_clause::WhereClause<diesel::expression::grouped::Grouped<diesel::expression::operators::And<diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<AliasedField<b, script_tag_assoc::columns::tag_id>, AliasedField<a, script_tag_assoc::columns::tag_id>>>, diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<AliasedField<b, script_tag_assoc::columns::is_output>, diesel::expression::bound::Bound<diesel::sql_types::Bool, bool>>>>>>, query_builder::order_clause::NoOrderClause, query_builder::limit_offset_clause::LimitOffsetClause<query_builder::limit_clause::LimitClause<diesel::expression::bound::Bound<BigInt, i64>>, query_builder::offset_clause::NoOffsetClause>>, diesel::sql_types::Nullable<BigInt>>>)>`
```

### Are you seeing any additional errors?
No

### Steps to reproduce
https://github.com/jakob-lilliemarck/bug_report

## 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



Discussion / hints:
It appears changing the order of calls to `inner_join()` and `select()` made a difference - so perhaps this issue is not really a bug, but merely something to be further clarified in the documentation?

Doing this instead it seems to all check out:
```
let query = a
            .inner_join(schema::tags::dsl::name)
            .select((
                schema::tags::dsl::name,
                a.field(schema::script_tag_assoc::dsl::tag_id),
                a.field(schema::script_tag_assoc::dsl::is_output),
                b.filter(
                    b.field(schema::script_tag_assoc::dsl::tag_id)
                        .eq(a.field(schema::script_tag_assoc::dsl::tag_id))
                        .and(b.field(schema::script_tag_assoc::dsl::is_output).eq(false)),
                )
                .count()
                .single_value(),
            ));
``` 
As you already noticed: This depends on the order you call `.inner_join` and `.select`. Thats because the later function can only access type information provided up to this point. So for your example it just don't know about the join and therefore rejects the select clause as invalid. 

I would accept a PR clarifying that in the documentation of `QueryDsl::select`.
Hello,
I'm interested in doing this PR.
I saw that there is already an example in the `inner_join` documentation <https://docs.diesel.rs/master/diesel/prelude/trait.QueryDsl.html#examples-1> , but I can add a clarification in `QueryDsl::select` if you want
@JeremyLARDENOIS That would be welcome. I think the only thing that's required here is to explicitly state that for `select` (and `filter`, …) you can only access columns from tables that appear in your query before that function call. Might be worth to add such a note to `QueryDsl::inner_join`, `QueryDsl::select` and `QueryDsl::filter` as that are the commonly used methods.
Okay I see, I will do that.

Fix patch:
diff --git a/diesel/src/query_dsl/mod.rs b/diesel/src/query_dsl/mod.rs
index 227bf22047bd..e03700fe6d11 100644
--- a/diesel/src/query_dsl/mod.rs
+++ b/diesel/src/query_dsl/mod.rs
@@ -199,6 +199,16 @@ pub trait QueryDsl: Sized {
     /// column. `title.nullable()`, `lower(title).nullable()`, and `(id,
     /// title).nullable()` would all be valid.
     ///
+    /// In order to use this method with columns from different tables
+    /// a method like [`.inner_join`] or [`.left_join`] needs to be called before
+    /// calling [`.select`] (See examples below).
+    /// This is because you can only access columns from tables
+    /// that appear in your query before that function call.
+    ///
+    /// [`.inner_join`]: QueryDsl::inner_join()
+    /// [`.left_join`]: QueryDsl::left_join()
+    /// [`.select`]: QueryDsl::select()
+    ///
     /// # Examples
     ///
     /// ```rust
@@ -382,6 +392,12 @@ pub trait QueryDsl: Sized {
     /// [associations]: crate::associations
     /// [`allow_tables_to_appear_in_same_query!`]: crate::allow_tables_to_appear_in_same_query!
     ///
+    /// Note that in order to use this method with [`.select`], you will need to use it before calling
+    /// [`.select`] (See examples below). This is because you can only access columns from tables
+    /// that appear in your query before the call to [`.select`].
+    ///
+    /// [`.select`]: QueryDsl::select()
+    ///
     /// # Examples
     ///
     /// ### With implicit `ON` clause
@@ -603,6 +619,15 @@ pub trait QueryDsl: Sized {
     ///
     /// If there is already a `WHERE` clause, the result will be `old AND new`.
     ///
+    /// Note that in order to use this method with columns from different tables, you need to call
+    //  [`.inner_join`] or [`.left_join`] beforehand.
+    /// This is because you can only access columns from tables
+    /// that appear in your query before the call to [`.filter`].
+    ///
+    /// [`.inner_join`]: QueryDsl::inner_join()
+    /// [`.left_join`]: QueryDsl::left_join()
+    /// [`.filter`]: QueryDsl::filter()
+    ///
     /// # Example:
     ///
     /// ```rust


Test patch: