[#DC-1010] When putting a subquery in the where clause which includes a join and a limit the limit subquery algorithm mistakenly modifies the subquery

[DC-1010] When putting a subquery in the where clause which includes a join and a limit the limit subquery algorithm mistakenly modifies the subquery Created: 21/Jun/11 Updated: 21/Jun/11
Status:	Open
Project:	Doctrine 1
Component/s:	Query
Affects Version/s:	1.2.2
Fix Version/s:	None

Type:

Bug

Priority:

Major

Reporter:

will ferrer

Assignee:

Guilherme Blanco

Resolution:

Unresolved

Votes:

Labels:

None

Environment:

XP Xamp

Description

I have fixed this in my own version of doctrine but unfortunately I am to far diverged from the trunk to offer a patch.

here is a test case:

public function testSubqueryInWhereWithJoinAndLimit()
    {
        $q = new Doctrine_Query();
        $q->select('u.id');
        $q->from('User u');
        $q->where('u.id NOT IN (SELECT a.id FROM User u2 LEFT JOIN u2.Album a LIMIT 1)');
        $this->assertEqual($q->getSqlQuery(), 'SELECT e.id AS e__id FROM entity e WHERE (e.id NOT IN (SELECT a.id AS a__id FROM entity e2 LEFT JOIN album a ON e2.id = a.user_id WHERE (e2.type = 0) LIMIT 1) AND (e.type = 0))');
    }

To fix the issue I changed this line in Doctrine_Query as follows:

if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery) {

if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery && !$this->isSubquery()) {

Hope that helps.

Sincerely

Will Ferrer

Generated at Tue May 21 12:25:47 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.

[DC-1010] When putting a subquery in the where clause which includes a join and a limit the limit subquery algorithm mistakenly modifies the subquery Created: 21/Jun/11 Updated: 21/Jun/11