Query Returns No Results Only When ORDER BY Added

Question

I have a table with the following schema:

people_stages

id    |    person_id    |   stage_id   |   created
1     |    1            |   1          |   2013-09-01 00:00:00
2     |    1            |   2          |   2013-09-02 00:00:00
3     |    1            |   3          |   2013-09-03 00:00:00

I have created the following query to select the most recent stage grouped by person:

SELECT * 
FROM people Person
LEFT JOIN people_stages PersonStage ON PersonStage.person_id = Person.id
WHERE PersonStage.created = (SELECT MAX(people_stages.created) FROM people_stages GROUP BY person_id HAVING person_id = PersonStage.person_id);

It works fine, however, if I try to ORDER BY a field in the Person table:

SELECT * 
FROM people Person
LEFT JOIN people_stages PersonStage ON PersonStage.person_id = Person.id
WHERE PersonStage.created = (SELECT MAX(people_stages.created) FROM people_stages GROUP BY person_id HAVING person_id = PersonStage.person_id)
ORDER BY Person.last_name;

It returns 0 results.

Could anyone provide some insight, please?

Thanks!

EDIT: Structure of people

+----------------------------+--------------------------------------------------------------+------+-----+---------+----------------+
| Field                      | Type                                                         | Null | Key | Default | Extra          |
+----------------------------+--------------------------------------------------------------+------+-----+---------+----------------+
| id                         | bigint(20)                                                   | NO   | PRI | NULL    | auto_increment |
| internal_id                | varchar(50)                                                  | NO   | MUL | NULL    |                |
| public_id                  | varchar(30)                                                  | NO   |     | NULL    |                |
| counselor_id               | bigint(20)                                                   | NO   |     | NULL    |                |
| term_id                    | int(11)                                                      | NO   | MUL | NULL    |                |
| program_id                 | int(11)                                                      | NO   |     | NULL    |                |
| person_type_id             | int(11)                                                      | NO   | MUL | NULL    |                |
| first_name                 | varchar(100)                                                 | NO   |     | NULL    |                |
| middle_name                | varchar(100)                                                 | NO   |     | NULL    |                |
| last_name                  | varchar(100)                                                 | NO   |     | NULL    |                |
| photo_url                  | varchar(255)                                                 | NO   |     | NULL    |                |
| gender                     | enum('m','f','u')                                            | NO   |     | NULL    |                |
| date_of_birth              | date                                                         | NO   |     | NULL    |                |
| address                    | varchar(255)                                                 | NO   |     | NULL    |                |
| address_apt                | varchar(100)                                                 | NO   |     | NULL    |                |
| address_city               | varchar(100)                                                 | NO   |     | NULL    |                |
| address_state              | varchar(100)                                                 | NO   |     | NULL    |                |
| address_state_intl         | varchar(255)                                                 | NO   |     | NULL    |                |
| address_zip                | varchar(25)                                                  | NO   |     | NULL    |                |
| address_country            | varchar(100)                                                 | NO   |     | NULL    |                |
| address_verified           | tinyint(1)                                                   | NO   |     | NULL    |                |
| address_latitude           | varchar(100)                                                 | NO   |     | NULL    |                |
| address_longitude          | varchar(100)                                                 | NO   |     | NULL    |                |
| address_position           | point                                                        | NO   | MUL | NULL    |                |
| address_distance           | smallint(6)                                                  | NO   |     | NULL    |                |
| social_facebook            | mediumtext                                                   | NO   |     | NULL    |                |
| social_twitter             | varchar(255)                                                 | NO   |     | NULL    |                |
| social_instagram           | varchar(255)                                                 | NO   |     | NULL    |                |
| phone_cell                 | varchar(25)                                                  | NO   |     | NULL    |                |
| phone_cell_clean           | varchar(25)                                                  | YES  |     | NULL    |                |
| phone_work                 | varchar(25)                                                  | NO   |     | NULL    |                |
| phone_work_clean           | varchar(25)                                                  | NO   |     | NULL    |                |
| permission_to_text         | tinyint(1)                                                   | NO   |     | NULL    |                |
| permission_to_text_confirm | tinyint(1)                                                   | NO   |     | NULL    |                |
| phone_home                 | varchar(25)                                                  | NO   |     | NULL    |                |
| phone_home_clean           | varchar(25)                                                  | YES  |     | NULL    |                |
| email_address              | varchar(255)                                                 | NO   |     | NULL    |                |
| permission_to_email        | tinyint(1)                                                   | NO   |     | NULL    |                |
| preferred_contact          | enum('phone_home','phone_cell','text_cell','email','postal') | NO   |     | NULL    |                |
| parent_first_name          | varchar(100)                                                 | NO   |     | NULL    |                |
| parent_last_name           | varchar(100)                                                 | NO   |     | NULL    |                |
| parent_email               | varchar(255)                                                 | NO   |     | NULL    |                |
| hs_name                    | varchar(255)                                                 | NO   |     | NULL    |                |
| hs_homeschooled            | tinyint(1)                                                   | NO   |     | NULL    |                |
| hs_ceeb_id                 | varchar(100)                                                 | NO   |     | NULL    |                |
| hs_grad_year               | varchar(4)                                                   | NO   |     | NULL    |                |
| coll_name                  | varchar(255)                                                 | NO   |     | NULL    |                |
| coll_ceeb_id               | varchar(100)                                                 | NO   |     | NULL    |                |
| coll_major                 | varchar(255)                                                 | NO   |     | NULL    |                |
| coll_year                  | varchar(20)                                                  | NO   |     | NULL    |                |
| counselor_read             | tinyint(1)                                                   | NO   |     | NULL    |                |
| source                     | varchar(100)                                                 | NO   |     | NULL    |                |
| entry_method               | varchar(100)                                                 | NO   |     | NULL    |                |
| erp_processed              | tinyint(1)                                                   | NO   |     | NULL    |                |
| created                    | datetime                                                     | NO   |     | NULL    |                |
| modified                   | datetime                                                     | NO   |     | NULL    |                |
+----------------------------+--------------------------------------------------------------+------+-----+---------+----------------+

Answer 1

This appears to be a bug in MySQL, about which I have filed a report. I have narrowed it to the following test case, which one would expect to return a single record (but it does not):

CREATE TABLE t (x INT NULL);  -- table with nullable column
INSERT INTO  t VALUES (0);    -- but non null data

SELECT   a.x                  -- select our nullable column
FROM     t a, (SELECT NULL) b -- joining it with anything at all

WHERE    EXISTS (             -- but filter on a subquery
           SELECT *
           FROM   (SELECT NULL) c -- doesn't really matter what
           HAVING a.x IS NOT NULL -- provided there is some correlated condition
                                  -- on our nullable column in the HAVING clause
         )

ORDER BY RAND()               -- then perform a filesort on the outer query

See it on sqlfiddle.

In your case, you can do a number of things to fix this:

1. Avoid the correlated subquery by rewriting as a join:

   SELECT   *
   FROM     people AS p LEFT JOIN (people_stages AS s NATURAL JOIN (
              SELECT   person_id, MAX(created) created
              FROM     people_stages
              GROUP BY person_id
            ) t) ON s.person_id = p.id
   ORDER BY p.last_name
   

2. If you want to keep the correlated subquery (which can generally yield poor performance but is often easier to understand), use WHERE instead of HAVING:

   SELECT   * 
   FROM     people AS p LEFT JOIN people_stages AS s ON s.person_id = p.id
   WHERE    s.created = (
              SELECT MAX(created)
              FROM   people_stages
              WHERE  person_id = s.person_id
            )
   ORDER BY p.last_name
   

3. If you're unable to change the query, you should find that making the people_stages.person_id column non-nullable will get around the problem:

   ALTER TABLE people_stages MODIFY person_id BIGINT UNSIGNED NOT NULL
   

   It seems that having an index on that column (which would be required to effect a foreign key constraint) may also help:

   ALTER TABLE people_stages ADD FOREIGN KEY (person_id) REFERENCES people (id)
   

4. Alternatively one could remove people_stages.person_id from the select list, or adjust the data model/indexing/query strategy to avoid a filesort (may not be practical in this case, but I mention them here for completeness).