[DC-930] Complex query with DISTINCT and LIMIT on pgsql causes a SQLSTATE exception - problem in doctrine_subquery_alias Created: 16/Nov/10  Updated: 16/Nov/10

Status: Open
Project: Doctrine 1
Component/s: Query
Affects Version/s: 1.2.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Jacek Dębowczyk Assignee: Guilherme Blanco
Resolution: Unresolved Votes: 0
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.
Generated at Sun May 19 08:43:49 UTC 2013 using JIRA 5.2.7#850-sha1:b2af0c8dc8537b36121c6a579fabbdf79fc919e5.