DDC-479: MEMBER OF generates wrong SQL #596

New Issue

admin · 2026-01-22T12:43:48+01:00

admin commented

2026-01-22 12:43:48 +01:00

Originally created by @doctrinebot on GitHub (Mar 26, 2010).

Jira issue originally created by user @beberlei:

        $dql = 'SELECT p FROM Whitewashing\Blog\Post p WHERE ?1 MEMBER OF p.tags';

        return $this->_em->createQuery($dql)
                         ->setParameter(1, $tagId)
                         ->setMaxResults(10)
                         ->getResult();

SELECT b0*.post_headline AS post_headline0, b0_.post_text AS post_text1, b0_.post_created AS post_created2, b0_.is_published AS is_published3, b0_.id AS id4, b0_.author_id AS author_id5, b0_.blog_id AS blog_id6 FROM blog_posts b0_ WHERE EXISTS (SELECT 1 FROM blog_posts_tags b1_ INNER JOIN blog_tags b2_ ON b1_.post_id = b0_.id WHERE b1_.tag_id = b2_.id AND b2*.id = 1);

Generates the following error: ERROR 1054 (42S22): Unknown column 'b0_.id' in 'on clause'

Originally created by @doctrinebot on GitHub (Mar 26, 2010). Jira issue originally created by user @beberlei: ``` $dql = 'SELECT p FROM Whitewashing\Blog\Post p WHERE ?1 MEMBER OF p.tags'; return $this->_em->createQuery($dql) ->setParameter(1, $tagId) ->setMaxResults(10) ->getResult(); ``` ``` SELECT b0*.post_headline AS post_headline0, b0_.post_text AS post_text1, b0_.post_created AS post_created2, b0_.is_published AS is_published3, b0_.id AS id4, b0_.author_id AS author_id5, b0_.blog_id AS blog_id6 FROM blog_posts b0_ WHERE EXISTS (SELECT 1 FROM blog_posts_tags b1_ INNER JOIN blog_tags b2_ ON b1_.post_id = b0_.id WHERE b1_.tag_id = b2_.id AND b2*.id = 1); ``` Generates the following error: **ERROR 1054 (42S22): Unknown column 'b0_.id' in 'on clause'**

admin added the Bug label 2026-01-22 12:43:48 +01:00

admin closed this issue

2026-01-22 12:43:49 +01:00

admin commented

2026-01-22 12:43:50 +01:00

@doctrinebot commented on GitHub (Mar 26, 2010):

Comment created by romanb:

Correct SQL should be:

SELECT b0*.post_headline AS post_headline0, b0_.post_text AS post_text1, b0_.post_created AS post_created2, b0_.is_published AS is_published3, b0_.id AS id4, b0_.author_id AS author_id5, b0_.blog_id AS blog_id6 FROM blog_posts b0_ WHERE EXISTS (SELECT 1 FROM blog_posts_tags b1_ INNER JOIN blog_tags b2_ ON b1_.tag_id = b2_.id WHERE b1_.post_id = b0_.id AND b2*.id = 1)

@doctrinebot commented on GitHub (Mar 26, 2010): Comment created by romanb: Correct SQL should be: ``` SELECT b0*.post_headline AS post_headline0, b0_.post_text AS post_text1, b0_.post_created AS post_created2, b0_.is_published AS is_published3, b0_.id AS id4, b0_.author_id AS author_id5, b0_.blog_id AS blog_id6 FROM blog_posts b0_ WHERE EXISTS (SELECT 1 FROM blog_posts_tags b1_ INNER JOIN blog_tags b2_ ON b1_.tag_id = b2_.id WHERE b1_.post_id = b0_.id AND b2*.id = 1) ```

admin commented

2026-01-22 12:43:50 +01:00

@doctrinebot commented on GitHub (Mar 26, 2010):

Comment created by romanb:

Should be fixed now.

@doctrinebot commented on GitHub (Mar 26, 2010): Comment created by romanb: Should be fixed now.

admin commented

2026-01-22 12:43:51 +01:00

@doctrinebot commented on GitHub (Mar 26, 2010):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Mar 26, 2010): Issue was closed with resolution "Fixed"

admin commented

2026-01-22 12:43:54 +01:00

@doctrinebot commented on GitHub (Jul 30, 2010):

Comment created by jorritposthuma:

Not sure if it is the same, but MEMBER OF might still not generating correct SQL (or we are doing something wrong):

SELECT g FROM Model:User\Group g WHERE :user MEMBER OF g.users

Tables:
User_User

/****
 * @var array The groups of this user
 * @method array getGroups() Returns all the groups
 * @method void setGroups($groups) Sets the groups
 * @ManyToMany( targetEntity = "JJ\Model\Instance\User\Group", inversedBy = "users" )
 * @JoinTable( name="User_UserGroups",
 *          joinColumns         = { @JoinColumn( name = "user_id",  referencedColumnName = "id" ) },
 *          inverseJoinColumns  = { @JoinColumn( name = "group_id", referencedColumnName = "id" ) }
 *      )
 */
protected $groups;

User_Group

/****
 * @var array The users of this group
 * @method array getUsers() Returns all the users
 * @method void setUsers($users) Sets the users
 * @ManyToMany( targetEntity = "JJ\Model\Instance\User\User", mappedBy = "groups" )
 * @JoinTable( name="`User_UserGroups`",
 *          joinColumns         = { @JoinColumn( name = "group_id", referencedColumnName = "id" ) },
 *          inverseJoinColumns  = { @JoinColumn( name = "user_id",  referencedColumnName = "id" ) }
 *      )
 */
protected $users;

Results in:
SELECT u0_.id AS id0, u0_.name AS name1, u0_.removable AS removable2 FROM User_Group u0_ WHERE EXISTS (SELECT 1 FROM u1_ INNER JOIN User_User u2_ ON u1_.user_id = u2_.id WHERE u1_.group_id = u0_.id AND u2_.id = ?)

Should be:
SELECT u0_.id AS id0, u0_.name AS name1, u0_.removable AS removable2 FROM User_Group u0_ WHERE EXISTS (SELECT 1 FROM User_UserGroups u1_ INNER JOIN User_User u2_ ON u1_.user_id = u2_.id WHERE u1_.group_id = u0_.id AND u2_.id = 1)

@doctrinebot commented on GitHub (Jul 30, 2010): Comment created by jorritposthuma: Not sure if it is the same, but MEMBER OF might still not generating correct SQL (or we are doing something wrong): SELECT g FROM Model:User\Group g WHERE :user MEMBER OF g.users Tables: User_User ``` /**** * @var array The groups of this user * @method array getGroups() Returns all the groups * @method void setGroups($groups) Sets the groups * @ManyToMany( targetEntity = "JJ\Model\Instance\User\Group", inversedBy = "users" ) * @JoinTable( name="User_UserGroups", * joinColumns = { @JoinColumn( name = "user_id", referencedColumnName = "id" ) }, * inverseJoinColumns = { @JoinColumn( name = "group_id", referencedColumnName = "id" ) } * ) */ protected $groups; ``` User_Group ``` /**** * @var array The users of this group * @method array getUsers() Returns all the users * @method void setUsers($users) Sets the users * @ManyToMany( targetEntity = "JJ\Model\Instance\User\User", mappedBy = "groups" ) * @JoinTable( name="`User_UserGroups`", * joinColumns = { @JoinColumn( name = "group_id", referencedColumnName = "id" ) }, * inverseJoinColumns = { @JoinColumn( name = "user_id", referencedColumnName = "id" ) } * ) */ protected $users; ``` Results in: SELECT u0_.id AS id0, u0_.name AS name1, u0_.removable AS removable2 FROM User_Group u0_ WHERE EXISTS (SELECT 1 FROM u1_ INNER JOIN User_User u2_ ON u1_.user_id = u2_.id WHERE u1_.group_id = u0_.id AND u2_.id = ?) Should be: SELECT u0_.id AS id0, u0_.name AS name1, u0_.removable AS removable2 FROM User_Group u0_ WHERE EXISTS (SELECT 1 FROM User_UserGroups u1_ INNER JOIN User_User u2_ ON u1_.user_id = u2_.id WHERE u1_.group_id = u0_.id AND u2_.id = 1)

admin commented

2026-01-22 12:43:54 +01:00

@doctrinebot commented on GitHub (Jul 30, 2010):

Comment created by jorritposthuma:

I guess that _validateAndCompleteMapping of AssociationMapping needs the addition of:

$this->joinTable = $mapping['joinTable']; at line 222 resulting in:

/****
 * Validates & completes the mapping. Mapping defaults are applied here.
 *
 * @param array $mapping
 * @throws MappingException If something is wrong with the mapping.
 */
protected function _validateAndCompleteMapping(array $mapping)
{        
    // Mandatory attributes for both sides
    if ( ! isset($mapping['fieldName'])) {
        throw MappingException::missingFieldName();
    }
    $this->sourceFieldName = $mapping['fieldName'];

    if ( ! isset($mapping['sourceEntity'])) {
        throw MappingException::missingSourceEntity($mapping['fieldName']);
    }
    $this->sourceEntityName = $mapping['sourceEntity'];

    if ( ! isset($mapping['targetEntity'])) {
        throw MappingException::missingTargetEntity($mapping['fieldName']);
    }
    $this->targetEntityName = $mapping['targetEntity'];

    // Mandatory and optional attributes for either side
    if ( ! isset($mapping['mappedBy'])) {            
        // Optional
        if (isset($mapping['joinTable']) && $mapping['joinTable']) {
            if ($mapping['joinTable']['name'][0] == '`') {
                $mapping['joinTable']['name'] = trim($mapping['joinTable']['name'], '`');
                $mapping['joinTable']['quoted'] = true;
            }
            $this->joinTable = $mapping['joinTable'];
        }
        if (isset($mapping['inversedBy'])) {
            $this->inversedBy = $mapping['inversedBy'];
        }
    } else {
        $this->isOwningSide = false;
        $this->mappedBy = $mapping['mappedBy'];
        $this->joinTable = $mapping['joinTable'];
    }

    // Optional attributes for both sides
    $this->fetchMode = isset($mapping['fetch']) ? $mapping['fetch'] : self::FETCH_LAZY;
    $cascades = isset($mapping['cascade']) ? $mapping['cascade'] : array();

    if (in_array('all', $cascades)) {
        $cascades = array(
           'remove',
           'persist',
           'refresh',
           'merge',
           'detach'
        );
    }

    $this->isCascadeRemove = in_array('remove',  $cascades);
    $this->isCascadePersist = in_array('persist', $cascades);
    $this->isCascadeRefresh = in_array('refresh', $cascades);
    $this->isCascadeMerge = in_array('merge',   $cascades);
    $this->isCascadeDetach = in_array('detach',  $cascades);
}

@doctrinebot commented on GitHub (Jul 30, 2010): Comment created by jorritposthuma: I guess that _validateAndCompleteMapping of AssociationMapping needs the addition of: $this->joinTable = $mapping['joinTable']; at line 222 resulting in: ``` /**** * Validates & completes the mapping. Mapping defaults are applied here. * * @param array $mapping * @throws MappingException If something is wrong with the mapping. */ protected function _validateAndCompleteMapping(array $mapping) { // Mandatory attributes for both sides if ( ! isset($mapping['fieldName'])) { throw MappingException::missingFieldName(); } $this->sourceFieldName = $mapping['fieldName']; if ( ! isset($mapping['sourceEntity'])) { throw MappingException::missingSourceEntity($mapping['fieldName']); } $this->sourceEntityName = $mapping['sourceEntity']; if ( ! isset($mapping['targetEntity'])) { throw MappingException::missingTargetEntity($mapping['fieldName']); } $this->targetEntityName = $mapping['targetEntity']; // Mandatory and optional attributes for either side if ( ! isset($mapping['mappedBy'])) { // Optional if (isset($mapping['joinTable']) && $mapping['joinTable']) { if ($mapping['joinTable']['name'][0] == '`') { $mapping['joinTable']['name'] = trim($mapping['joinTable']['name'], '`'); $mapping['joinTable']['quoted'] = true; } $this->joinTable = $mapping['joinTable']; } if (isset($mapping['inversedBy'])) { $this->inversedBy = $mapping['inversedBy']; } } else { $this->isOwningSide = false; $this->mappedBy = $mapping['mappedBy']; $this->joinTable = $mapping['joinTable']; } // Optional attributes for both sides $this->fetchMode = isset($mapping['fetch']) ? $mapping['fetch'] : self::FETCH_LAZY; $cascades = isset($mapping['cascade']) ? $mapping['cascade'] : array(); if (in_array('all', $cascades)) { $cascades = array( 'remove', 'persist', 'refresh', 'merge', 'detach' ); } $this->isCascadeRemove = in_array('remove', $cascades); $this->isCascadePersist = in_array('persist', $cascades); $this->isCascadeRefresh = in_array('refresh', $cascades); $this->isCascadeMerge = in_array('merge', $cascades); $this->isCascadeDetach = in_array('detach', $cascades); } ```

admin commented

2026-01-22 12:43:56 +01:00

@doctrinebot commented on GitHub (Jul 30, 2010):

Comment created by romanb:

No, only the owning side has the join table. Your mapping is wrong. Remove @JoinTable from the inverse side entirely.

@doctrinebot commented on GitHub (Jul 30, 2010): Comment created by romanb: No, only the owning side has the join table. Your mapping is wrong. Remove @JoinTable from the inverse side entirely.

admin commented

2026-01-22 12:43:57 +01:00

@doctrinebot commented on GitHub (Jul 30, 2010):

Comment created by jorritposthuma:

Thanks, just figured that out :).

@doctrinebot commented on GitHub (Jul 30, 2010): Comment created by jorritposthuma: Thanks, just figured that out :).

admin commented

2026-01-22 12:43:57 +01:00

@doctrinebot commented on GitHub (Jul 30, 2010):

Comment created by jorritposthuma:

Now i remember why we do have the @JoinTable on the inverse side. We want to specify a @JoinTable( name="User_UserGroups" ). It doen't work only specified on the owning side, and we figured out that this was the only way?

@doctrinebot commented on GitHub (Jul 30, 2010): Comment created by jorritposthuma: Now i remember why we do have the @JoinTable on the inverse side. We want to specify a @JoinTable( name="User_UserGroups" ). It doen't work only specified on the owning side, and we figured out that this was the only way?

admin commented

2026-01-22 12:43:59 +01:00

@doctrinebot commented on GitHub (Jul 30, 2010):

Comment created by jorritposthuma:

Never mind. Need to get some sleep. Sorry for the inconvenience. Only wanted to help. Love the Doctrine 2 project!!!

@doctrinebot commented on GitHub (Jul 30, 2010): Comment created by jorritposthuma: Never mind. Need to get some sleep. Sorry for the inconvenience. Only wanted to help. Love the Doctrine 2 project!!!

admin commented

2026-01-22 12:43:59 +01:00

@doctrinebot commented on GitHub (Jul 30, 2010):

Comment created by jorritposthuma:

Sorry, last question, shouldn't the SqlWalker in that case call getQuotedJoinTableName on the owningAssoc, instead of assoc? ( Line 1404 rev. 0c07b31136 )

@doctrinebot commented on GitHub (Jul 30, 2010): Comment created by jorritposthuma: Sorry, last question, shouldn't the SqlWalker in that case call getQuotedJoinTableName on the owningAssoc, instead of assoc? ( Line 1404 rev. 0c07b311360070a2268c0f900c96f5aed643f5ca )

admin commented

2026-01-22 12:44:00 +01:00

@doctrinebot commented on GitHub (Jul 31, 2010):

Comment created by jorritposthuma:

Should i create a new Issue for that?

@doctrinebot commented on GitHub (Jul 31, 2010): Comment created by jorritposthuma: Should i create a new Issue for that?

admin commented

2026-01-22 12:44:01 +01:00

@doctrinebot commented on GitHub (Aug 1, 2010):

Comment created by @beberlei:

yes please!

@doctrinebot commented on GitHub (Aug 1, 2010): Comment created by @beberlei: yes please!