query_as! macro doesn't map Uuid properly with SQLite

[rtyler]

Bug Description

The query_as! macro does not appear to be able to understand the mapping of Uuid types in and out of SQLIte, while FromRow and query_as offer no complaints. It basically seems to try to deserialize a String when it should be deserializing a `Uuid, resulting in the following error:

error[E0308]: mismatched types
  --> src/main.rs:27:18
   |
27 |     let record = sqlx::query_as!(WithUuid, "SELECT * FROM with_uuid ORDER BY id DESC")
   |                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ expected struct `Uuid`, found struct `std::string::String`
   |
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as` (in Nightly builds, run with -Z macro-backtrace for more info)

As far as I can tell, this means that the macro is not handling Uuid properly
whereas the normal FromRow does

Minimal Reproduction

use serde::Deserialize;
use sqlx::{SqlitePool, FromRow};
use uuid::Uuid;

#[derive(Debug, Deserialize, FromRow)]
struct WithUuid {
    id: i64,
    uuid: Uuid,
    msg: String,
}

#[async_std::main]
async fn main() -> Result<(), sqlx::Error> {
    let database_url = std::env::var("DATABASE_URL").unwrap_or(":memory:".to_string());
    let pool = SqlitePool::connect(&database_url).await?;
    sqlx::migrate!().run(&pool).await?;

    sqlx::query("INSERT INTO with_uuid (uuid, msg) VALUES (?, ?)")
        .bind(Uuid::new_v4())
        .bind("Hello World")
        .execute(&pool)
        .await?;

    let record: WithUuid = sqlx::query_as("SELECT * FROM with_uuid ORDER BY id DESC")
        .fetch_one(&pool).await?;
    let record = sqlx::query_as!(WithUuid, "SELECT * FROM with_uuid ORDER BY id DESC")
        .fetch_one(&pool).await?;
    println!("Fetched one record with a text UUID: {:?}", record);

    Ok(())
}

When the query_as! line is commented out, the program compiles and executes just fine.

Info

• SQLx version: 0.6.2
• SQLx features enabled: ["chrono", "json", "migrate", "sqlite", "uuid", "runtime-async-std-rustls"]
• Database server and version: SQLite 3.39.3
• Operating system: Linux/amd64
• rustc --version: 1.66.1

[CosmicHorrorDev]

You likely need a type override on the output column. SQLite doesn't have a unique type for UUIDs so the type inference is going to be very limited

https://docs.rs/sqlx/latest/sqlx/macro.query.html#type-overrides-output-columns

[rtyler]

@CosmicHorrorDev from our documentation on sqlite types, Uuid is supposed to work here as far as I can tell.

[CosmicHorrorDev]

It works as in you can store and retrieve data for that type, but it still needs a type override to specify that type

query_as!() doesn't have type information on the struct that it returns, so it just naively infers what the type should be based on the type in the database. That type is ambiguous in this case because TEXT can map to several Rust types hence you need a type override to specify the correct type

[rtyler]

I can verify that behavior and understand the trade-off being made here.

I am closing this issue, but I believe that the docs should be updated to make it clear that type overrides are needed for almost all of these types supported

[7sedam7]

docs are still not updated