MYSQL 5.7.20 - Left join with order by a coalesced column - very strange behavior

user_1732308 0
Viewed 160

I'm facing a very strange issue that I hope you can explain to me. What I'm trying to do is order a resultset based on a coalesced column in a subquery. Let me explain better.

I have two tables:

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;

As one user (let's say ID=1) is browsing the website, I would like to show him other users ordered with his favorites at the bottom. So, I start with this query:

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

So far so good, this is what I get and I expected:

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

Now, I want to order the resultset. I thought that an ORDER BY clause may suffice:

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

At this point, I get the same result as above:

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

I then thought the coalesce is not good for sorting on-the-fly, so I added a wrapper query but the result is still the same.

Why the ORDER BY is_favorited_coalesced is not working? What am I missing here?

EDIT: I tried using:

order by coalesce(favorites.is_favorited,0) asc

instead of the alias but I got the same results:

    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)

EDIT 2 I've found another strange behavior. If I try to order by the ID column, this is what I get:

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

I have no clue why this is happening. I'm using MySQL 5.7.20 on a virtualized Fedora 25 under windows using VirtualBox.

EDIT 3

As suggested in the comments I've run:

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)

Also:

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)

EDIT 4:

I've run:

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)

As stated in the comments.

Including the dataset for fast testing:

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);
mysql-5.7 coalesce left-join join mysql
1 Answers

This is the bug Query returns wrong data if order by is present (or at least closely related).

It is (in a very similar form) still present in MySQL 8.0.12 (see e.g. your example in dbfiddle, although it will hopefully not show the incorrect behaviour once it is fixed): while it actually orders correctly now (probably because you do a calculation on it), it still returns the incorrect value for 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 |
+----+-------+--------------+------------------------+

It seems to be an optimizer problem related to (non-)materialization (MySQL 5.7 had a lot of those). You can workaround most of those bugs by forcing materialization of the derived table (e.g. by adding a 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 |
+----+-------+--------------+------------------------+

As @RaymondNijland mentioned, there are other workarounds, e.g. disabling Derived Table Merging with set [GLOBAL|SESSION] optimizer_switch='derived_merge=off' before you run that query. You could also use this to disable the feature globally until the bug gets fixed, so you do not have to inspect every query if it's broken, and just enable it for queries you have verfied that they are not affected (so they can profit from that optimization again).

user_62485280
Related