sqlite: `REGEXP` rejects non-`TEXT` values instead of using SQLite text coercion

[meng-xu-cs]

I have found these related issues/pull requests

The original REGEXP PR (#2189) already noted that the implementation was "very strict" and "only accepts UTF-8 columns in both the field and the REGEXP", but I could not find a dedicated bug report for the non-TEXT coercion behavior itself.

Description

When SqliteConnectOptions::with_regexp() is enabled, REGEXP only works if both arguments already have SQLite storage class TEXT. Values with storage class INTEGER, REAL, or expression results are silently treated as unsupported and the function returns NULL.

This diverges from normal SQLite text-access semantics. sqlite3_value_type() reports the initial type, and calling sqlite3_value_text() may coerce non-text values to text. SQLite documents X REGEXP Y as a call to the application-defined regexp(Y, X) function, so a regexp() implementation that wants SQLite-style text coercion should use sqlite3_value_text() on non-NULL inputs instead of rejecting anything not already tagged SQLITE_TEXT.

The current implementation gates both arguments through:

let ty = ffi::sqlite3_value_type(arg);
if ty == ffi::SQLITE_TEXT {
    let ptr = ffi::sqlite3_value_text(arg);
    ...
} else {
    None
}

Because sqlite3_regexp_func() uses let Some(...) = ... else { return; } for both the pattern and the value, non-TEXT arguments return early without producing a boolean result.

Reproduction steps

use std::str::FromStr;

use sqlx::{ConnectOptions, Connection};
use sqlx_sqlite::SqliteConnectOptions;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let mut conn = SqliteConnectOptions::from_str("sqlite://:memory:")?
        .with_regexp()
        .connect()
        .await?;

    for sql in [
        "SELECT regexp('23', 123)",
        "SELECT 123 REGEXP '23'",
        "SELECT regexp('12\\.5', 12.5)",
        "SELECT regexp('1', TRUE)",
        "SELECT regexp('23', CAST(123 AS TEXT))",
    ] {
        let value: Option<i64> = sqlx::query_scalar(sql).fetch_one(&mut conn).await?;
        println!("{sql} => {value:?}");
    }

    sqlx::query("CREATE TABLE t(x INTEGER NOT NULL)")
        .execute(&mut conn)
        .await?;
    sqlx::query("INSERT INTO t(x) VALUES (123), (45)")
        .execute(&mut conn)
        .await?;

    let rows: Vec<i64> = sqlx::query_scalar("SELECT x FROM t WHERE x REGEXP '23'")
        .fetch_all(&mut conn)
        .await?;

    println!("matching rows = {rows:?}");

    Ok(())
}

Observed output:

SELECT regexp('23', 123) => None
SELECT 123 REGEXP '23' => None
SELECT regexp('12\\.5', 12.5) => None
SELECT regexp('1', TRUE) => None
SELECT regexp('23', CAST(123 AS TEXT)) => Some(1)
matching rows = []

SQLx version

main branch

Enabled SQLx features

default features

Database server and version

SQLite

Operating system

MacOS

Rust version

1.94.0 (4a4ef493e 2026-03-02)