There is no limit on the size of an IN list directly, the limit is on the number of parameterized constants in the query. Most IN lists are just one parameterized constant.

You are most likely to hit this limit if you mix types in the IN clause, for example IN (1, “test"), here it will be treated as two parameters, but if you use IN clause that contains the same type of parameters it will be treated as one parameter.

For example, if you run:

SELECT * FROM t WHERE c1 IN (1, 2, 3, 4, 5);

and look in the table INFORMATION_SCHEMA.PLANCACHE you will notice that the values in the IN clause are treated as one single argument as shown below:

SELECT * FROM t WHERE c1 IN(@);

The number of @s is the number of parameterized constants. It's just one in this case.

You need about one million @s to hit this error.

For example consider this query with one million ORs:

SELECT * FROM t WHERE c1 = 1 or OR c1 = 2 OR c1 =3………..;

maps to:

SELECT * FROM t WHERE c1 = @ OR c1 = @ OR c1 =@…………;

A query with multiple subqueries, that contain the IN clause can also generate a large number of @s. SingleStore will create a new @ for each row in the SELECT. Depending on the number of @s generated eventually a query can reach the end of the parameter array capacity.

Solution:

The query should be refactored to reduce the number of parameterized constants in the query and subqueries.