SQLite Forum

In operator max list size
Login

In operator max list size

(1) By anonymous on 2020-10-21 08:35:26 [link] [source]

Hi there,

I'm trying to find information about the maximum size for a list in an in-operator. I.e:

select ... where v in ('value1', 'value2'... 'valueN')

What is the maximum N allowed.

I couldn't find this information here in the forums or in the limits page.

Thanks in advance.

(2) By Gunter Hick (gunter_hick) on 2020-10-21 11:08:10 in reply to 1 [source]

The maximum will be dictated by the amount of memory available for the input string, the parser memory and the generated bytecode.

You may be much better off storing the values in a separate table and joining to it.

(3) By Richard Hipp (drh) on 2020-10-21 11:08:26 in reply to 1 [link] [source]

There is no explicit limit on the number of terms in the RHS of an IN operator. The size will end up being limited by things like:

  • SQLITE_LIMIT_SQL_LENGTH
  • SQLITE_LIMIT_VDBE_OP

If you have a large number of values in your application program that you want to appear on the RHS of an IN operator, you might be better served to load those values using the carray() table-valued function.

(4) By anonymous on 2020-10-21 11:30:13 in reply to 3 [link] [source]

Excellent,

Thank you both for the info.

This is mainly for documentation purposes. Some users (and ill-behaved applications) sometimes like to generate very long IN clauses. We limit the list size by config in our software for other databases, so we will do the same here.

Also thanks for the tip about carray().

Cheers!