When using a combination of: a group by field referencing a table in a relation, a join to a different table via a many type relation and a limit clause, doctrine creates a broken query then throws an exception

Hi All

I have run into a very problematic Doctrine 1.2.2 bug which puts me in quite a bit of danger of having to rewrite my whole app in Doctrine 2 (hopefully if this is a bug it isn't present in Doctrine 2).

The problem I am running into seems like something that probably would have been found and rectified however so hopefully there is something wrong in my execution.

The problem I am running into is caused when I have a combination of the following 3 things in my query:
1) A group by field referencing a table in a relation
2) A join to a different table via a many type relation
3) A limit clause

This combination causes Doctrine to create a broken SQL query which it then throws an exception about when I try to execute the query or call getSqlQuery() on it.

Using some sample data I put together some very simple examples to illustrate the problem:

$q = Doctrine_Query::create(); 
$q->from('Customer Customer'); 
$q->leftJoin('Customer.Order Order'); 
$q->leftJoin('Customer.Zip Zip'); 
$q->addGroupBy('Zip.city');
$q->addSelect('Zip.city as city');
$q->addSelect('Customer.customer_id'); 
$q->addSelect('Order.order_id');
$q->offset(0);
$q->limit(5);

This creates the following dql:

SELECT Zip.city as city, Customer.customer_id, Order.order_id FROM Customer Customer LEFT JOIN Customer.Order Order LEFT JOIN Customer.Zip Zip GROUP BY Zip.city LIMIT 5 OFFSET 0

However when I attempt to run $q->getSqlQuery() an exception is thrown:
{"type":"exception","tid":3,"exception":{},"message":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'z2.city' in 'group statement'. Failing Query: \"SELECT DISTINCT c2.customer_id FROM customers c2 GROUP BY z2.city LIMIT 5\"","where":"#0 C:\\htdocs\\php_library\\Doctrine-1.2.1\\lib\\Doctrine
Connection.php(1025): Doctrine_Connection->rethrowException(Object(PDOException), Object(Doctrine_Connection_Mysql), 'SELECT DISTINCT...')\n#1 C:\\htdocs\\php_library\\Doctrine-1.2.1\\lib\\Doctrine
Query.php(1263): Doctrine_Connection->execute('SELECT DISTINCT...', Array)\n#2 C:\\htdocs\\php_library\\Doctrine-1.2.1\\lib\\Doctrine
Query.php(1122): Doctrine_Query->buildSqlQuery(true)\n#3............

As you can see from the SQL in the exception Doctrine is trying to create a query that groups by a field from the Zip table with out first joining to it (SELECT DISTINCT c2.customer_id FROM customers c2 GROUP BY z2.city LIMIT 5).

I know Doctrine works some magic to get limit statements to work with joins and I suspect that something with in that magic may be broken, but hopefully its something I am doing wrong.

Take out any one of the 3 things I mentioned I above and everything works fine – the following all work:

Remove the limit:

$q = Doctrine_Query::create(); 
$q->from('Customer Customer'); 
$q->leftJoin('Customer.Order Order'); 
$q->leftJoin('Customer.Zip Zip'); 
$q->addGroupBy('Zip.city'); 
$q->addSelect('Zip.city as city');
$q->addSelect('Order.order_id');
$q->addSelect('Customer.customer_id'); 

Remove the additional join:

$q = Doctrine_Query::create(); 
$q->from('Customer Customer'); 
$q->leftJoin('Customer.Zip Zip'); 
$q->addGroupBy('Zip.city'); 
$q->addSelect('Zip.city as city');
$q->addSelect('Customer.customer_id');
$q->offset(0);
$q->limit(5);

Remove the group by:

$q = Doctrine_Query::create(); 
$q->from('Customer Customer'); 
$q->leftJoin('Customer.Order Order'); 
$q->leftJoin('Customer.Zip Zip'); 
$q->addSelect('Zip.city as city');
$q->addSelect('Customer.customer_id'); 
$q->addSelect('Order.order_id');
$q->offset(0);
$q->limit(5);

Its also worth noting that the following changes also stop the problem from happening:
Changing the relation to the Order table to be a one relation instead of a many relation.
Changing the group by to a field located in the "from" table (such as: $q->addGroupBy('Customer.customer_id')

Here are the relevant parts of my sample data schema:

detect_relations: false
package: Example
options:
  type: INNODB
  charset: utf8
Order:
  tableName: orders
  columns:
    order_id:
      type: integer(4)
      primary: true
      notnull: true
    customer_id:
      type: integer(4)
    order_date: timestamp
  relations:
    OrderItem:
      type: many
      local: order_id
      foreign: order_id
    Customer:
      type: one
      local: customer_id
      foreign: customer_id
  options:
    type: InnoDB
Customer:
  tableName: customers
  columns:
    customer_id:
      type: integer(4)
      primary: true
      notnull: true
      autoincrement: true
    firstname:
      type: string(45)
    lastname:
      type: string(45)
    streetaddress:
      type: string(45)
    city:
      type: string(45)
    state:
      type: string(45)
    postalcode:
      type: string(45)
  relations:
    Order:
      type: many
      local: customer_id
      foreign: customer_id
    Zip:
      type: one
      local: postalcode
      foreign: postalcode
  options:
    type: InnoDB
Zip:
  connection: default_schema
  tableName: zips
  columns:
    postalcode:
      type: varchar(30)
      primary: true
    latitude: 'float(10,6)'
    longitude: 'float(10,6)'
    city: string(50)
    state: string(50)
    country: string(50)
    type: string(50)
  relations:
    Customer:
      type: many
      local: postalcode
      foreign: postalcode

Thank for any advice or information you can give me on this.

Best regards

Will Ferrer