Doctrine 1
  1. Doctrine 1
  2. DC-49

Wrong query parameter order when using limit() on PostgreSQL

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.1.4
    • Fix Version/s: None
    • Component/s: Query
    • Labels:
      None
    • Environment:
      latest doctrine 1.1 revision, linux x64, PostgeSQL 8.4, php 5.2.10

      Description

      Trying to add limit() to complex query with many where's and some joins. In pgsql it causes nested limit subquery and in some cases parameter order (when limit subquery exists params array just merges with itself, i. e. (1,2) becomes (1,2,1,2)) makes result query wrong, i. e. params array order doesn't correspond placeholders order in query.

      With named query parameters all ok.

      Testcase attached.

      1. Doctrine_Query_Abstract.diff
        0.6 kB
        Daniel Bojdo
      2. PostgresParamsBugTestCase.php
        4 kB
        Nicholas Kasyanov

        Activity

        Nicholas Kasyanov created issue -
        Nicholas Kasyanov made changes -
        Field Original Value New Value
        Priority Critical [ 2 ] Major [ 3 ]
        Hide
        Jonathan H. Wage added a comment -

        here is the working test case:

        class Doctrine_Ticket_DC49_TestCase extends Doctrine_UnitTestCase 
        {
                public static $query;
                public static $params;
        
                public function init()
                {
                        $this->dbh = new Doctrine_Adapter_Mock('pgsql');
                        $this->conn = Doctrine_Manager::getInstance()->openConnection($this->dbh);
                        $this->conn->addListener(new PPB_QueryListener());
                }
                
                public function prepareTables()
                {
                        $this->tables = array('PPB_Section', 'PPB_Post', 'PPB_PostVote');
                        
                        parent::prepareTables();
                }
                
                public function testBug()
                {
                        $q = Doctrine_Query::create()->from('PPB_Post p')
                                                     ->where('p.section_id = ?', 1)
                                                     ->addWhere('p.id = ?', 15)
                                                     ->leftJoin('p.Votes v WITH v.user_id = ?', 10)
                                                     ->limit(10);
                                                     
                        $q->execute();
                        
                        $this->assertEqual(self::$query, "SELECT p.id AS p__id, p.section_id AS p__section_id, p2.id AS p2__id, p2.post_id AS p2__post_id, p2.user_id AS p2__user_id FROM p_p_b__post p LEFT JOIN p_p_b__post_vote p2 ON p.id = p2.post_id AND (p2.user_id = ?) WHERE p.id IN (SELECT doctrine_subquery_alias.id FROM (SELECT DISTINCT p3.id FROM p_p_b__post p3 LEFT JOIN p_p_b__post_vote p4 ON p3.id = p4.post_id AND (p4.user_id = ?) WHERE p3.section_id = ? AND p3.id = ? LIMIT 10) AS doctrine_subquery_alias) AND (p.section_id = ? AND p.id = ?)");
                        
                        $this->assertEqual(implode(",", self::$params), '10,10,1,15,1,15'); // correct order of params
                }
        }
        
        class PPB_Post extends Doctrine_Record
        {
                public function setTableDefinition()
                {
                        $this->hasColumn('section_id', 'integer');
                }
                
                public function setUp()
                {
                        $this->hasOne('PPB_Section', array('local' => 'section_id',
                                                           'foreign' => 'id'));
                                                           
                        $this->hasMany('PPB_PostVote as Votes', array('local' => 'id',
                                                                      'foreign' => 'post_id'));
                }
        }
        
        class PPB_Section extends Doctrine_Record
        {
                public function setTableDefinition()
                {
                }
        }
        
        class PPB_PostVote extends Doctrine_Record
        {
                public function setTableDefinition()
                {
                        $this->hasColumn('post_id', 'integer');
                        $this->hasColumn('user_id', 'integer');
                }
        }
        
        class PPB_QueryListener extends Doctrine_EventListener
        {
                public function postStmtExecute(Doctrine_Event $event)
                {
                        //var_dump($event->getQuery());
                        //var_dump($event->getParams());
                        
                        Doctrine_Ticket_DC49_TestCase::$query = $event->getQuery();
                        Doctrine_Ticket_DC49_TestCase::$params = $event->getParams();
                }
        }
        
        Show
        Jonathan H. Wage added a comment - here is the working test case: class Doctrine_Ticket_DC49_TestCase extends Doctrine_UnitTestCase { public static $query; public static $params; public function init() { $ this ->dbh = new Doctrine_Adapter_Mock('pgsql'); $ this ->conn = Doctrine_Manager::getInstance()->openConnection($ this ->dbh); $ this ->conn->addListener( new PPB_QueryListener()); } public function prepareTables() { $ this ->tables = array('PPB_Section', 'PPB_Post', 'PPB_PostVote'); parent::prepareTables(); } public function testBug() { $q = Doctrine_Query::create()->from('PPB_Post p') ->where('p.section_id = ?', 1) ->addWhere('p.id = ?', 15) ->leftJoin('p.Votes v WITH v.user_id = ?', 10) ->limit(10); $q->execute(); $ this ->assertEqual(self::$query, "SELECT p.id AS p__id, p.section_id AS p__section_id, p2.id AS p2__id, p2.post_id AS p2__post_id, p2.user_id AS p2__user_id FROM p_p_b__post p LEFT JOIN p_p_b__post_vote p2 ON p.id = p2.post_id AND (p2.user_id = ?) WHERE p.id IN (SELECT doctrine_subquery_alias.id FROM (SELECT DISTINCT p3.id FROM p_p_b__post p3 LEFT JOIN p_p_b__post_vote p4 ON p3.id = p4.post_id AND (p4.user_id = ?) WHERE p3.section_id = ? AND p3.id = ? LIMIT 10) AS doctrine_subquery_alias) AND (p.section_id = ? AND p.id = ?)" ); $ this ->assertEqual(implode( "," , self::$params), '10,10,1,15,1,15'); // correct order of params } } class PPB_Post extends Doctrine_Record { public function setTableDefinition() { $ this ->hasColumn('section_id', 'integer'); } public function setUp() { $ this ->hasOne('PPB_Section', array('local' => 'section_id', 'foreign' => 'id')); $ this ->hasMany('PPB_PostVote as Votes', array('local' => 'id', 'foreign' => 'post_id')); } } class PPB_Section extends Doctrine_Record { public function setTableDefinition() { } } class PPB_PostVote extends Doctrine_Record { public function setTableDefinition() { $ this ->hasColumn('post_id', 'integer'); $ this ->hasColumn('user_id', 'integer'); } } class PPB_QueryListener extends Doctrine_EventListener { public function postStmtExecute(Doctrine_Event $event) { //var_dump($event->getQuery()); //var_dump($event->getParams()); Doctrine_Ticket_DC49_TestCase::$query = $event->getQuery(); Doctrine_Ticket_DC49_TestCase::$params = $event->getParams(); } }
        Roman S. Borschel made changes -
        Assignee Roman S. Borschel [ romanb ] Guilherme Blanco [ guilhermeblanco ]
        Hide
        Daniel Bojdo added a comment -

        I fixed this bug. Patch in attachment. I hope it's clear enough.

        Show
        Daniel Bojdo added a comment - I fixed this bug. Patch in attachment. I hope it's clear enough.
        Daniel Bojdo made changes -
        Attachment Doctrine_Query_Abstract.diff [ 10709 ]

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DC-49, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Nicholas Kasyanov
          • Votes:
            2 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: