Doctrine 1
  1. Doctrine 1
  2. DC-241

Count query with join o2m + limit + where throws an exception

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 1.2.0-BETA3
    • Fix Version/s: 1.2.0-RC1
    • Component/s: Record, Relations
    • Labels:
      None
    • Environment:
      1.2 svn

      Description

      class Poll extends Doctrine_Record
      {
      	public function setTableDefinition()
      	{
      		$this->setTableName('module_polls');
      		
      		$this->hasColumn('id_category', 'integer', null, array('notnull' => true));
      		$this->hasColumn('question', 'string', 256);
      	}
      	
      	public function setUp()
      	{
      		$this->hasMany('PollAnswer as Answers', array('local' => 'id', 'foreign' => 'id_poll', 'orderBy' => 'position'));
      	}
      }
      
      class PollAnswer extends Doctrine_Record
      {
      	public function setTableDefinition()
      	{
      		$this->setTableName('module_polls_answers');
      		
      		$this->hasColumn('id_poll', 'integer', null, array('notnull' => true));
      		$this->hasColumn('answer', 'string', 256);
      		$this->hasColumn('votes', 'integer', null, array('notnull' => true, 'default' => 0));
      		$this->hasColumn('position', 'integer');
      	}
      	
      	public function setUp()
      	{
      		$this->hasOne('Poll', array('local' => 'id_poll', 'foreign' => 'id', 'onDelete' => 'CASCADE'));
      	}
      }
      
      $query = Doctrine_Query::create()
      	->from('Poll p')
      	->leftJoin('p.Answers pa')
      	->where('id_category = ?', 1)
      	->limit(10);
      
      print_r($query->count());
      

      This fails with the following exception being thrown:

      SQLSTATE[42000]: Syntax error or access violation: 1064 Something is wrong in your syntax obok '? LIMIT 10' w linii 1. Failing Query: "SELECT DISTINCT `m3`.`id` FROM `module_polls` `m3` LEFT JOIN `module_polls_answers` `m4` ON `m3`.`id` = `m4`.`id_poll` WHERE `m3`.`id_category` = ? LIMIT 10"
      #0 (...)\library\Doctrine\Doctrine\Connection.php(1015): Doctrine_Connection->rethrowException(Object(PDOException), Object(Doctrine_Connection_Mysql), 'SELECT DISTINCT...')
      #1 (...)\library\Doctrine\Doctrine\Query.php(1246): Doctrine_Connection->execute('SELECT DISTINCT...', Array)
      #2 (...)\library\Doctrine\Doctrine\Query.php(1106): Doctrine_Query->buildSqlQuery()
      #3 (...)\library\Doctrine\Doctrine\Query\Abstract.php(1126): Doctrine_Query->getSqlQuery(Array)
      #4 (...)\library\Doctrine\Doctrine\Query\Abstract.php(1088): Doctrine_Query_Abstract->_getDqlCallbackComponents(Array)
      #5 (...)\library\Doctrine\Doctrine\Query.php(1126): Doctrine_Query_Abstract->_preQuery()
      #6 (...)\library\Doctrine\Doctrine\Query.php(1106): Doctrine_Query->buildSqlQuery()
      #7 (...)\library\Doctrine\Doctrine\Query.php(1995): Doctrine_Query->getSqlQuery()
      #8 (...)\library\Doctrine\Doctrine\Query.php(2091): Doctrine_Query->getCountSqlQuery()
      #9 (...)\test2.php(123): Doctrine_Query->count()
      #10

      {main}

      You can see the generated query which is wrong, moreover it is passed with an empty params array().
      Without either where condition or limit removed, it works. With a combo of both of them it fails.

      E.g. without where condition the following query is generated:
      SELECT COUNT AS `num_results` FROM (SELECT `m`.`id` FROM `module_polls` `m` LEFT JOIN `module_polls_answers` `m2` ON `m`.`id` = `m2`.`id_poll` GROUP BY `m`.`id`) `dctrn_count_query`

        Activity

        Hide
        Maciej Hołyszko added a comment - - edited

        This is related to recently closed, but not fixed due to unability to reproduce, DC-138 - seems like the same problem. In that ticket it was not clearly stated that ->count() method is used on the query. It fails when a 'where' condition is added to the query, no matter if it's simple =? condition or LIKE one.

        Show
        Maciej Hołyszko added a comment - - edited This is related to recently closed, but not fixed due to unability to reproduce, DC-138 - seems like the same problem. In that ticket it was not clearly stated that ->count() method is used on the query. It fails when a 'where' condition is added to the query, no matter if it's simple =? condition or LIKE one.
        Hide
        Jonathan H. Wage added a comment -

        I am not sure that these are the same issues. Can you test this patch though?

        Index: lib/Doctrine/Query.php
        ===================================================================
        --- lib/Doctrine/Query.php	(revision 6739)
        +++ lib/Doctrine/Query.php	(working copy)
        @@ -1334,15 +1334,6 @@
                     array_pop($this->_sqlParts['where']);
                 }
         
        -        if ($needsSubQuery) {
        -            // We need to double shift if > 2
        -            if (count($this->_sqlParts['where']) > 2) {
        -                array_shift($this->_sqlParts['where']);
        -            }
        -
        -            array_shift($this->_sqlParts['where']);
        -        }
        -
                 $this->_sql = $q;
         
                 $this->clear();
        

        I think this should fix the issue. I am not sure what that block of code is supposed to be doing, but I think it is there incorrectly.

        Show
        Jonathan H. Wage added a comment - I am not sure that these are the same issues. Can you test this patch though? Index: lib/Doctrine/Query.php =================================================================== --- lib/Doctrine/Query.php (revision 6739) +++ lib/Doctrine/Query.php (working copy) @@ -1334,15 +1334,6 @@ array_pop($ this ->_sqlParts['where']); } - if ($needsSubQuery) { - // We need to double shift if > 2 - if (count($ this ->_sqlParts['where']) > 2) { - array_shift($ this ->_sqlParts['where']); - } - - array_shift($ this ->_sqlParts['where']); - } - $ this ->_sql = $q; $ this ->clear(); I think this should fix the issue. I am not sure what that block of code is supposed to be doing, but I think it is there incorrectly.
        Hide
        Maciej Hołyszko added a comment -

        Unfortunately this patch does not fix the problem. The result is the same exception being thrown.
        I think that part of code is there for some purpose, are you sure that removing will not break anything else? It is steered by $needsSubQuery variable which is set to true or false in few conditions above it.

        Show
        Maciej Hołyszko added a comment - Unfortunately this patch does not fix the problem. The result is the same exception being thrown. I think that part of code is there for some purpose, are you sure that removing will not break anything else? It is steered by $needsSubQuery variable which is set to true or false in few conditions above it.
        Hide
        Jonathan H. Wage added a comment -

        No after talking with the team we realized that the code is not needed and should have been removed. It was there to "fix" a problem but at some point the real problem was fixed so this is no longer needed.

        Show
        Jonathan H. Wage added a comment - No after talking with the team we realized that the code is not needed and should have been removed. It was there to "fix" a problem but at some point the real problem was fixed so this is no longer needed.
        Hide
        Jonathan H. Wage added a comment -

        I committed some changes, can you test those? Also, can you get on IRC or something where we can chat so we can get this issue fixed today?

        Show
        Jonathan H. Wage added a comment - I committed some changes, can you test those? Also, can you get on IRC or something where we can chat so we can get this issue fixed today?

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Maciej Hołyszko
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: