Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-719

Error in SQL subquery for a ManyToMany selfreferencing enitity when using the SIZE() or IS EMPTY dql function

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0-BETA2
    • Fix Version/s: 2.0-BETA3
    • Component/s: DQL, Mapping Drivers, ORM
    • Security Level: All
    • Labels:
      None
    • Environment:
      PHP 5.3, Ubuntu Lucid, MySQL 5, Apache 2.2

      Description

      I have an entity (Group) with a self-referencing ManyToMany association (parents, children).
      Now im just trying to query all groups without a parent.

      My DQL:

      SELECT g, c, d FROM Volkszaehler\Model\Group g LEFT JOIN g.children c LEFT JOIN g.channels d  WHERE g.parents IS EMPTY
      

      throws this PDOException:

      object(PDOException)#31 (8) {
        ["message":protected]=>
        string(89) "SQLSTATE[42S02]: Base table or view not found: 1146 Table 'volkszaehler.5_' doesn't exist"
        ["string":"Exception":private]=>
        string(0) ""
        ["code":protected]=>
        string(5) "42S02"
        ["file":protected]=>
        string(90) "/home/steffen/workspace/doctrine/lib/vendor/doctrine-dbal/lib/Doctrine/DBAL/Connection.php"
        ["line":protected]=>
        int(568)
        ["trace":"Exception":private]=>
        array(9) {
          [0]=>
          array(6) {
            ["file"]=>
            string(90) "/home/steffen/workspace/doctrine/lib/vendor/doctrine-dbal/lib/Doctrine/DBAL/Connection.php"
            ["line"]=>
            int(568)
            ["function"]=>
            string(5) "query"
            ["class"]=>
            string(3) "PDO"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(1) {
              [0]=>
              string(604) "SELECT g0_.name AS name0, g0_.description AS description1, g0_.id AS id2, g0_.uuid AS uuid3, g1_.name AS name4, g1_.description AS description5, g1_.id AS id6, g1_.uuid AS uuid7, c2_.name AS name8, c2_.description AS description9, c2_.indicator AS indicator10, c2_.resolution AS resolution11, c2_.cost AS cost12, c2_.id AS id13, c2_.uuid AS uuid14 FROM groups g0_ LEFT JOIN groups_groups g3_ ON g0_.id = g3_.parent_id LEFT JOIN groups g1_ ON g1_.id = g3_.child_id LEFT JOIN groups_channel g4_ ON g0_.id = g4_.group_id LEFT JOIN channels c2_ ON c2_.id = g4_.channel_id WHERE (SELECT COUNT(*) FROM  5_) = 0"
            }
          }
          [1]=>
          array(6) {
            ["file"]=>
            string(85) "/home/steffen/workspace/doctrine/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php"
            ["line"]=>
            int(46)
            ["function"]=>
            string(12) "executeQuery"
            ["class"]=>
            string(24) "Doctrine\DBAL\Connection"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(3) {
              [0]=>
              string(604) "SELECT g0_.name AS name0, g0_.description AS description1, g0_.id AS id2, g0_.uuid AS uuid3, g1_.name AS name4, g1_.description AS description5, g1_.id AS id6, g1_.uuid AS uuid7, c2_.name AS name8, c2_.description AS description9, c2_.indicator AS indicator10, c2_.resolution AS resolution11, c2_.cost AS cost12, c2_.id AS id13, c2_.uuid AS uuid14 FROM groups g0_ LEFT JOIN groups_groups g3_ ON g0_.id = g3_.parent_id LEFT JOIN groups g1_ ON g1_.id = g3_.child_id LEFT JOIN groups_channel g4_ ON g0_.id = g4_.group_id LEFT JOIN channels c2_ ON c2_.id = g4_.channel_id WHERE (SELECT COUNT(*) FROM  5_) = 0"
              [1]=>
              array(0) {
              }
              [2]=>
              array(0) {
              }
            }
          }
          [2]=>
          array(6) {
            ["file"]=>
            string(59) "/home/steffen/workspace/doctrine/lib/Doctrine/ORM/Query.php"
            ["line"]=>
            int(265)
            ["function"]=>
            string(7) "execute"
            ["class"]=>
            string(44) "Doctrine\ORM\Query\Exec\SingleSelectExecutor"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(3) {
              [0]=>
              object(Doctrine\DBAL\Connection)#16 (11) {
                ["_conn":protected]=>
                object(Doctrine\DBAL\Driver\PDOConnection)#29 (0) {
                }
                ["_config":protected]=>
                object(Doctrine\ORM\Configuration)#7 (1) {
                  ["_attributes":protected]=>
                  array(7) {
                    ["metadataCacheImpl"]=>
                    object(Doctrine\Common\Cache\ApcCache)#8 (2) {
                      ["_cacheIdsIndexId":"Doctrine\Common\Cache\AbstractCache":private]=>
                      string(18) "doctrine_cache_ids"
                      ["_namespace":"Doctrine\Common\Cache\AbstractCache":private]=>
                      NULL
                    }
                    ["queryCacheImpl"]=>
                    object(Doctrine\Common\Cache\ApcCache)#8 (2) {
                      ["_cacheIdsIndexId":"Doctrine\Common\Cache\AbstractCache":private]=>
                      string(18) "doctrine_cache_ids"
                      ["_namespace":"Doctrine\Common\Cache\AbstractCache":private]=>
                      NULL
                    }
                    ["metadataDriverImpl"]=>
                    object(Doctrine\ORM\Mapping\Driver\AnnotationDriver)#13 (4) {
                      ["_reader":"Doctrine\ORM\Mapping\Driver\AnnotationDriver":private]=>
                      object(Doctrine\Common\Annotations\AnnotationReader)#9 (2) {
                        ["parser":"Doctrine\Common\Annotations\AnnotationReader":private]=>
                        object(Doctrine\Common\Annotations\Parser)#10 (6) {
                          ["lexer":"Doctrine\Common\Annotations\Parser":private]=>
                          object(Doctrine\Common\Annotations\Lexer)#11 (5) {
                            ["tokens":"Doctrine\Common\Lexer":private]=>
                            array(0) {
                            }
                            ["position":"Doctrine\Common\Lexer":private]=>
                            int(0)
                            ["peek":"Doctrine\Common\Lexer":private]=>
                            int(0)
                            ["lookahead"]=>
                            NULL
                            ["token"]=>
                            NULL
                          }
                          ["isNestedAnnotation":"Doctrine\Common\Annotations\Parser":private]=>
                          bool(false)
                          ["defaultAnnotationNamespace":"Doctrine\Common\Annotations\Parser":private]=>
                          string(21) "Doctrine\ORM\Mapping\"
                          ["namespaceAliases":"Doctrine\Common\Annotations\Parser":private]=>
                          array(0) {
                          }
                          ["context":"Doctrine\Common\Annotations\Parser":private]=>
                          string(0) ""
                          ["autoloadAnnotations":"Doctrine\Common\Annotations\Parser":private]=>
                          bool(false)
                        }
                        ["cache":"Doctrine\Common\Annotations\AnnotationReader":private]=>
                        object(Doctrine\Common\Cache\ArrayCache)#12 (3) {
                          ["data":"Doctrine\Common\Cache\ArrayCache":private]=>
                          array(0) {
                          }
                          ["_cacheIdsIndexId":"Doctrine\Common\Cache\AbstractCache":private]=>
                          string(18) "doctrine_cache_ids"
                          ["_namespace":"Doctrine\Common\Cache\AbstractCache":private]=>
                          NULL
                        }
                      }
                      ["_paths":protected]=>
                      array(1) {
                        [0]=>
                        string(58) "/home/steffen/workspace/volkszaehler.org/backend/lib/Model"
                      }
                      ["_fileExtension":protected]=>
                      string(4) ".php"
                      ["_classNames":protected]=>
                      NULL
                    }
                    ["proxyDir"]=>
                    string(66) "/home/steffen/workspace/volkszaehler.org/backend/lib/Model/Proxies"
                    ["proxyNamespace"]=>
                    string(26) "Volkszaehler\Model\Proxies"
                    ["autoGenerateProxyClasses"]=>
                    bool(true)
                    ["sqlLogger"]=>
                    object(Volkszaehler\Util\Debug)#22 (5) {
                      ["queries":protected]=>
                      array(1) {
                        [0]=>
                        array(2) {
                          ["sql"]=>
                          string(604) "SELECT g0_.name AS name0, g0_.description AS description1, g0_.id AS id2, g0_.uuid AS uuid3, g1_.name AS name4, g1_.description AS description5, g1_.id AS id6, g1_.uuid AS uuid7, c2_.name AS name8, c2_.description AS description9, c2_.indicator AS indicator10, c2_.resolution AS resolution11, c2_.cost AS cost12, c2_.id AS id13, c2_.uuid AS uuid14 FROM groups g0_ LEFT JOIN groups_groups g3_ ON g0_.id = g3_.parent_id LEFT JOIN groups g1_ ON g1_.id = g3_.child_id LEFT JOIN groups_channel g4_ ON g0_.id = g4_.group_id LEFT JOIN channels c2_ ON c2_.id = g4_.channel_id WHERE (SELECT COUNT(*) FROM  5_) = 0"
                          ["parameters"]=>
                          array(0) {
                          }
                        }
                      }
                      ["messages":protected]=>
                      array(0) {
                      }
                      ["started":protected]=>
                      NULL
                      ["level":protected]=>
                      string(1) "1"
                      ["created"]=>
                      float(1280063214.6367)
                    }
                  }
                }
                ["_eventManager":protected]=>
                object(Doctrine\Common\EventManager)#14 (1) {
                  ["_listeners":"Doctrine\Common\EventManager":private]=>
                  array(0) {
                  }
                }
                ["_isConnected":"Doctrine\DBAL\Connection":private]=>
                bool(true)
                ["_transactionNestingLevel":"Doctrine\DBAL\Connection":private]=>
                int(0)
                ["_transactionIsolationLevel":"Doctrine\DBAL\Connection":private]=>
                int(2)
                ["_params":"Doctrine\DBAL\Connection":private]=>
                array(5) {
                  ["driver"]=>
                  string(9) "pdo_mysql"
                  ["host"]=>
                  string(9) "localhost"
                  ["user"]=>
                  string(2) "vz"
                  ["password"]=>
                  string(4) "demo"
                  ["dbname"]=>
                  string(12) "volkszaehler"
                }
                ["_platform":protected]=>
                object(Doctrine\DBAL\Platforms\MySqlPlatform)#17 (1) {
                  ["doctrineTypeMapping":protected]=>
                  NULL
                }
                ["_schemaManager":protected]=>
                NULL
                ["_driver":protected]=>
                object(Doctrine\DBAL\Driver\PDOMySql\Driver)#15 (0) {
                }
                ["_isRollbackOnly":"Doctrine\DBAL\Connection":private]=>
                bool(false)
              }
              [1]=>
              array(0) {
              }
              [2]=>
              array(0) {
              }
            }
          }
          [3]=>
          array(6) {
            ["file"]=>
            string(67) "/home/steffen/workspace/doctrine/lib/Doctrine/ORM/AbstractQuery.php"
            ["line"]=>
            int(522)
            ["function"]=>
            string(10) "_doExecute"
            ["class"]=>
            string(18) "Doctrine\ORM\Query"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(0) {
            }
          }
          [4]=>
          array(6) {
            ["file"]=>
            string(67) "/home/steffen/workspace/doctrine/lib/Doctrine/ORM/AbstractQuery.php"
            ["line"]=>
            int(360)
            ["function"]=>
            string(7) "execute"
            ["class"]=>
            string(26) "Doctrine\ORM\AbstractQuery"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(2) {
              [0]=>
              array(0) {
              }
              [1]=>
              int(1)
            }
          }
          [5]=>
          array(6) {
            ["file"]=>
            string(83) "/home/steffen/workspace/volkszaehler.org/backend/lib/Controller/GroupController.php"
            ["line"]=>
            int(57)
            ["function"]=>
            string(9) "getResult"
            ["class"]=>
            string(26) "Doctrine\ORM\AbstractQuery"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(0) {
            }
          }
          [6]=>
          array(6) {
            ["file"]=>
            string(78) "/home/steffen/workspace/volkszaehler.org/backend/lib/Controller/Controller.php"
            ["line"]=>
            int(54)
            ["function"]=>
            string(3) "get"
            ["class"]=>
            string(39) "Volkszaehler\Controller\GroupController"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(0) {
            }
          }
          [7]=>
          array(6) {
            ["file"]=>
            string(67) "/home/steffen/workspace/volkszaehler.org/backend/lib/Dispatcher.php"
            ["line"]=>
            int(149)
            ["function"]=>
            string(3) "run"
            ["class"]=>
            string(34) "Volkszaehler\Controller\Controller"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(1) {
              [0]=>
              string(3) "get"
            }
          }
          [8]=>
          array(6) {
            ["file"]=>
            string(58) "/home/steffen/workspace/volkszaehler.org/backend/index.php"
            ["line"]=>
            int(55)
            ["function"]=>
            string(3) "run"
            ["class"]=>
            string(23) "Volkszaehler\Dispatcher"
            ["type"]=>
            string(2) "->"
            ["args"]=>
            array(0) {
            }
          }
        }
        ["previous":"Exception":private]=>
        NULL
        ["errorInfo"]=>
        array(3) {
          [0]=>
          string(5) "42S02"
          [1]=>
          int(1146)
          [2]=>
          string(37) "Table 'volkszaehler.5_' doesn't exist"
        }
      }
      

      Here is my Group Entitiy:

      namespace Volkszaehler\Model;
      
      use Doctrine\Common\Collections;
      
      use Doctrine\Common\Collections\ArrayCollection;
      
      /**
       * Group entity
       *
       * @author Steffen Vogel <info@steffenvogel.de>
       * @package default
       *
       * @Entity
       * @Table(name="groups")
       */
      class Group extends Entity {
      	/** @Column(type="string", nullable=false) */
      	protected $name;
      
      	/** @Column(type="string", nullable=true) */
      	protected $description;
      
      	/**
      	 * @ManyToMany(targetEntity="Channel", inversedBy="groups")
      	 * @JoinTable(name="groups_channel",
      	 * 		joinColumns={@JoinColumn(name="group_id", referencedColumnName="id")},
      	 * 		inverseJoinColumns={@JoinColumn(name="channel_id", referencedColumnName="id")}
      	 * )
      	 */
      	protected $channels = NULL;
      
      	/**
      	 * @ManyToMany(targetEntity="Group", inversedBy="parents")
      	 * @JoinTable(name="groups_groups",
      	 * 		joinColumns={@JoinColumn(name="parent_id", referencedColumnName="id")},
      	 * 		inverseJoinColumns={@JoinColumn(name="child_id", referencedColumnName="id")}
      	 * )
      	 */
      	protected $children = NULL;
      
      	/**
      	 * @ManyToMany(targetEntity="Group", mappedBy="children")
      	 */
      	protected $parents = NULL;
      
      	/**
      	 * construct
      	 */
      	public function __construct() {
      		parent::__construct();
      
      		$this->channels = new ArrayCollection();
      		$this->children = new ArrayCollection();
      		$this->parents = new ArrayCollection();
      	}
      
      	/**
      	 * adds group as new child
      	 *
      	 * @param Group $child
      	 * @todo check against endless recursion
      	 * @todo check if the group is already member of the group
      	 */
      	public function addGroup(Group $child) {
      		$this->children->add($child);
      	}
      
      	/**
      	 * adds channel as new child
      	 *
      	 * @param Channel $child
      	 * @todo check if the channel is already member of the group
      	 */
      	public function addChannel(Channel $child) {
      		$this->channels->add($child);
      	}
      
      	/**
      	 * getter & setter
      	 */
      	public function getName() { return $this->name; }
      	public function setName($name) { $this->name = $name; }
      	public function getDescription() { return $this->description; }
      	public function setDescription($description) { $this->description = $description; }
      	public function getChildren() { return $this->children; }
      	public function getParents() { return $this->parents; }
      	public function getChannels() { return $this->channels; }
      }
      

        Activity

        Hide
        Roman S. Borschel added a comment -

        Closing with correct version.

        Show
        Roman S. Borschel added a comment - Closing with correct version.
        Hide
        Roman S. Borschel added a comment -

        Reopening in order to correct the fixed version.

        Show
        Roman S. Borschel added a comment - Reopening in order to correct the fixed version.
        Hide
        Guilherme Blanco added a comment -

        In http://github.com/doctrine/doctrine2/commit/35af98260a525a841c05be15f52f8df455000066 I committed a fix to this issue.
        Should be working now =)

        Show
        Guilherme Blanco added a comment - In http://github.com/doctrine/doctrine2/commit/35af98260a525a841c05be15f52f8df455000066 I committed a fix to this issue. Should be working now =)
        Hide
        Guilherme Blanco added a comment -

        Path to DDC-719 aswell as a possible issue with collection member implementation.

        Show
        Guilherme Blanco added a comment - Path to DDC-719 aswell as a possible issue with collection member implementation.
        Hide
        Roman S. Borschel added a comment -

        That the jointable info is only on the owning side is by design, not a flaw.

        Show
        Roman S. Borschel added a comment - That the jointable info is only on the owning side is by design, not a flaw.
        Hide
        Guilherme Blanco added a comment -

        Ok, it seems that on Mapping drivers we don't map jointables on opposite side.

        So, if you map something on inversedBy and you grab the association from mappedBy side, you'll never have the jointable definition, because it is not exported to us.
        This seems like a bug flaw we have on our code... not at the point of a critical, but we need to fix it asap.

        Show
        Guilherme Blanco added a comment - Ok, it seems that on Mapping drivers we don't map jointables on opposite side. So, if you map something on inversedBy and you grab the association from mappedBy side, you'll never have the jointable definition, because it is not exported to us. This seems like a bug flaw we have on our code... not at the point of a critical, but we need to fix it asap.
        Hide
        Guilherme Blanco added a comment -

        No, it is a bug on ClassMetadata.

        The var_dump on Association of parents refers to a NULL on joinTable.
        Problem seems to be deeper. I am creating a test case, will figure it out soon.

        Show
        Guilherme Blanco added a comment - No, it is a bug on ClassMetadata. The var_dump on Association of parents refers to a NULL on joinTable. Problem seems to be deeper. I am creating a test case, will figure it out soon.
        Hide
        Benjamin Eberlei added a comment -

        Looks like abug in the SQL Walker.

        btw, you can use $e->getTraceAsString() to get a nice looking output for an exception. Its not as verbose as var_dump on the exception

        Show
        Benjamin Eberlei added a comment - Looks like abug in the SQL Walker. btw, you can use $e->getTraceAsString() to get a nice looking output for an exception. Its not as verbose as var_dump on the exception

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Steffen Vogel
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: