按合并列顺序左连接-非常奇怪的行为

5w9g7ksd  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(418)

我正面临一个非常奇怪的问题，希望你能解释给我听。我要做的是根据子查询中的合并列对结果集进行排序。让我解释清楚。
我有两张table：

  1. CREATE TABLE `user` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  4.   PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  7. CREATE TABLE `user_favorites_user` (
  8.   `source_user_id` int(11) NOT NULL,
  9.   `favorited_user_id` int(11) NOT NULL,
  10.   KEY `source_user_id` (`source_user_id`),
  11.   KEY `favorited_user_id` (`favorited_user_id`),
  12.   CONSTRAINT `user_favorites_user_ibfk_1` FOREIGN KEY (`source_user_id`) REFERENCES `user` (`id`),
  13.   CONSTRAINT `user_favorites_user_ibfk_2` FOREIGN KEY (`favorited_user_id`) REFERENCES `user` (`id`)
  14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

由于一个用户（假设id=1）正在浏览该网站，我想向他展示其他用户的最爱与他的底部订购。所以，我从这个问题开始：

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

到目前为止还不错，这是我得到的和我期望的：

  1. +----+-------+------------------------+
  2. | id | name  | is_favorited_coalesced |
  3. +----+-------+------------------------+
  4. |  3 | user3 |                      1 |
  5. |  4 | user4 |                      1 |
  6. |  1 | user1 |                      0 |
  7. |  2 | user2 |                      0 |
  8. +----+-------+------------------------+
  9. 4 rows in set (0.00 sec)

现在，我想订购结果集。我认为按条款订货就足够了：

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

在这一点上，我得到了与上面相同的结果：

  1. +----+-------+------------------------+
  2. | id | name  | is_favorited_coalesced |
  3. +----+-------+------------------------+
  4. |  3 | user3 |                      1 |
  5. |  4 | user4 |                      1 |
  6. |  1 | user1 |                      0 |
  7. |  2 | user2 |                      0 |
  8. +----+-------+------------------------+
  9. 4 rows in set (0.00 sec)

然后我认为coalesce不适合动态排序，所以我添加了一个 Package 器查询，但结果仍然是一样的。
为什么order by\u favorited\u Coalsed不起作用？我错过了什么？
编辑：我尝试使用：

  1. order by coalesce(favorites.is_favorited,0) asc

但我得到了相同的结果：

  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 order by coalesce(favorites.is_favorited,0)
  2. --------------
  4. +----+-------+------------------------+
  5. | id | name  | is_favorited_coalesced |
  6. +----+-------+------------------------+
  7. |  3 | user3 |                      1 |
  8. |  4 | user4 |                      1 |
  9. |  1 | user1 |                      0 |
  10. |  2 | user2 |                      0 |
  11. +----+-------+------------------------+
  12. 4 rows in set (0.00 sec)

我发现了另一个奇怪的行为。如果我尝试按id列排序，我得到的结果如下：

  1. --------------
  2. 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
  3. --------------
  5. +----+-------+------------------------+
  6. | id | name  | is_favorited_coalesced |
  7. +----+-------+------------------------+
  8. |  1 | user1 |                      1 |
  9. |  2 | user2 |                      1 |
  10. |  3 | user3 |                      1 |
  11. |  4 | user4 |                      1 |
  12. +----+-------+------------------------+
  13. 4 rows in set (0.00 sec)

我不知道为什么会这样。我在使用virtualbox的windows下的虚拟fedora25上使用mysql 5.7.20。
编辑3
正如我在评论中所说：

  1. 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;
  2. +----+-------------+---------------------+------------+-------+----------------------------------+----------------+---------+------+------+----------+----------------------------------------------------+
  3. | id | select_type | table               | partitions | type  | possible_keys                    | key            | key_len | ref  | rows | filtered | Extra                                              |
  4. +----+-------------+---------------------+------------+-------+----------------------------------+----------------+---------+------+------+----------+----------------------------------------------------+
  5. |  1 | SIMPLE      | user                | NULL       | ALL   | NULL                             | NULL           | NULL    | NULL |    4 |   100.00 | NULL                                               |
  6. |  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) |
  7. +----+-------------+---------------------+------------+-------+----------------------------------+----------------+---------+------+------+----------+----------------------------------------------------+
  8. 2 rows in set, 1 warning (0.00 sec)
  10. +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. | Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                      |
  12. +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  13. | 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` |
  14. +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  15. 1 row in set (0.00 sec)

也：

  1. mysql> SELECT @@sql_mode;
  2. +-------------------------------------------------------------------------------------------------------------------------------------------+
  3. | @@sql_mode                                                                                                                                |
  4. +-------------------------------------------------------------------------------------------------------------------------------------------+
  5. | 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 |
  6. +-------------------------------------------------------------------------------------------------------------------------------------------+
  7. 1 row in set (0.00 sec)

编辑4：
我跑过：

  1. mysql> SELECT @@optimizer_switch;
  2. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | @@optimizer_switch                                                                                                                                                                                                                                                                                                                                                                                               |
  4. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | 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 |
  6. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  7. 1 row in set (0.00 sec)

如评论所述。
包括用于快速测试的数据集：

  1. SET NAMES utf8;
  2. SET time_zone = '+00:00';
  3. SET foreign_key_checks = 0;
  4. SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
  6. CREATE TABLE `user` (
  7.   `id` int(11) NOT NULL AUTO_INCREMENT,
  8.   `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  9.   PRIMARY KEY (`id`)
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  12. INSERT INTO `user` (`id`, `name`) VALUES
  13. (1, 'user1'),
  14. (2, 'user2'),
  15. (3, 'user3'),
  16. (4, 'user4');
  18. CREATE TABLE `user_favorites_user` (
  19.   `source_user_id` int(11) NOT NULL,
  20.   `favorited_user_id` int(11) NOT NULL,
  21.   KEY `source_user_id` (`source_user_id`),
  22.   KEY `favorited_user_id` (`favorited_user_id`),
  23.   CONSTRAINT `user_favorites_user_ibfk_1` FOREIGN KEY (`source_user_id`) REFERENCES `user` (`id`),
  24.   CONSTRAINT `user_favorites_user_ibfk_2` FOREIGN KEY (`favorited_user_id`) REFERENCES `user` (`id`)
  25. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  27. INSERT INTO `user_favorites_user` (`source_user_id`, `favorited_user_id`) VALUES
  28. (1, 3),
  29. (1, 4);
mysqlcoalesceJoinleft-joinmysql-5.7

1条答案

按热度按时间
r7s23pms

r7s23pms1#

如果存在orderby（或者至少是密切相关的），那么这个bug查询将返回错误的数据。
它（以非常相似的形式）仍然存在于mysql 8.0.12中（例如，请参阅dbfiddle中的示例，尽管它在修复后不会显示错误的行为）：虽然它现在实际上正确地排序（可能是因为您对它进行了计算），但它仍然返回错误的值 is_favorited :

  1. select user.*, favorites.is_favorited, 
  2. coalesce(favorites.is_favorited,0) as is_favorited_coalesced from user
  3. left join (
  4. select 1 as is_favorited, favorited_user_id from user_favorites_user
  5. where source_user_id = '1'
  6. ) favorites on favorites.favorited_user_id = user.id
  7. order by is_favorited_coalesced desc
  9. +----+-------+--------------+------------------------+
  10. | id | name  | is_favorited | is_favorited_coalesced |
  11. +----+-------+--------------+------------------------+
  12. |  1 | user1 |              |                      1 |
  13. |  2 | user2 |              |                      1 |
  14. |  3 | user3 |              |                      0 |
  15. |  4 | user4 |              |                      0 |
  16. +----+-------+--------------+------------------------+

这似乎是一个与（非）物化相关的优化器问题（mysql 5.7有很多这样的问题）。您可以通过强制实现派生表（例如，通过添加 limit ):

  1. select user.*, favorites.is_favorited, 
  2. coalesce(favorites.is_favorited,0) as is_favorited_coalesced from user
  3. left join (
  4. select 1 as is_favorited, favorited_user_id from user_favorites_user
  5. where source_user_id = '1' limit 1000000
  6. ) favorites on favorites.favorited_user_id = user.id
  7. order by is_favorited_coalesced desc
  9. +----+-------+--------------+------------------------+
  10. | id | name  | is_favorited | is_favorited_coalesced |
  11. +----+-------+--------------+------------------------+
  12. |  1 | user1 |            1 |                      1 |
  13. |  2 | user2 |            1 |                      1 |
  14. |  3 | user3 |              |                      0 |
  15. |  4 | user4 |              |                      0 |
  16. +----+-------+--------------+------------------------+

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

展开查看全部
赞(0）分享  回复(0）举报  2021-06-21
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com