Sample Header Ad - 728x90

MariaDB using 1000+ parameters with EXPLAIN

1 vote
0 answers
487 views
Can you confirm if this is a bug... I cannot work out if it's MariaDB (10.3.34 or 10.7.3), PHP (mysqlnd), or a mistake I've made:
query('SET in_predicate_conversion_threshold=2000;');

  $count = 1000;

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

  $statement = $db->prepare('EXPLAIN SELECT id FROM my_table WHERE 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);

?>
This hangs when it gets to $statement->execute(), and does not show any error message. If you remove EXPLAIN from the SQL (to make a normal SELECT query), the $statement->get_result() fails (returns false or exception, depending on mysqli_report), and you get the error message "Commands out of sync; you can't run this command now". --- The 1,000 parameter limit within IN() seems to be related to in_predicate_conversion_threshold; and you can have multiple IN() operators with 999 parameters each.
Asked by Craig Francis (135 rep)
Mar 14, 2022, 03:18 PM
Last activity: Mar 14, 2022, 05:48 PM