Doctrine query caching causing “Invalid parameter number” error for queries using “IN ?”

October 5th, 2010 | Tags: , , , , , ,

When using Doctrine query caching, if your DQL contains “IN ?” the query cache may return a query that will result in an “Invalid parameter number: number of bound variables does not match number of tokens” exception.

The reason for this is because the index to the query cache is a hash of the DQL before the “IN ?” is expanded to something like “IN (?, ?, ?)” (the number of ? will vary depending on the number of elements in the array passed as the parameter for that spot), but the actual query being stored in the cache is the expanded form.

It may be easier to understand with an example. Let’s use a simple schema and query to illustrate the situation:

Name:
  columns:
    first:  { type: string }
    middle: { type: string }
    last:   { type: string }
$lastNames = array('Smith', 'Jones');
$query = Doctrine::getTable('Name')
  ->createQuery()
  ->where('last IN ?', array($lastNames));

The problem comes if you have query caching turned on and you try to execute that query more than once.  Any time you execute the query with the $lastNames array having a different number of elements than when it was first run (and cached) you will get the “Invalid parameter number” exception.

For example, this code will work without problems:

$lastNames1 = array('Smith', 'Jones');
$query1 = Doctrine::getTable('Name')
  ->createQuery()
  ->where('last IN ?', array($lastNames1));
$query1->execute();

$lastNames2 = array('Johnson', 'Martin');
$query2 = Doctrine::getTable('Name')
  ->createQuery()
  ->where('last IN ?', array($lastNames2));
$query2->execute();

But this code will throw the exception on the last execute() even though the only change is the addition of one extra element to the $lastNames2 array:

$lastNames1 = array('Smith', 'Jones');
$query1 = Doctrine::getTable('Name')
  ->createQuery()
  ->where('last IN ?', array($lastNames1));
$query1->execute();

$lastNames2 = array('Johnson', 'Martin', 'Williams');
$query2 = Doctrine::getTable('Name')
  ->createQuery()
  ->where('last IN ?', array($lastNames2));
$query2->execute();

The error message will look something like:

Fatal error:  Uncaught exception 'Doctrine_Connection_Mysql_Exception' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens' in /jlc/jlc-builds/3_28/site/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection.php:1082
Stack trace:
  #0 /jlc/jlc-builds/3_28/site/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection/Statement.php(269): Doctrine_Connection->rethrowException(Object(PDOException), Object(Doctrine_Connection_Statement))
  #1 /jlc/jlc-builds/3_28/site/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection.php(1006): Doctrine_Connection_Statement->execute(Array)
  #2 /jlc/jlc-builds/3_28/site/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/Abstract.php(976): Doctrine_Connection->execute('SELECT c.id AS ...', Array)
  #3 /jlc/jlc-builds/3_28/site/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Query/A in /jlc/jlc-builds/3_28/site/lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection.php on line 1082

You can look at your log file to see the SQL Doctrine tried to execute:

Oct 06 01:59:35 symfony [info] {Doctrine_Connection_Statement} execute : SELECT n.id AS n__id, n.first AS n__first, n.middle AS n__middle, n.last AS n__last FROM name n WHERE (n.last IN (?, ?)) - (Johnson, Martin, Williams)

Note that the number of ? differs from the number of actual parameters, which is what causes the exception.

The reason this happens is because the index used to lookup the query in the cache is based on the DQL rather than the SQL of the query. You can see the DQL by running:

print_r($query->getDql());

For our example, the DQL is:

SELECT id, first, middle, last FROM Name WHERE last IN ?

But, as we saw earlier, the value stored in the cache is:

SELECT n.id AS n__id, n.first AS n__first, n.middle AS n__middle, n.last AS n__last FROM name n WHERE (n.last IN (?, ?))

So, you can see that the number of ? is hard-coded into the query cache. There are two ways to fix this problem. You can prevent the query from being cached:

$query->useQueryCache(false);

Or, modify the query so that the number of ? is hard-coded into the DQL:

$lastNames = array('Smith', 'Jones');
$query = Doctrine::getTable('Name')
  ->createQuery()
  ->where('last IN (?' . str_repeat(', ?', count($lastNames) - 1) . ')', $lastNames);

This information was gathered using Doctrine 1.2.1 and Symfony 1.4.1. I have not checked if this bug has been fixed in newer versions of Doctrine.

  1. October 10th, 2010 at 14:05
    Reply | Quote | #1

    I had the same problem, but i using just using ->whereIn(‘last’, $array); And it’s ok :)
    But for my problem, that was not a problem of caching.

TOP