[SQLite] REGEXP feature does not work with SqlitePool

[arionkoder-camilo]

Bug Description

although the feature flag regexp is enabled, query_as! it doesn't detect it and I get error returned from database: (code: 1) no such function: REGEXP

Minimal Reproduction

pub async fn get_notes(db: SqlitePool) -> Result<Vec<Note>, _> {
    let notes = sqlx::query_as!(
        Note,
        r#"SELECT
              text
             FROM
              notes
             WHERE text REGEXP '(foo|bar)'"#
     )
     .fetch_all(&self.db)
     .await?;
        
    Ok(notes)
}

Info

• SQLx version: 0.7.3
• SQLx features enabled: ["runtime-tokio", "sqlite", "json", "regexp"]
• Database server and version: SQLite 3.42.0
• Operating system: Gnu/Linux
• rustc --version: 3.42.0

[Jeremiah-Griffin]

Have you registered the regex function?

To the best of my reading of the code, the regex feature does not included a vendored regex instead exposing a safe api over the create_function FFI

Regex is not meaningfully standardized and regex engines are free to interpret syntax however they please. As such, instead of embedding its own engine or trying to mach bug-for-bug compatibility with every engine likely to be used with sqlite, sqlite exposes REGEXP as an "application defined SQL function", allowing the query engine to call back into the regex engine you provide. This has the additional advantage of keeping binary size down be preventing duplication of the embedded engine, as well as keeping regex behavior within and without sqlite consistent.

[arionkoder-camilo]

I have sqlx = { version = "0.7.3", features = ["runtime-tokio", "sqlite", "json", "regexp" ] } and .with_regexp() in

 let options = SqliteConnectOptions::from_str(&database_url)?
        .read_only(read_only)
        .with_regexp()

Ok(SqlitePoolOptions::new()
        .connect_with(options).await?)

not luck, I still getting the same error and according to the code .with_regexp() changes register_regexp_function to true and here the registration happens https://github.com/launchbadge/sqlx/blob/main/sqlx-sqlite/src/connection/establish.rs#L267, but is not working, maybe I'm missing something

[abonander]

Try regexp instead of REGEXP? I can't find a clear answer on whether function names are case-sensitive, but we register the function with the all-lowercase name.

[filtsin]

Same for me.

sqlx = { version = "0.8", features = ["runtime-tokio", "sqlite", "macros", "migrate", "chrono", "regexp"] }

SqliteConnectOptions with regexp.

error returned from database: (code: 1) no such function: regexp

Tried REGEXP and regexp.

I'm saying about query!. With unchecked query() all works.