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 OR
s:
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.@
for each row in the SELECT
.@
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.
Last modified: April 27, 2023