我遇到了一个非常奇怪的问题，希望您能向我解释一下。 我想要做的是根据子查询中的合并列对结果集进行排序。让我解释得更好。

我有两个表:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `user_favorites_user` (
  `source_user_id` int(11) NOT NULL,
  `favorited_user_id` int(11) NOT NULL,
  KEY `source_user_id` (`source_user_id`),
  KEY `favorited_user_id` (`favorited_user_id`),
  CONSTRAINT `user_favorites_user_ibfk_1` FOREIGN KEY (`source_user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `user_favorites_user_ibfk_2` FOREIGN KEY (`favorited_user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

当一个用户(假设 ID=1)正在浏览该网站时，我想在底部向他显示使用他的收藏夹订购的其他用户。 所以，我从这个查询开始:

select user.*, coalesce(favorites.is_favorited,0) as is_favorited_coalesced from user
left join (
select 1 as is_favorited, favorited_user_id from user_favorites_user
where source_user_id = '1'
) favorites on favorites.favorited_user_id = user.id

到目前为止一切顺利，这就是我得到的和我期望的:

+----+-------+------------------------+
| id | name  | is_favorited_coalesced |
+----+-------+------------------------+
|  3 | user3 |                      1 |
|  4 | user4 |                      1 |
|  1 | user1 |                      0 |
|  2 | user2 |                      0 |
+----+-------+------------------------+
4 rows in set (0.00 sec)

现在，我想对结果集进行排序。我认为 ORDER BY 子句可能就足够了:

select user.*, coalesce(favorites.is_favorited,0) as is_favorited_coalesced from user
left join (
select 1 as is_favorited, favorited_user_id from user_favorites_user
where source_user_id = '1'
) favorites on favorites.favorited_user_id = user.id
order by is_favorited_coalesced asc

此时，我得到与上面相同的结果:

+----+-------+------------------------+
| id | name  | is_favorited_coalesced |
+----+-------+------------------------+
|  3 | user3 |                      1 |
|  4 | user4 |                      1 |
|  1 | user1 |                      0 |
|  2 | user2 |                      0 |
+----+-------+------------------------+
4 rows in set (0.00 sec)

然后我认为合并不适合即时排序，所以我添加了一个包装器查询，但结果仍然相同。

为什么 ORDER BY is_favorited_coalesced 不起作用？我在这里缺少什么？

编辑: 我尝试使用:

order by coalesce(favorites.is_favorited,0) asc

而不是别名，但我得到了相同的结果:

    select user.*, coalesce(favorites.is_favorited,0) as is_favorited_coalesced from user left join ( select 1 as is_favorited, favorited_user_id from user_favorites_user where source_user_id = '1' ) favorites on favorites.favorited_user_id = user.id order by coalesce(favorites.is_favorited,0)
--------------

+----+-------+------------------------+
| id | name  | is_favorited_coalesced |
+----+-------+------------------------+
|  3 | user3 |                      1 |
|  4 | user4 |                      1 |
|  1 | user1 |                      0 |
|  2 | user2 |                      0 |
+----+-------+------------------------+
4 rows in set (0.00 sec)

编辑 2 我发现了另一个奇怪的行为。如果我尝试按 ID 列排序，这就是我得到的:

--------------
select user.*, coalesce(favorites.is_favorited,0) as is_favorited_coalesced from user left join ( select 1 as is_favorited, favorited_user_id from user_favorites_user where source_user_id = '1' ) favorites on favorites.favorited_user_id = user.id order by id asc
--------------

+----+-------+------------------------+
| id | name  | is_favorited_coalesced |
+----+-------+------------------------+
|  1 | user1 |                      1 |
|  2 | user2 |                      1 |
|  3 | user3 |                      1 |
|  4 | user4 |                      1 |
+----+-------+------------------------+
4 rows in set (0.00 sec)

我不知道为什么会这样。 我在使用 VirtualBox 的 Windows 下的虚拟化 Fedora 25 上使用 MySQL 5.7.20。

编辑 3

正如我运行的评论中所建议的那样:

mysql> explain select user.*, coalesce(favorites.is_favorited,0) as is_favorited_coalesced from user left join ( select 1 as is_favorited, favorited_user_id from user_favorites_user where source_user_id = '1' ) favorites on favorites.favorited_user_id = user.id order by is_favorited_coalesced asc;show warnings;
+----+-------------+---------------------+------------+-------+----------------------------------+----------------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table               | partitions | type  | possible_keys                    | key            | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+---------------------+------------+-------+----------------------------------+----------------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | user                | NULL       | ALL   | NULL                             | NULL           | NULL    | NULL |    4 |   100.00 | NULL                                               |
|  1 | SIMPLE      | user_favorites_user | NULL       | range | source_user_id,favorited_user_id | source_user_id | 4       | NULL |    2 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+---------------------+------------+-------+----------------------------------+----------------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                      |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `so_test`.`user`.`id` AS `id`,`so_test`.`user`.`name` AS `name`,coalesce(1,0) AS `is_favorited_coalesced` from `so_test`.`user` left join (`so_test`.`user_favorites_user`) on(((`so_test`.`user_favorites_user`.`favorited_user_id` = `so_test`.`user`.`id`) and (`so_test`.`user_favorites_user`.`source_user_id` = '1'))) where 1 order by `is_favorited_coalesced` |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

还有:

mysql> SELECT @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

编辑 4:

我跑过:

mysql> SELECT @@optimizer_switch;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@optimizer_switch                                                                                                                                                                                                                                                                                                                                                                                               |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如评论中所述。

包括用于快速测试的数据集:

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `user` (`id`, `name`) VALUES
(1, 'user1'),
(2, 'user2'),
(3, 'user3'),
(4, 'user4');

CREATE TABLE `user_favorites_user` (
  `source_user_id` int(11) NOT NULL,
  `favorited_user_id` int(11) NOT NULL,
  KEY `source_user_id` (`source_user_id`),
  KEY `favorited_user_id` (`favorited_user_id`),
  CONSTRAINT `user_favorites_user_ibfk_1` FOREIGN KEY (`source_user_id`) REFERENCES `user` (`id`),
  CONSTRAINT `user_favorites_user_ibfk_2` FOREIGN KEY (`favorited_user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `user_favorites_user` (`source_user_id`, `favorited_user_id`) VALUES
(1, 3),
(1, 4);

最佳答案

这是错误 Query returns wrong data if order by is present (或至少密切相关)。

它(以非常相似的形式)仍然存在于 MySQL 8.0.12 中(参见例如 your example 在 dbfiddle 中，尽管它希望在修复后不会显示不正确的行为):虽然它现在实际上排序正确(可能是因为您对其进行了计算)，它仍然为 is_favorited 返回不正确的值:

select user.*, favorites.is_favorited, 
coalesce(favorites.is_favorited,0) as is_favorited_coalesced from user
left join (
select 1 as is_favorited, favorited_user_id from user_favorites_user
where source_user_id = '1'
) favorites on favorites.favorited_user_id = user.id
order by is_favorited_coalesced desc

+----+-------+--------------+------------------------+
| id | name  | is_favorited | is_favorited_coalesced |
+----+-------+--------------+------------------------+
|  1 | user1 |              |                      1 |
|  2 | user2 |              |                      1 |
|  3 | user3 |              |                      0 |
|  4 | user4 |              |                      0 |
+----+-------+--------------+------------------------+

这似乎是与(非)实现相关的优化器问题(MySQL 5.7 有很多)。您可以通过强制实现派生表(例如，通过添加 limit)来解决大部分错误:

select user.*, favorites.is_favorited, 
coalesce(favorites.is_favorited,0) as is_favorited_coalesced from user
left join (
select 1 as is_favorited, favorited_user_id from user_favorites_user
where source_user_id = '1' limit 1000000
) favorites on favorites.favorited_user_id = user.id
order by is_favorited_coalesced desc

+----+-------+--------------+------------------------+
| id | name  | is_favorited | is_favorited_coalesced |
+----+-------+--------------+------------------------+
|  1 | user1 |            1 |                      1 |
|  2 | user2 |            1 |                      1 |
|  3 | user3 |              |                      0 |
|  4 | user4 |              |                      0 |
+----+-------+--------------+------------------------+

正如@RaymondNijland 提到的，还有其他解决方法，例如使用 set [GLOBAL|SESSION] optimizer_switch='derived_merge=off' 禁用派生表合并在运行该查询之前。您还可以使用它来全局禁用该功能，直到错误得到修复，这样您就不必检查每个查询是否损坏，只需为您已确认它们不受影响的查询启用它(这样他们就可以从中获利)再次优化)。

关于MYSQL 5.7.20 - 按合并列顺序左连接 - 非常奇怪的行为，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/52273770/