Doctrine 1

When putting a subquery in the where clause which includes a join and a limit the limit subquery algorithm mistakenly modifies the subquery

Details

  • Type: Bug Bug
  • Status: Open Open
  • Priority: Major Major
  • Resolution: Unresolved
  • Affects Version/s: 1.2.2
  • Fix Version/s: None
  • Component/s: Query
  • 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

Activity

Ascending order - Click to sort in descending order
will ferrer made changes -
Field Original Value New Value
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:
[code]
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))');
    }
[code]

To fix the issue I changed this line in Doctrine_Query as follows:
[code]
if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery) {
[code]
=
[code]
if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery && !$this->isSubquery()) {
[code]

Hope that helps.

Sincerely

Will Ferrer 		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:
{code}
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))');
    }
{code}

To fix the issue I changed this line in Doctrine_Query as follows:
{code}
if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery) {
[code]
=
{code}
if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery && !$this->isSubquery()) {
{code}

Hope that helps.

Sincerely

Will Ferrer
will ferrer made changes -
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:
{code}
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))');
    }
{code}

To fix the issue I changed this line in Doctrine_Query as follows:
{code}
if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery) {
[code]
=
{code}
if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery && !$this->isSubquery()) {
{code}

Hope that helps.

Sincerely

Will Ferrer 		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:
{code}
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))');
    }
{code}

To fix the issue I changed this line in Doctrine_Query as follows:
{code}
if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery) {
[code]
=
{code}
if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery && !$this->isSubquery()) {
{code}

Hope that helps.

Sincerely

Will Ferrer
will ferrer made changes -
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:
{code}
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))');
    }
{code}

To fix the issue I changed this line in Doctrine_Query as follows:
{code}
if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery) {
[code]
=
{code}
if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery && !$this->isSubquery()) {
{code}

Hope that helps.

Sincerely

Will Ferrer 		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:
{code}
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))');
    }
{code}

To fix the issue I changed this line in Doctrine_Query as follows:
{code}
if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery) {
{code}
=
{code}
if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery && !$this->isSubquery()) {
{code}

Hope that helps.

Sincerely

Will Ferrer
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-1010, expand=changesets[-21:-1].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

People

  • Assignee:
    Guilherme Blanco
    Reporter:
    will ferrer
Vote (0)
Watch (0)

Dates

  • Created:
    Updated: