mysql - 仅在添加 ORDER BY 时查询不返回任何结果

Question

我有一个具有以下架构的表:

人物阶段

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

我创建了以下查询来选择按人员分组的最新阶段:

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);

但是，如果我尝试对 Person 表中的字段进行 ORDER BY，它工作正常:

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;

它返回 0 个结果。

有人能提供一些见解吗？

谢谢!

编辑:人的结构

+----------------------------+--------------------------------------------------------------+------+-----+---------+----------------+
| 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

这似乎是 MySQL 中的一个错误，关于它我有 filed a report .我已将其缩小到以下测试用例，它会返回一条记录(但它没有):

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

查看 sqlfiddle .

在你的情况下，你可以做很多事情来解决这个问题:

1. 通过重写为连接来避免相关子查询:

   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. 如果您想保留相关的子查询(这通常会产生较差的性能但通常更容易理解)，请使用 WHERE 而不是 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. 如果您无法更改查询，您会发现将 people_stages.person_id 列设置为不可空将解决此问题:

   ALTER TABLE people_stages MODIFY person_id BIGINT UNSIGNED NOT NULL
   

   似乎在该列上有一个索引(这将需要影响外键约束)也可能有帮助:

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

4. 或者，可以从选择列表中删除 people_stages.person_id，或者调整数据模型/索引/查询策略以避免文件排序(在这种情况下可能不切实际，但我为了完整起见在这里提及它们)。