Doctrine 1
  1. Doctrine 1
  2. DC-645

Query with a leftJoin() + where(NOT IN) + limit() generate wrong SQL alias in the NOT IN part

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 1.2.2
    • Fix Version/s: None
    • Component/s: Query
    • Labels:
      None
    • Environment:

      Description

      I have a simple case with 3 Classes. Contact, Phone and Email. A Contact can many many phones, but only one email.

      When doing this simple query:
      $query = Doctrine_Query::create()->from('Contact c');
      $query->leftJoin('c.Phones p ON c.id = p.contact_id');
      $query->where('c.id NOT IN (SELECT Email.contact_id FROM Email)');
      $query->execute();
      Every thing went fine.

      But when adding a limit() condition like:
      $query->limit(20);
      $query->execute();

      The SQL generated query is not valid. There is a problem with the alias used in the NOT IN subquery. This query is generated like this:
      WHERE c2.id NOT IN (SELECT e2.contact_id AS e2__contact_id FROM email e)
      There is a mix between alias e2 and e

      I have been trying to debug, but I didn't understand what was going wrong. The problem seems to happend in the class Doctrine_Query between line 1486 and 1554, but this part is obscur to me.

      I attach to this ticket a valid TestCase

      Thanks for support

        Activity

        Hide
        David Jeanmonod added a comment -

        TEXT VERSION OF THE TEST CASE

        <?php

        require_once('doctrine/lib/Doctrine.php');
        spl_autoload_register(array('Doctrine', 'autoload'));
        $manager = Doctrine_Manager::getInstance();
        $manager->setAttribute(Doctrine::ATTR_EXPORT, Doctrine::EXPORT_ALL);
        $conn = Doctrine_Manager::connection('mysql://root:@localhost/test_doctrine');
        echo "Connection is set up\n";

        class Contact extends Doctrine_Record {
        public function setTableDefinition()

        { $this->setTableName('contact'); $this->hasColumn('name', 'string', 300, array('type' => 'string', 'length' => 300)); }

        public function setUp()

        { $this->hasMany('Phone as Phones', array('local' => 'id', 'foreign' => 'contact_id')); $this->hasOne('Email as Email', array('local' => 'id', 'foreign' => 'contact_id')); }

        }
        class Phone extends Doctrine_Record {
        public function setTableDefinition()

        { $this->setTableName('phone'); $this->hasColumn('contact_id', 'integer', null, array('type' => 'integer')); }

        public function setUp()

        { $this->hasOne('Contact', array('local' => 'contact_id', 'foreign' => 'id', 'onDelete' => 'CASCADE')); }

        }
        class Email extends Doctrine_Record {
        public function setTableDefinition()

        { $this->setTableName('email'); $this->hasColumn('contact_id', 'integer', null, array('type' => 'integer')); }

        public function setUp()

        { $this->hasOne('Contact', array('local' => 'contact_id', 'foreign' => 'id')); }

        }
        echo "Classes Contact, Phone and Email are defines\n";

        try

        {Doctrine::dropDatabases();}

        catch(Exception $e){} // Drop if exist
        Doctrine::createDatabases();
        Doctrine::createTablesFromArray(array('Contact', 'Phone', 'Email'));
        echo "Databases tables are create\n";

        $query = Doctrine_Query::create()->from('Contact c');
        $query->leftJoin('c.Phones p ON c.id = p.contact_id');
        $query->where('c.id NOT IN (SELECT Email.contact_id FROM Email)');
        $query->limit(20);
        try

        { $query->execute(); echo "TEST: Doctrine LEFTJOIN + NOT IN + LIMIT is OK\n"; }

        catch (Exception $e)

        { echo "TEST: Doctrine LEFTJOIN + NOTIN + LIMIT is NOT working\n Alias in the NOT IN part are wrong, we get e2 and e\n\nDetail of the error:\n ", $e->getMessage(), "\n"; }
        Show
        David Jeanmonod added a comment - TEXT VERSION OF THE TEST CASE <?php require_once('doctrine/lib/Doctrine.php'); spl_autoload_register(array('Doctrine', 'autoload')); $manager = Doctrine_Manager::getInstance(); $manager->setAttribute(Doctrine::ATTR_EXPORT, Doctrine::EXPORT_ALL); $conn = Doctrine_Manager::connection('mysql://root:@localhost/test_doctrine'); echo "Connection is set up\n"; class Contact extends Doctrine_Record { public function setTableDefinition() { $this->setTableName('contact'); $this->hasColumn('name', 'string', 300, array('type' => 'string', 'length' => 300)); } public function setUp() { $this->hasMany('Phone as Phones', array('local' => 'id', 'foreign' => 'contact_id')); $this->hasOne('Email as Email', array('local' => 'id', 'foreign' => 'contact_id')); } } class Phone extends Doctrine_Record { public function setTableDefinition() { $this->setTableName('phone'); $this->hasColumn('contact_id', 'integer', null, array('type' => 'integer')); } public function setUp() { $this->hasOne('Contact', array('local' => 'contact_id', 'foreign' => 'id', 'onDelete' => 'CASCADE')); } } class Email extends Doctrine_Record { public function setTableDefinition() { $this->setTableName('email'); $this->hasColumn('contact_id', 'integer', null, array('type' => 'integer')); } public function setUp() { $this->hasOne('Contact', array('local' => 'contact_id', 'foreign' => 'id')); } } echo "Classes Contact, Phone and Email are defines\n"; try {Doctrine::dropDatabases();} catch(Exception $e){} // Drop if exist Doctrine::createDatabases(); Doctrine::createTablesFromArray(array('Contact', 'Phone', 'Email')); echo "Databases tables are create\n"; $query = Doctrine_Query::create()->from('Contact c'); $query->leftJoin('c.Phones p ON c.id = p.contact_id'); $query->where('c.id NOT IN (SELECT Email.contact_id FROM Email)'); $query->limit(20); try { $query->execute(); echo "TEST: Doctrine LEFTJOIN + NOT IN + LIMIT is OK\n"; } catch (Exception $e) { echo "TEST: Doctrine LEFTJOIN + NOTIN + LIMIT is NOT working\n Alias in the NOT IN part are wrong, we get e2 and e\n\nDetail of the error:\n ", $e->getMessage(), "\n"; }
        Hide
        will ferrer added a comment -

        Hi David

        I had a problem with subqueries in the which I worked around by including real sql in the subquery with a prefix of SQL:.

        My bug occurred trying to run this code:

        $q = Doctrine_Query::create(); 
        $q->from('Customer Customer'); 
        $q->addWhere(' Customer.id in (SELECT Customer.id as customer_id FROM Customer Customer)'); 
        $q->addSelect('Customer.id'); 
        $q->addSelect('Customer.id as customer_id');
        $q->limit(20);
        

        However this code works fine for me now (though make sure you have the latest svn build of doctrine because there were some patches that helped this work):

        $q = Doctrine_Query::create(); 
        $q->from('Customer Customer'); 
        $q->addWhere(' Customer.id in (SQL:SELECT p.id AS p__0 FROM product_customers p)'); 
        $q->addSelect('Customer.id'); 
        $q->addSelect('Customer.id as customer_id');
        

        Notice the use of SQL: in the subquery.

        Here is the bug:

        http://www.doctrine-project.org/jira/browse/DC-692

        Also worth noting that I am getting that sql by building another query and then running the getSqlQuery method to return the sql I am then using in the other query.

        Hope that helps.

        Will Ferrer

        Show
        will ferrer added a comment - Hi David I had a problem with subqueries in the which I worked around by including real sql in the subquery with a prefix of SQL:. My bug occurred trying to run this code: $q = Doctrine_Query::create(); $q->from('Customer Customer'); $q->addWhere(' Customer.id in (SELECT Customer.id as customer_id FROM Customer Customer)'); $q->addSelect('Customer.id'); $q->addSelect('Customer.id as customer_id'); $q->limit(20); However this code works fine for me now (though make sure you have the latest svn build of doctrine because there were some patches that helped this work): $q = Doctrine_Query::create(); $q->from('Customer Customer'); $q->addWhere(' Customer.id in (SQL:SELECT p.id AS p__0 FROM product_customers p)'); $q->addSelect('Customer.id'); $q->addSelect('Customer.id as customer_id'); Notice the use of SQL: in the subquery. Here is the bug: http://www.doctrine-project.org/jira/browse/DC-692 Also worth noting that I am getting that sql by building another query and then running the getSqlQuery method to return the sql I am then using in the other query. Hope that helps. Will Ferrer

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            David Jeanmonod
          • Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: