mysql - LEFT JOIN 和 ORDER BY 奇怪的错误

Question

当我将 MSQL 从 5 升级到 6 时出现了一个奇怪的错误我的脚本创建数据库

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `table1` (`id`, `value`) VALUES 
  (1, 100),
  (2, 200),
  (3, 300);
CREATE TABLE `table1_use` (
  `id` int(11) NOT NULL,
  `table1id` int(11) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `table1_use` (`id`, `table1id`, `uid`) VALUES 
  (1, 1, 99);

我有两个查询(其中一个有排序依据)

select
      temp.sel
from
    table1 t1
    left join (
         select *,1 as sel from table1_use t1u where t1u.`table1id`=1
    ) temp on temp.table1id = t1.id
order by t1.value
------
select
      temp.sel
from
    table1 t1
    left join (
         select *,1 as sel from table1_use t1u where t1u.`table1id`=1
    ) temp on temp.table1id = t1.id

为什么在 2 个查询中结果不同 (MYSQL 6.xx)SQL 1 的结果

sel
1
1
1

SQL 2 的结果

sel
1
null
null

Answer 1

这似乎与优化器行为有关。参见 9.9.2 Controlling Switchable Optimizations .

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.17    |
+-----------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `table1_use`, `table1`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `table1` (
    ->   `id` int(11) NOT NULL,
    ->   `value` int(11) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `table1` (`id`, `value`) VALUES 
    ->   (1, 100),
    ->   (2, 200),
    ->   (3, 300);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE IF NOT EXISTS `table1_use` (
    ->   `id` int(11) NOT NULL,
    ->   `table1id` int(11) DEFAULT NULL,
    ->   `uid` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `table1_use` (`id`, `table1id`, `uid`) VALUES 
    ->   (1, 1, 99);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@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)

mysql> SELECT 
    ->   `temp`.`sel`
    -> FROM
    ->   `table1` `t1`
    ->   LEFT JOIN (
    ->     SELECT *, 1 `sel` FROM `table1_use` `t1u` WHERE `t1u`.`table1id` = 1
    ->   ) `temp` ON `temp`.`table1id` = `t1`.`id`
    -> ORDER BY `t1`.`value`;
+------+
| sel  |
+------+
|    1 |
|    1 |
|    1 |
+------+
3 rows in set (0.00 sec)

mysql> SELECT
    ->   `temp`.`sel`
    -> FROM
    ->   `table1` `t1`
    ->   LEFT JOIN (
    ->     SELECT *, 1 `sel` FROM `table1_use` `t1u` WHERE `t1u`.`table1id` = 1
    ->   ) `temp` ON `temp`.`table1id` = `t1`.`id`;
+------+
| sel  |
+------+
|    1 |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)

mysql> SET optimizer_switch='block_nested_loop=off';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@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=off,
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)

mysql> SELECT 
    ->   `temp`.`sel`
    -> FROM
    ->   `table1` `t1`
    ->   LEFT JOIN (
    ->     SELECT *, 1 `sel` FROM `table1_use` `t1u` WHERE `t1u`.`table1id` = 1
    ->   ) `temp` ON `temp`.`table1id` = `t1`.`id`
    -> ORDER BY `t1`.`value`;
+------+
| sel  |
+------+
|    1 |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)

mysql> SELECT
    ->   `temp`.`sel`
    -> FROM
    ->   `table1` `t1`
    ->   LEFT JOIN (
    ->     SELECT *, 1 `sel` FROM `table1_use` `t1u` WHERE `t1u`.`table1id` = 1
    ->   ) `temp` ON `temp`.`table1id` = `t1`.`id`;
+------+
| sel  |
+------+
|    1 |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)

来自 MySQL 5.7.7，参见 9.9.3 Optimizer Hints .

mysql> DROP TABLE IF EXISTS `table1_use`, `table1`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `table1` (
    ->   `id` int(11) NOT NULL,
    ->   `value` int(11) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `table1` (`id`, `value`) VALUES 
    ->   (1, 100),
    ->   (2, 200),
    ->   (3, 300);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE IF NOT EXISTS `table1_use` (
    ->   `id` int(11) NOT NULL,
    ->   `table1id` int(11) DEFAULT NULL,
    ->   `uid` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `table1_use` (`id`, `table1id`, `uid`) VALUES 
    ->   (1, 1, 99);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@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)

mysql> SELECT
    ->   /*+ NO_BNL(@`subquery`) */ `temp`.`sel`
    -> FROM
    ->   `table1` `t1`
    ->   LEFT JOIN (
    ->     SELECT /*+ QB_NAME(`subquery`) */ *, 1 `sel`
    ->     FROM `table1_use` `t1u`
    ->     WHERE `t1u`.`table1id` = 1
    ->   ) `temp` ON `temp`.`table1id` = `t1`.`id`
    -> ORDER BY `t1`.`value`;
+------+
| sel  |
+------+
|    1 |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)

MariaDB 10.1.21，参见 optimizer_switch和 RESET .

MariaDB [_]> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 10.1.21-MariaDB |
+-----------------+
1 row in set (0.00 sec)

MariaDB [_]> SELECT 
    ->   `temp`.`sel`
    -> FROM
    ->   `table1` `t1`
    ->   LEFT JOIN (
    ->     SELECT *, 1 `sel` FROM `table1_use` `t1u` WHERE `t1u`.`table1id` = 1
    ->   ) `temp` ON `temp`.`table1id` = `t1`.`id`
    -> ORDER BY `t1`.`value`;
+------+
| sel  |
+------+
| NULL |
| NULL |
| NULL |
+------+
3 rows in set (0.00 sec)

MariaDB [_]> SELECT 
    ->   `temp`.`sel`
    -> FROM
    ->   `table1` `t1`
    ->   LEFT JOIN (
    ->     SELECT *, 1 `sel` FROM `table1_use` `t1u` WHERE `t1u`.`table1id` = 1
    ->   ) `temp` ON `temp`.`table1id` = `t1`.`id`;
+-----+
| sel |
+-----+
|   1 |
| NULL |
| NULL |
+-----+
3 rows in set (0.00 sec)

MariaDB [_]> SET optimizer_switch='outer_join_with_cache=off';
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> SELECT 
    ->   `temp`.`sel`
    -> FROM
    ->   `table1` `t1`
    ->   LEFT JOIN (
    ->     SELECT *, 1 `sel` FROM `table1_use` `t1u` WHERE `t1u`.`table1id` = 1
    ->   ) `temp` ON `temp`.`table1id` = `t1`.`id`
    -> ORDER BY `t1`.`value`;
+-----+
| sel |
+-----+
|   1 |
| NULL |
| NULL |
+-----+
3 rows in set (0.00 sec)

MariaDB [_]> SELECT 
    ->   `temp`.`sel`
    -> FROM
    ->   `table1` `t1`
    ->   LEFT JOIN (
    ->     SELECT *, 1 `sel` FROM `table1_use` `t1u` WHERE `t1u`.`table1id` = 1
    ->   ) `temp` ON `temp`.`table1id` = `t1`.`id`;
+-----+
| sel |
+-----+
|   1 |
| NULL |
| NULL |
+-----+
3 rows in set (0.00 sec)