Query Returns No Results Only When ORDER BY Added
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 | |
+----------------------------+--------------------------------------------------------------+------+-----+---------+----------------+
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:
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
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
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)
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).
检查你的服务器没有空间不足......是的,听起来很奇怪,但是所描述的行为可能是由于
链接地址: http://www.djcxy.com/p/74716.html上一篇: 捆绑罐:为他们创建捆绑?