ERROR 1956 ER_
PARAMETER_ CAPACITY_ EXCEEDED: Query cannot be completed because the parameter array capacity of 1048576 was exceeded.
There is no limit on the size of an
IN list directly, the limit is on the number of parameterized constants in the query.
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_ 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.
You need about one million
@s to hit this error.
For example consider this query with one million
SELECT * FROM t WHERE c1 = 1 or OR c1 = 2 OR c1 =3……….
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
@ for each row in the
@s generated eventually a query can reach the end of the parameter array capacity.
The query should be refactored to reduce the number of parameterized constants in the query and subqueries.
Last modified: April 27, 2023