query_as!() gives an error for a non-existing column for an expression

[zbrox]

I have found these related issues/pull requests

I haven't found similar issues

Description

Hey,

I've been using the query_as! macro with an expression, doing a similarity search with pgvector.

Here's an example:

CREATE TABLE vectors (
    id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    embedding vector(3072) NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    text text NOT NULL,
    collection TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

#[derive(Debug, Clone, sqlx::FromRow)]
pub struct SearchResult {
    pub id: uuid::Uuid,
    pub metadata: serde_json::Value,
    pub document_id: uuid::Uuid,
    pub text: String,
    pub score: f64,
}

sqlx::query_as!(
    SearchResult,
    r#"SELECT id as "id!:_", text, metadata as "metadata!:_", (1 - (embedding <=> $1)) as "score!:_"
            FROM vectors
            WHERE collection = $2
            ORDER BY score DESC"#,
    vector as pgvector::Vector,
    collection,
)
.fetch_all(&mut *conn)
.await

With this I get the error column "score" does not exist. If I remove the type casting and just specify it as "score" then I get an error because it cannot convert it from an Option.

Reproduction steps

The minimal example above should reproduce the error.

I'm using pgvector 0.4.0.

SQLx version

0.8.3

Enabled SQLx features

"runtime-tokio", "chrono", "uuid", "postgres", "migrate", "derive"

Database server and version

Postgres 17

Operating system

macos 15.3.2

Rust version

1.85.1

[abonander]

The minimal example above should reproduce the error.

@zbrox that is not a sufficiently minimal example. There's no table schema, nor a definition for SearchResult. It should not require guessing what the rest of your code looks like.

[zbrox]

The minimal example above should reproduce the error.

@zbrox that is not a sufficiently minimal example. There's no table schema, nor a definition for SearchResult. It should not require guessing what the rest of your code looks like.

You are right, I'm sorry, I have omitted this in haste. I will edit the report.

[xpe]

@zbrox Any updates? I'm also working through some hiccups with pgvector and pgvector-rust. I'll try to take a look. Maybe we can help each other out.

[zbrox]

@xpe No, nothing new. To work around it quickly I just use it as an Option and then force unwrap it with an expect, knowing that it will always have a value. Not great, but for now it works. I had to move on quickly from this, I didn't have much time unfortunately.

[xpe]

@zbrox You probably already know this, but just in case you don't: there is a lot of detailed documentation about SQLx to be found around option handling. For example:

https://docs.rs/sqlx/latest/sqlx/macro.query.html#overrides-cheatsheet

I refer to that that doc.rs page frequently when I find myself in trouble.

[peterschutt]

Here's a repro that you can run on any postgres schema:

#[derive(Debug, FromRow)]
pub struct TableStats {
    pub relname: String,
    pub n_live_tup: i64,
    pub n_dead_tup: i64,
    pub total_bytes: i64,
    pub table_bytes: i64,
    pub index_bytes: i64,
}

sqlx::query_as!(
    TableStats,
    r#"
    SELECT
        n.relname as "relname!",
        n.n_live_tup as "n_live_tup!",
        n.n_dead_tup as "n_dead_tup!",
        pg_total_relation_size(n.relid) as "total_bytes!",
        pg_relation_size(n.relid) as "table_bytes!",
        pg_indexes_size(n.relid) as "index_bytes!"
    FROM pg_stat_user_tables n
    ORDER BY relname, total_bytes DESC
    "#
)
.fetch_all(&pool)
.await
.context("Failed to fetch database stats")?;

reports

error: error returned from database: column "total_bytes" does not exist
   --> services/archiver/src/service.rs:393:38
    |
393 |           let stats: Vec<TableStats> = sqlx::query_as!(
    |  ______________________________________^
394 | |             TableStats,
395 | |             r#"
396 | |             SELECT
...   |
406 | |             "#
407 | |         )
    | |_________^
    |
    = 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)