Doctrine 1

Query Cache causes exception when using array parameter and IN in a where clause

Details

  • Type: Bug Bug
  • Status: Open Open
  • Priority: Major Major
  • Resolution: Unresolved
  • Affects Version/s: 1.2.1, 1.2.2
  • Fix Version/s: None
  • Component/s: Query
  • Labels:
    None
  • Environment:
    PHP 5.2.11 Linux, Mysql 5.0.x

Description

Following the documentation, I'm using an IN condition in a where clause on a Doctrine_Query with Query cache enabled (APC). Syntax:

$values = array(1, 2, 3, 4);

$q = Doctrine_Query::create()>from('SomeModel s')>where('s.column IN ?', array($values));

$results = $q->execute();

The first time this query is run, all is fine, and any subsequent runs will be fine as well, UNLESS the values array changes length, like so:

$values = array(1, 2, 3, 4, 5);

$q = Doctrine_Query::create()>from('SomeModel s')>where('s.column IN ?', array($values));

$results = $q->execute();

With the query cache enabled, this will throw a PDO "Invalid Parameter Count" exception, because it appears that Doctrine uses the cached query which only has 4 placeholders, but the passed parameters now has a count of 5.

A change in the length of the parameters array in this situation should cause Doctrine to insert a new entry into the query cache for this query.

Let me know if you need more information regarding this, if I find time I can try to put together a Test case.

Thanks!

  1. File
    DC600TestCase.php
    30/Mar/10 3:19 PM
    7 kB
    Jay Klehr
  2. Text File
    quickfix.txt
    21/Jul/10 8:39 AM
    0.4 kB
    Till Seifert

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Jay Klehr added a comment -

Failing test case exhibiting this bug.

Show
Jay Klehr added a comment - Failing test case exhibiting this bug.
Hide
Permalink
Jay Klehr added a comment -

I've attached a TestCase that I put together that shows this bug.

Turns out that when using the sqlite adapter, the exception isn't thrown, so I had to look at the profiler in order to show that the query fetched from the cache has the wrong parameter count.

I also tested this with the "whereIn()" method instead of just "where()". whereIn works correctly (but isn't as flexible as where, so I don't believe it's an acceptable replacement in all situations).

Show
Jay Klehr added a comment - I've attached a TestCase that I put together that shows this bug. Turns out that when using the sqlite adapter, the exception isn't thrown, so I had to look at the profiler in order to show that the query fetched from the cache has the wrong parameter count. I also tested this with the "whereIn()" method instead of just "where()". whereIn works correctly (but isn't as flexible as where, so I don't believe it's an acceptable replacement in all situations).
Hide
Permalink
Jay Klehr added a comment -

fixing my quoted blocks

Show
Jay Klehr added a comment - fixing my quoted blocks
Hide
Permalink
Jay Klehr added a comment -

Updated test case. I discovered that sqlite DOES throw an exception in the array's length grows in the second query so added another test to my test case to exhibit this.

Show
Jay Klehr added a comment - Updated test case. I discovered that sqlite DOES throw an exception in the array's length grows in the second query so added another test to my test case to exhibit this.
Hide
Permalink
Till Seifert added a comment - - edited

Hi, this bug just hit me hard, and made a quick fix:

in the Query/Abstract.php

the function calculateQueryCacheHash was augmented with the param-count:

public function calculateQueryCacheHash($params = array())

{ $dql = $this->getDql(); /// quickfox by Till Seifert $hash = md5($dql . '|' . count($this->getFlattenedParams($params)) . '|' . var_export($this->_pendingJoinConditions, true) . 'DOCTRINE_QUERY_CACHE_SALT'); return $hash; }

and in protected function _execute($params) the params are passed:

Line:927
++ $hash = $this->calculateQueryCacheHash($dqlParams);

cann anyone comment on if this could be horribly wrong? for know this fixes this bug for me.

[EDIT: posted code as attachment, for better readability)

Show
Till Seifert added a comment - - edited Hi, this bug just hit me hard, and made a quick fix: in the Query/Abstract.php the function calculateQueryCacheHash was augmented with the param-count: public function calculateQueryCacheHash($params = array()) { $dql = $this->getDql(); /// quickfox by Till Seifert $hash = md5($dql . '|' . count($this->getFlattenedParams($params)) . '|' . var_export($this->_pendingJoinConditions, true) . 'DOCTRINE_QUERY_CACHE_SALT'); return $hash; } and in protected function _execute($params) the params are passed: Line:927 ++ $hash = $this->calculateQueryCacheHash($dqlParams); cann anyone comment on if this could be horribly wrong? for know this fixes this bug for me. [EDIT: posted code as attachment, for better readability)
Hide
Permalink
Till Seifert added a comment - - edited

proposed fix. not a patch, sorry.

it's also faulty: it runs getFlattenedParams twice, and it fails when you have more than 1 "x IN Array" condition. If the global number or queryparams stays the same, no new cache-entry is created, so you wold have somehow incorporate the individual param-parts-sizes in the cache-hash, not just the number of params.

Show
Till Seifert added a comment - - edited proposed fix. not a patch, sorry. it's also faulty: it runs getFlattenedParams twice, and it fails when you have more than 1 "x IN Array" condition. If the global number or queryparams stays the same, no new cache-entry is created, so you wold have somehow incorporate the individual param-parts-sizes in the cache-hash, not just the number of params.

People

  • Assignee:
    Guilherme Blanco
    Reporter:
    Jay Klehr
Vote (1)
Watch (1)

Dates

  • Created:
    Updated: