Doctrine 1
  1. Doctrine 1
  2. DC-600

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

    Details

    • Type: Bug Bug
    • Status: 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. DC600TestCase.php
        7 kB
        Jay Klehr
      2. quickfix.txt
        0.4 kB
        Till Seifert

        Activity

        Hide
        Jay Klehr added a comment -

        Failing test case exhibiting this bug.

        Show
        Jay Klehr added a comment - Failing test case exhibiting this bug.
        Hide
        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
        Jay Klehr added a comment -

        fixing my quoted blocks

        Show
        Jay Klehr added a comment - fixing my quoted blocks
        Hide
        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
        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
        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
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: