SqliteException: variable number must be between ?1 and ?999, SQL logic error

[bakua]

Hello,

select queries in SQLite are limited to have 999 value subtitutions with ?. So if I know I'll be doing something line select * from table whenere id IN :ids; then I have to make sure to batch such query by 999 ids. As this is a boilerplate, is that something that could be generated by Moor in advance?

[simolus3]

I'm not sure what's the best way to approach this. Generating multiple selects with subsets of the list sounds fragile when we have multiple list parameters.

Maybe we can implicitly do something like this:

CREATE TEMP TABLE tmp_list_arg_01 (arg INTEGER /* or the matching type */);
INSERT INTO tmp_list_01 (?); -- with ? = ids[0]
...
select * from table where id in tmp_list_arg_01
DROP TABLE tmp_list_arg_01

Running the insert a lot shouldn't be a problem as it writes to memory and we can easily cache the prepared statement.

[bakua]

Agree on fragility with multiple list statements, Interesting solution you have there. I wonder what the performance would be. Could you try?