Raw Sql + Paginate results in double SELECT statements

[pdylanross]

Description

Using paginate with a custom query causes inconsistent results. The paginate method will prepend an erroneous SELECT statement that makes it impossible to use in conjunction with regular querying.

Steps to Reproduce

1. Generate a raw sql query
2. Vary usage of paginate vs all based on codepath
3. error returned from database: syntax error at or near "SELECT"

Expected Behavior

Given the following method:

#[derive(FromQueryResult)]
pub struct RaritySearchResult {
    pub tag_key: String,
    pub rank: f64,
}

pub async fn query_rarity_prefix(
    db: &Arc<DbConn>,
    prefix: &str,
    limit: Option<usize>,
) -> Result<Vec<RaritySearchResult>, DbErr> {
    let query = RaritySearchResult::find_by_statement(Statement::from_sql_and_values(
        DbBackend::Postgres,
        r#"
            SELECT
                tag_key,
                rank
            FROM mv_image_tag_classifier_rarity
            WHERE tag_key like $1
            ORDER BY rank asc
            "#,
        vec![format!("{}%", prefix).into()],
    ));

    if let Some(page_size) = limit {
        if let Some(page) = query
            .paginate(db, page_size)
            .fetch_and_next()
            .await?
        {
            Ok(page)
        } else {
            Ok(Vec::new())
        }
    } else {
        query.all(db).await
    }
}

I should be able to pass Some(n) or None as the limit, to turn pagination on and off.

Actual Behavior

When ran with None, and the code path flows to using query.all(db), the query functions as expected. However, when Some(n) is passed to the method and it flows to query.paginate(db, page_size), then the query returns an error. The issue is obvious when looking at the query log:

[2022-10-06T06:05:33Z DEBUG sqlx::query] SELECT SELECT tag_key, rank …; rows affected: 0, rows returned: 0, elapsed: 369.654µs
    
    SELECT
    SELECT
      tag_key,
      rank
    FROM
      mv_image_tag_classifier_rarity
    WHERE
      tag_key like $1
    ORDER BY
      rank asc
    LIMIT
      $2 OFFSET $3
    
[2022-10-06T06:05:33Z INFO  actix_web::middleware::logger] 127.0.0.1 "GET /api/v1/image/tags/by/prefix?prefix=b&limit=20 HTTP/1.1" 500 75 "-" "insomnia/2022.6.0" 0.005967

Happy path for comparison

[2022-10-06T06:05:36Z DEBUG sqlx::query] SELECT tag_key, rank FROM …; rows affected: 1103, rows returned: 1103, elapsed: 42.514ms
    
    SELECT
      tag_key,
      rank
    FROM
      mv_image_tag_classifier_rarity
    WHERE
      tag_key like $1
    ORDER BY
      rank asc
    
[2022-10-06T06:05:36Z INFO  actix_web::middleware::logger] 127.0.0.1 "GET /api/v1/image/tags/by/prefix?prefix=b HTTP/1.1" 200 13905 "-" "insomnia/2022.6.0" 0.057730

Reproduces How Often

Yep

Versions

sea-orm 0.9.3 with sqlx-postgres & tokio

Additional Information

First time using Sea-Orm & the experience has been smooth and pleasant. I've been enjoying the wonderful lib so far :)

Also, I bet there's a way this query can be done without using raw queries, but I was unsure of how to query a materialized view.

[billy1624]

Hey @pdylanross, thanks for the bug report!! Turns out the leading spaces of the raw SQL cause the bug. #1094 will fix the issue. Thanks!!