Utilizing carray (or it's rust equiv rarray), generated code causes a runtime error of too many SQL variables for .eq_any with slices > 999 elements

[drahnr]

Setup

Versions

• Rust: rustc 1.87.0 (17067e9ac 2025-05-09)
• Diesel: 2.2.10
• Database: sqlite
• Operating System fedora 42

Feature Flags

• diesel:

Problem Description

I am trying to find the latest note

     let q_pre = SelectDsl::select(schema::notes::table, schema::notes::block_num)
        .filter(
            schema::notes::tag
                .eq_any(&desired_note_tags[..10])
                .or(schema::notes::sender.eq_any(&desired_senders[..10])),
        )
        .filter(schema::notes::block_num.gt(start_block_num))
        .order_by(schema::notes::block_num.asc())
        .limit(1);

where desired_senders and desired_note_tags can be large lists of 50k entries, I'd have anticipated the following code being generated:

        SELECT
            block_num
        FROM
            notes
        WHERE
            (tag IN rarray(?1) OR sender IN rarray(?2)) AND
            block_num > ?3
        ORDER BY
            block_num ASC

but instead I get

 "SELECT `notes`.`block_num` FROM `notes` WHERE (((`notes`.`tag` IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) OR (`notes`.`sender` IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?))) AND (`notes`.`block_num` > ?)) ORDER BY `notes`.`block_num` ASC LIMIT ?",

which differs in the number of SQL variables. Now this works up until 999 (a compiletime fixed value for SQLite) but then it breaks.

The key here is RARRAY which is an SQL extension.

What are you trying to accomplish?

Filter for the latest entry matching a few conditions.

What is the expected output?

Functional queries for large array memebership filter queries

What is the actual output?

An error, too many SQL variables

Are you seeing any additional errors?

No

Steps to reproduce

Since this is at the boundary of feature/bug, I'd like to discuss first if you have the capacity to address it.

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

• This issue can be reproduced on Rust's stable channel. (Your issue will be
  closed if this is not the case)
• This issue can be reproduced without requiring a third party crate

[drahnr]

Getting this to work in / implement a replacement the carray/rarray outside of diesel-rs makes me face two challenges:

1. there is no exposure of a load_module on a SqliteConnection - it'd require to expose https://docs.diesel.rs/2.2.x/libsqlite3_sys/fn.sqlite3_create_module_v2.html in some form
2. representing it properly as a valid filter fragment

[weiznich]

Thanks for opening this feature request. I converted this to a discussion as that's a feature request and not a bug report and it also requires non-standard extensions to SQLite.

That brings me to my next point: Given that this is not a standard extension in SQLite we cannot use this in core diesel itself as we cannot assume that everyone enables and uses that particular extension.
What I'm open to would be to work on allowing to implement the necessary extension functionality outside of diesel. I'm happy to help with suggestions how to fix potential shortcomings if someone collects a list.

---

there is no exposure of a load_module on a SqliteConnection - it'd require to expose https://docs.diesel.rs/2.2.x/libsqlite3_sys/fn.sqlite3_create_module_v2.html in some form

This is certainly something we want to support. There was #2180 which tried to add this but got stuck due to we were not able to figure out a good solution to check if that functionality existed in the linked sqlite version at that point. It might be that this is now easily possible.

representing it properly as a valid filter fragment

I'm not sure what you miss here, maybe you could provide an example?

[drahnr]

This is certainly something we want to support. There was #2180 which tried to add this but got stuck due to we were not able to figure out a good solution to check if that functionality existed in the linked sqlite version at that point. It might be that this is now easily possible.

Cool! Priorities right now shifted on my end and moving away from c/rarrays is acceptable. Feel free to close the discussion.