Doctrine 2 - ORM

Exception thrown when generating SQL from a DQL subselect where the entity has a foreign key as a primary key

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: Git Master
  • Fix Version/s: 2.1.3
  • Component/s: ORM
  • Security Level: All
  • Labels:
    None

Description

If an entity has a foreign key as part of its primary key and you try to query that entity in a subselect clause in DQL, an exception is thrown in ClassMetadataInfo->getQuotedColumnName. The problem seems to be that it's only considering the field mappings rather than both the field and association mappings when it's looking for the correct column name.

Example entities:

  • Article(id primary key, title, content)
  • ArticleTag(article_id, tag, primary key (article_id, tag))

Example DQL: select art from Article art where exists (select tag from ArticleTag tag where tag.article = art)

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Benjamin Eberlei added a comment - - edited

Verified:

    /**
     * @group DDC-1435
     */
    public function testForeignKeyAsPrimaryKeySubselect()
    {
        $this->assertSqlGeneration(
            "SELECT s FROM Doctrine\Tests\Models\DDC117\DDC117Article s WHERE EXISTS (SELECT r FROM Doctrine\Tests\Models\DDC117\DDC117Reference r WHERE r.source = s)",
            ""
        );
    }

This however is not possible anyways, since you cannot have "r" in a SimpleSelectExpression when it resolves to multiple columns. You can try to select just one arbitrary field, for example."SELECT r.foobar FROM .."

Show
Benjamin Eberlei added a comment - - edited Verified: /** * @group DDC-1435 */ public function testForeignKeyAsPrimaryKeySubselect() { $ this ->assertSqlGeneration( "SELECT s FROM Doctrine\Tests\Models\DDC117\DDC117Article s WHERE EXISTS (SELECT r FROM Doctrine\Tests\Models\DDC117\DDC117Reference r WHERE r.source = s)" , "" ); } This however is not possible anyways, since you cannot have "r" in a SimpleSelectExpression when it resolves to multiple columns. You can try to select just one arbitrary field, for example."SELECT r.foobar FROM .."
Hide
Permalink
Payam Hekmat added a comment -

Would that just be a documentation issue then? I didn't see that restriction in the grammar, but I may have overlooked it. I issued a pull request for this issue assuming that an association mapped field is valid input for getQuotedColumnName.

Selecting a single field does work fine.

Show
Payam Hekmat added a comment - Would that just be a documentation issue then? I didn't see that restriction in the grammar , but I may have overlooked it. I issued a pull request for this issue assuming that an association mapped field is valid input for getQuotedColumnName. Selecting a single field does work fine.
Hide
Permalink
Benjamin Eberlei added a comment -

Did you touch this in your latest changes to SimpleSelectExpression guilherme?

Show
Benjamin Eberlei added a comment - Did you touch this in your latest changes to SimpleSelectExpression guilherme?
Benjamin Eberlei made changes -
Field Original Value New Value
Assignee Benjamin Eberlei [ beberlei ] Guilherme Blanco [ guilhermeblanco ]
Hide
Permalink
Guilherme Blanco added a comment -

Hi,

@beberlei No. Issue was totally unrelated.
Please merge my commit to 2.1.X.

The issue was caused by the identifier iteration was considering that @Id fields are always fields, never associations. You may see that in method getQuoteColumnName.

Solution was to create a new method called getQuotedIdenitiferColumnNames and consume it in SqlWalker.
This addresses the issue.

Committed patch and test case as of: https://github.com/doctrine/doctrine2/commit/0ec2cc557f51d6240396689e36101f62d84d2a38

Cheers,

Show
Guilherme Blanco added a comment - Hi, @beberlei No. Issue was totally unrelated. Please merge my commit to 2.1.X. The issue was caused by the identifier iteration was considering that @Id fields are always fields, never associations. You may see that in method getQuoteColumnName. Solution was to create a new method called getQuotedIdenitiferColumnNames and consume it in SqlWalker. This addresses the issue. Committed patch and test case as of: https://github.com/doctrine/doctrine2/commit/0ec2cc557f51d6240396689e36101f62d84d2a38 Cheers,
Guilherme Blanco made changes -
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 2.2-DEV [ 10157 ]
Resolution Fixed [ 1 ]
Hide
Permalink
Benjamin Eberlei added a comment -

Merged into 2.1.x

Show
Benjamin Eberlei added a comment - Merged into 2.1.x
Benjamin Eberlei made changes -
Fix Version/s 2.1.3 [ 10164 ]
Fix Version/s 2.2-DEV [ 10157 ]
Hide
Permalink
Benjamin Eberlei added a comment -

This issue is referenced in Github Pull-Request GH-159
https://github.com/doctrine/doctrine2/pull/159

Show
Benjamin Eberlei added a comment - This issue is referenced in Github Pull-Request GH-159 https://github.com/doctrine/doctrine2/pull/159
Hide
Permalink
Benjamin Eberlei added a comment -

A related Github Pull-Request [GH-159] was opened
https://github.com/doctrine/dbal/pull/159

Show
Benjamin Eberlei added a comment - A related Github Pull-Request [GH-159] was opened https://github.com/doctrine/dbal/pull/159
Benjamin Eberlei made changes -
Workflow jira [ 13100 ] jira-feedback [ 15072 ]
Benjamin Eberlei made changes -
Workflow jira-feedback [ 15072 ] jira-feedback2 [ 16936 ]
Benjamin Eberlei made changes -
Workflow jira-feedback2 [ 16936 ] jira-feedback3 [ 19189 ]
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=DDC-1435, expand=changesets[-21:-1].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

People

  • Assignee:
    Guilherme Blanco
    Reporter:
    Payam Hekmat
Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: