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

Issue:
Ambiguous traits in QueryableByName derivation when two fields have same concrete type but different SQL types
<!--
If you want to report a bug, we added some points below which help us track down the problem faster.
-->

## Setup

### Versions

- **Rust:** 1.80.0
- **Diesel:** 2.2.2
- **Database:** PostgreSQL
- **Operating System: ** MacOS

### Feature Flags

- **diesel:** `postgres`

## Problem Description


### What are you trying to accomplish?

Attempting to use `QueryableByName` + `sql_query`. In this case, two fields have the same type in the struct, but different SQL types.

Example:
```rust
#[derive(QueryableByName)]
#[diesel(check_for_backend(Pg))]
struct StructWithTextAndCitext {
    #[diesel(sql_type = sql_types::Text)]
    name: String,
    #[diesel(sql_type = sql_types::Citext)]
    email: String,
}
```

Alternative example (this also requires the `serde_json` feature of diesel):
```rust
#[derive(QueryableByName)]
#[diesel(check_for_backend(Pg))]
struct StructWithJsonAndJsonb {
    #[diesel(sql_type = sql_types::Json)]
    v1: serde_json::Value,
    #[diesel(sql_type = sql_types::Jsonb)]
    v2: serde_json::Value,
}
```

### What is the expected output?

The code compiles, and creates a useful `QueryableByName` trait implementation.

### What is the actual output?

In the first case above, the error is:

```
error[E0283]: type annotations needed
   --> src/main.rs:11:10
    |
11  | #[derive(QueryableByName)]
    |          ^^^^^^^^^^^^^^^ cannot infer type
    |
    = note: cannot satisfy `String: FromSql<_, __DB>`
    = help: the following types implement trait `FromSql<A, DB>`:
              <String as FromSql<Citext, Pg>>
              <String as FromSql<ST, DB>>
note: required by a bound in `diesel::row::NamedRow::get`
   --> /xxx/.cargo/registry/src/index.crates.io-6f17d22bba15001f/diesel-2.2.2/src/row.rs:133:12
    |
131 |     fn get<ST, T>(&self, column_name: &str) -> deserialize::Result<T>
    |        --- required by a bound in this associated function
132 |     where
133 |         T: FromSql<ST, DB>;
    |            ^^^^^^^^^^^^^^^ required by this bound in `NamedRow::get`
    = note: this error originates in the derive macro `QueryableByName` (in Nightly builds, run with -Z macro-backtrace for more info)
```

In the second case, the error is:

```
error[E0283]: type annotations needed
   --> src/main.rs:11:10
    |
11  | #[derive(QueryableByName)]
    |          ^^^^^^^^^^^^^^^ cannot infer type
    |
    = note: cannot satisfy `Value: FromSql<_, __DB>`
    = help: the following types implement trait `FromSql<A, DB>`:
              <Value as FromSql<Json, Pg>>
              <Value as FromSql<Jsonb, Pg>>
note: required by a bound in `diesel::row::NamedRow::get`
   --> /xxx/.cargo/registry/src/index.crates.io-6f17d22bba15001f/diesel-2.2.2/src/row.rs:133:12
    |
131 |     fn get<ST, T>(&self, column_name: &str) -> deserialize::Result<T>
    |        --- required by a bound in this associated function
132 |     where
133 |         T: FromSql<ST, DB>;
    |            ^^^^^^^^^^^^^^^ required by this bound in `NamedRow::get`
    = note: this error originates in the derive macro `QueryableByName` (in Nightly builds, run with -Z macro-backtrace for more info)
```

### Steps to reproduce

Write the code above, with `diesel` dependency, and features `postgres` (and `serde_json` in the second case). If added to `src/main.rs`, you should see the above compile errors. 

## Checklist

- [x] I have already looked over the [issue tracker](https://github.com/diesel-rs/diesel/issues) and the [discussion forum](https://github.com/diesel-rs/diesel/discussions) for similar possible closed issues.
- [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:

Fix patch:
diff --git a/diesel_derives/src/queryable_by_name.rs b/diesel_derives/src/queryable_by_name.rs
index 16763059a70b..7848e20ed58c 100644
--- a/diesel_derives/src/queryable_by_name.rs
+++ b/diesel_derives/src/queryable_by_name.rs
@@ -23,12 +23,13 @@ pub fn derive(item: DeriveInput) -> Result<TokenStream> {
             if f.embed() {
                 Ok(quote!(<#field_ty as QueryableByName<__DB>>::build(row)?))
             } else {
+                let st = sql_type(f, &model)?;
                 let deserialize_ty = f.ty_for_deserialize();
                 let name = f.column_name()?;
                 let name = LitStr::new(&name.to_string(), name.span());
                 Ok(quote!(
                    {
-                       let field = diesel::row::NamedRow::get(row, #name)?;
+                       let field = diesel::row::NamedRow::get::<#st, #deserialize_ty>(row, #name)?;
                        <#deserialize_ty as Into<#field_ty>>::into(field)
                    }
                 ))


Test patch:
diff --git a/diesel_compile_tests/tests/fail/derive/queryable_by_name_requires_table_name_or_sql_type_annotation.stderr b/diesel_compile_tests/tests/fail/derive/queryable_by_name_requires_table_name_or_sql_type_annotation.stderr
index 2dd72fafbb74..f46370568073 100644
--- a/diesel_compile_tests/tests/fail/derive/queryable_by_name_requires_table_name_or_sql_type_annotation.stderr
+++ b/diesel_compile_tests/tests/fail/derive/queryable_by_name_requires_table_name_or_sql_type_annotation.stderr
@@ -6,49 +6,6 @@ error: All fields of tuple structs must be annotated with `#[diesel(column_name)
    |
    = note: this error originates in the derive macro `QueryableByName` (in Nightly builds, run with -Z macro-backtrace for more info)
 
-error[E0277]: cannot deserialize a value of the database type `_` as `i32`
- --> tests/fail/derive/queryable_by_name_requires_table_name_or_sql_type_annotation.rs:4:10
-  |
-4 | #[derive(QueryableByName)]
-  |          ^^^^^^^^^^^^^^^ the trait `FromSql<_, __DB>` is not implemented for `i32`
-  |
-  = note: double check your type mappings via the documentation of `_`
-  = help: the following other types implement trait `FromSql<A, DB>`:
-            <i32 as FromSql<diesel::sql_types::Integer, Mysql>>
-            <i32 as FromSql<diesel::sql_types::Integer, Pg>>
-            <i32 as FromSql<diesel::sql_types::Integer, Sqlite>>
-note: required by a bound in `diesel::row::NamedRow::get`
- --> $DIESEL/src/row.rs
-  |
-  |     fn get<ST, T>(&self, column_name: &str) -> deserialize::Result<T>
-  |        --- required by a bound in this associated function
-  |     where
-  |         T: FromSql<ST, DB>;
-  |            ^^^^^^^^^^^^^^^ required by this bound in `NamedRow::get`
-  = note: this error originates in the derive macro `QueryableByName` (in Nightly builds, run with -Z macro-backtrace for more info)
-
-error[E0277]: cannot deserialize a value of the database type `_` as `*const str`
- --> tests/fail/derive/queryable_by_name_requires_table_name_or_sql_type_annotation.rs:4:10
-  |
-4 | #[derive(QueryableByName)]
-  |          ^^^^^^^^^^^^^^^ the trait `FromSql<_, __DB>` is not implemented for `*const str`, which is required by `std::string::String: FromSql<_, __DB>`
-  |
-  = note: double check your type mappings via the documentation of `_`
-  = help: the following other types implement trait `FromSql<A, DB>`:
-            <*const str as FromSql<diesel::sql_types::Text, Mysql>>
-            <*const str as FromSql<diesel::sql_types::Text, Pg>>
-            <*const str as FromSql<diesel::sql_types::Text, Sqlite>>
-  = note: required for `std::string::String` to implement `FromSql<_, __DB>`
-note: required by a bound in `diesel::row::NamedRow::get`
- --> $DIESEL/src/row.rs
-  |
-  |     fn get<ST, T>(&self, column_name: &str) -> deserialize::Result<T>
-  |        --- required by a bound in this associated function
-  |     where
-  |         T: FromSql<ST, DB>;
-  |            ^^^^^^^^^^^^^^^ required by this bound in `NamedRow::get`
-  = note: this error originates in the derive macro `QueryableByName` (in Nightly builds, run with -Z macro-backtrace for more info)
-
 error[E0433]: failed to resolve: use of undeclared crate or module `foos`
  --> tests/fail/derive/queryable_by_name_requires_table_name_or_sql_type_annotation.rs:5:8
   |
diff --git a/diesel_compile_tests/tests/fail/derive_deprecated/deprecated_sql_type.stderr b/diesel_compile_tests/tests/fail/derive_deprecated/deprecated_sql_type.stderr
index 5b5592b5bf6e..94c66d44a278 100644
--- a/diesel_compile_tests/tests/fail/derive_deprecated/deprecated_sql_type.stderr
+++ b/diesel_compile_tests/tests/fail/derive_deprecated/deprecated_sql_type.stderr
@@ -59,24 +59,3 @@ error[E0412]: cannot find type `foo` in this scope
    |
 27 |     #[sql_type = "foo"]
    |                  ^^^^^ not found in this scope
-
-error[E0277]: cannot deserialize a value of the database type `_` as `i32`
-  --> tests/fail/derive_deprecated/deprecated_sql_type.rs:25:10
-   |
-25 | #[derive(QueryableByName)]
-   |          ^^^^^^^^^^^^^^^ the trait `FromSql<_, __DB>` is not implemented for `i32`
-   |
-   = note: double check your type mappings via the documentation of `_`
-   = help: the following other types implement trait `FromSql<A, DB>`:
-             <i32 as FromSql<diesel::sql_types::Integer, Mysql>>
-             <i32 as FromSql<diesel::sql_types::Integer, Pg>>
-             <i32 as FromSql<diesel::sql_types::Integer, Sqlite>>
-note: required by a bound in `diesel::row::NamedRow::get`
-  --> $DIESEL/src/row.rs
-   |
-   |     fn get<ST, T>(&self, column_name: &str) -> deserialize::Result<T>
-   |        --- required by a bound in this associated function
-   |     where
-   |         T: FromSql<ST, DB>;
-   |            ^^^^^^^^^^^^^^^ required by this bound in `NamedRow::get`
-   = note: this error originates in the derive macro `QueryableByName` (in Nightly builds, run with -Z macro-backtrace for more info)
diff --git a/diesel_derives/tests/queryable_by_name.rs b/diesel_derives/tests/queryable_by_name.rs
index d865ebba8804..4135935b4b2f 100644
--- a/diesel_derives/tests/queryable_by_name.rs
+++ b/diesel_derives/tests/queryable_by_name.rs
@@ -12,6 +12,15 @@ table! {
     }
 }
 
+#[cfg(feature = "sqlite")]
+table! {
+    multiple_sql_types_for_text {
+        id -> Integer,
+        string -> Text,
+        time -> Timestamp,
+    }
+}
+
 #[test]
 fn named_struct_definition() {
     #[derive(Debug, Clone, Copy, PartialEq, Eq, QueryableByName)]
@@ -86,8 +95,50 @@ fn struct_with_path_in_name() {
     );
 }
 
-// FIXME: Test usage with renamed columns
+#[cfg(feature = "sqlite")]
+#[test]
+fn struct_with_multiple_sql_types_for_text() {
+    #[derive(Debug, PartialEq, QueryableByName)]
+    struct MultipleSqlTypesForText {
+        #[diesel(sql_type = diesel::sql_types::Text)]
+        string: String,
+        #[diesel(sql_type = diesel::sql_types::Timestamp)]
+        time: String,
+    }
+
+    let conn = &mut connection();
+    let data = sql_query("SELECT 'name' AS string, '2024-07-31T21:09:00' AS time").get_result(conn);
+    assert_eq!(
+        Ok(MultipleSqlTypesForText {
+            string: "name".into(),
+            time: "2024-07-31T21:09:00".into()
+        }),
+        data
+    );
+}
 
+#[cfg(feature = "sqlite")]
+#[test]
+fn struct_with_multiple_sql_types_for_text_from_table() {
+    #[derive(Debug, PartialEq, QueryableByName)]
+    #[diesel(table_name = multiple_sql_types_for_text)]
+    struct MultipleSqlTypesForText {
+        string: String,
+        time: String,
+    }
+
+    let conn = &mut connection();
+    let data = sql_query("SELECT 'name' AS string, '2024-07-31T21:09:00' AS time").get_result(conn);
+    assert_eq!(
+        Ok(MultipleSqlTypesForText {
+            string: "name".into(),
+            time: "2024-07-31T21:09:00".into()
+        }),
+        data
+    );
+}
+
+// FIXME: Test usage with renamed columns
 #[test]
 fn struct_with_no_table() {
     #[derive(Debug, Clone, Copy, PartialEq, Eq, QueryableByName)]