Doctrine 1

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

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:
    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

People

  • Assignee:
    Guilherme Blanco
    Reporter:
    Jacek Dębowczyk
Vote (0)
Watch (0)

Dates

  • Created:
    Updated: