The request is returned only if ORDER BY

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 created the following query to select the most recent stage, grouped by a 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); 

Works well if I try to execute ORDER BY 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.

Can someone give some idea please?

Thanks!

EDIT: people structure

 +----------------------------+--------------------------------------------------------------+------+-----+---------+----------------+ | 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 seems to be a bug in MySQL that I wrote a report about . I narrowed it down to the following test case, which you would expect to return a single record (but this is not the case):

 CREATE TABLE t (x INT NULL); -- table with nullable column INSERT INTO t VALUES (0); -- but non null data SELECT ax -- select our nullable column FROM ta, (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 ax 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 

Take a look at sqlfiddle .

In your case, you can do several things to fix this:

• Avoid the correlated subquery by rewriting it 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 

• If you want to keep a correlated subquery (which can usually give 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 

• If you cannot modify the query, you should find that when creating the people_stages.person_id non-nullable column, a problem will occur:

   ALTER TABLE people_stages MODIFY person_id BIGINT UNSIGNED NOT NULL 

  It seems that having an index in this column (which is required to restrict the foreign key) can also help:

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

• Alternatively, you can remove people_stages.person_id from the selection list or configure the data / index / query model strategy to avoid file management (in this case, this may be impractical, but I mention them here for completeness).

Answer 2

Make sure your server is running out of space ... yes, it sounds weird, but behavior like the one described may be caused by