MariaDB limits to 1000 parameters with `IN()`

[Craig Francis]

With the recent release of MariaDB `1:10.3.34-0ubuntu0.20.04.1`, or maybe php7.4-mysql `7.4.3-4ubuntu2.9`, something changed so the number of parameters used with `IN()` is now being limited to 1000.

I believe the number of parameters used to only be limited by the `max_allowed_packet` value, where I was able to specify 3,000+ integers. This is not noted in the MariaDB documentation, but is in MySQL:

https://mariadb.com/kb/en/in/
https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html#operator_in

The `$statement->errno` returns the integer 2014, and `$statement->error` does include "Commands out of sync; you can't run this command now", which matches later versions of MariaDB, so I think part of this change has been back-ported somehow (cannot see in either patch, but I can't understand all that has changed).

For a test script, using PHP (so I can easily use parameters)... I can do 2 x `IN()` with 999 parameters each, but as soon as there are 1000 parameters in either or both, it fails.

<?php

  $db = new mysqli('hostname', 'username', 'password', 'database');

  $count = 999;
  $count = 1000;

  $params = implode(',', array_fill(0, $count, '?'));
  $types = str_repeat('i', ($count * 2));
  $values = array_fill(0, ($count * 2), 3);

  $statement = $db->prepare('SELECT id FROM my_table WHERE id IN (' . $params . ') AND id IN (' . $params . ')');
  $statement->bind_param($types, ...$values);
  $statement->execute();

  $result = $statement->get_result();

  var_dump($db->error);
  var_dump($statement->error);
  var_dump($statement->errno);
  var_dump($result);

  while ($row = mysqli_fetch_assoc($result)) {
    print_r($row);
  }

?>

Running this without parameters is fine, same with the `mysql` cli:

SELECT id FROM my_table WHERE id IN (
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1
);

[Launchpad Janitor (janitor)]

Status changed to 'Confirmed' because the bug affects multiple users.

[Craig Francis (craig.francis)]

I suspect this might be related to `in_predicate_conversion_threshold`?

While I have fixed this elsewhere by using

  foreach (array_chunk($ids, 900) as $chunk_ids) {
  }

I've also been able to use the following as a temporary work around:

  SET in_predicate_conversion_threshold=2000;

I assume this config variable is used to protect against something, so I'm hesitant to apply it globally.

[Walter (wdoekes)]

That is a likely culprit, as there are changes there after 10.3.32:

  $ git tag --contains a65d01a4cf04c744f9355bec186430540ce6ba08 | grep 10.3 -F
  mariadb-10.3.34
  mariadb-10.3.33

commit a65d01a4cf04c744f9355bec186430540ce6ba08
Author: Dmitry Shulga <email address hidden>
Date: Wed Oct 20 19:24:31 2021 +0700

    MDEV-23182: Server crashes in Item::fix_fields_if_needed / table_value_constr::prepare upon 2nd execution of PS

    Repeating execution of a query containing the clause IN with string literals
    in environment where the server variable in_predicate_conversion_threshold
    is set results in server abnormal termination in case the query is run
    as a Prepared Statement and conversion of charsets for string values in the
    query are required.

    The reason for server abnormal termination is that instances of the class
    Item_string created on transforming the IN clause into subquery were created
    on runtime memory root that is deallocated on finishing execution of Prepared
    statement. On the other hand, references to Items placed on deallocated memory
    root still exist in objects of the class table_value_constr. Subsequent running
    of the same prepared statement leads to dereferencing of pointers to already
    deallocated memory that could lead to undefined behaviour.

    To fix the issue the values being pushed into a values list for TVC are created
    by cloning their original items. This way the cloned items are allocate on
    the PS memroot and as consequences no dangling pointer does more exist.

[Walter (wdoekes)]

https://jira.mariadb.org/browse/MDEV-27937

[Daniel Black (daniel-black)]

Per above JIRA issue this has been fixed in upstream release 10.3.35

[Timo Wege (wetimo)]

Will there be an upgrade to version 10.3.35 or 10.3.36 for this?

[Otto Kekäläinen (otto)]

Next upgrade pending in LP#1996452.

[Otto Kekäläinen (otto)]

Fixed in 1:10.3.37-0ubuntu0.20.04.1

[Craig Francis (craig.francis)]

Thank you for fixing this Otto, I really appreciate it.