Doctrine 1
  1. Doctrine 1
  2. DC-930

Complex query with DISTINCT and LIMIT on pgsql causes a SQLSTATE exception - problem in doctrine_subquery_alias

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.2
    • Fix Version/s: None
    • Component/s: Query
    • Labels:
      None
    • Environment:
      pgsql

      Description

      There is a problem in the following code in Doctrine/Query.php (lines 1257-1279) inside the buildSqlQuery() method:

                  $subquery = $this->getLimitSubquery();
      
                  // what about composite keys?
                  $idColumnName = $table->getColumnName($table->getIdentifier());
      
                  switch (strtolower($this->_conn->getDriverName())) {
                      case 'mysql':
      [...]
                      case 'pgsql':
                          $subqueryAlias = $this->_conn->quoteIdentifier('doctrine_subquery_alias');
      
                          // pgsql needs special nested LIMIT subquery
                          $subquery = 'SELECT ' . $subqueryAlias . '.' . $this->_conn->quoteIdentifier($idColumnName)
                                  . ' FROM (' . $subquery . ') AS ' . $subqueryAlias;
      
                          break;
                  }
      

      The above code is executed when a query consist of DISTINCT and LIMIT clauses. The most common situation is using pager.
      The problem is in the subquery variable. The $idColumnName variable often has value "id". In such a situation in case of $subquery consist of some JOINs and some tables have column named "id", we have:

      SELECT doctrine_subquery_alias.id FROM ((SELECT DISTINCT d1.id, d2.id FROM ...)) AS doctrine_subquery_alias
      

      It, of course, causes the "ambiguous column name" pgsql exception.

        Activity

        Jacek Dębowczyk created issue -

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

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            Jacek Dębowczyk
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated: