This project is archived and is in readonly mode.

#5468 ✓invalid
James Kebinger

Invalid SQL in AREL readme

Reported by James Kebinger | August 26th, 2010 @ 04:59 PM

The SQL in the Crazy Features > Complex Joins section is invalid. This is an interesting problem, because the invalid SQL builds towards an incorrect conclusion that a subselect is needed in the case described. Seems like more than a simple doc-patch.

First the SQL to do photo counts by user has a misplaced limit 3 so it doesn't run at all.

select users.*, count(photos.id) from users left outer join photos on users.id = photos.user_id limit 3 group by user_id;

should be

mysql> select users.*, count(photos.id) from users left outer join photos on users.id = photos.user_id group by user_id;
+----+------+------------------+
| id | name | count(photos.id) | 
+----+------+------------------+ 
| 2  | bai  | 0                |
| 1  | hai  | 3                | 
+----+------+------------------+
 2 rows in set (0.00 sec)

So that supports the example, but the group by can be better specified as group by users.id instead of user_id, which produces the correct results:

mysql> select users.*, count(photos.id) from users left outer join photos on users.id = photos.user_id group by users.id;
+----+--------+------------------+
| id | name   | count(photos.id) |
+----+--------+------------------+
| 1  | hai    | 3                |
| 2  | bai    | 0                |
| 3  | dumpty | 0                | 
+----+--------+------------------+
 3 rows in set (0.00 sec)

Interestingly, the first form of the group by clause is rejected by Postgres, which appears to restrict joins to columns in the result set.

Edited by Rohit Arondekar for formating.

Comments and changes to this ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Pages