mysql - 奇怪的 "all"在“哪里”

Question

这个问题出现在最新版本的MySQL中，所以我什至怀疑这可能是一个bug。

这里有两个表:

t1(id int), values (10),(2)
t2(id int), values (0),(null),(1)

执行:

select id from t1 where id > all (select id from t2);

返回结果集:

+------+
| id   |
+------+
|   10 |
|    2 |
+------+

据我所知和页面http://dev.mysql.com/doc/refman/5.5/en/all-subqueries.html

该语句应该返回空结果!因为“where”中的每一次判断都会导致null，像这样:

select id > all (select id from t2)  as c1 from t1;

返回:

+------+
| c1   |
+------+
| NULL |
| NULL |
+------+

实际上 select id from t1 where null; 什么都不返回!

最后，我尝试了这个:

explain extended select id from t1 where id > all (select id from t2);
show warnings;

+-------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                             |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t1`.`id` AS `id` from `test`.`t1` where <not>((`test`.`t1`.`id` <= (select max(`test`.`t2`.`id`) from `test`.`t2`))) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------+

集合中的 1 行(0.00 秒)

我们可以看到，MySQL将原来的SQL优化成了这条，实际上是符合结果集的。

但我认为优化后的 SQL 不等于原始 SQL。

我错了吗？

Answer 1

更新:进一步分析和展开MySQL的> ALL奇数实现。这个答案应该被认为是特定于 MySQL 的。因此，对于进一步的免责声明，此处关于 > ALL 的答案的解释不适用于其他 RDBMS(除非有其他 RDBMS 复制了 MySQL 实现)。从 > ALL 到 MAX 构造的内部转换，仅适用于 MySQL。

这个:

select id from t1 where id > all (select id from t2); 

在语义上等同于:

select id from t1 where id > (select max(id) from t2); 

由于 select max(id) from t2 返回 1，第二个查询具体化为:

select id from t1 where id > 1

这就是它从表 t1 返回 10 和 2 的原因

---

应用 NULL 规则的实例之一是当您使用 NOT IN 时，例如:

数据链接:

create table t1(id int);

insert into t1 values (10),(2);


create table t2(id int); 

insert into t2 values (0),(null),(1);

查询:

select * from t1 where id not in (select id from t2);

-- above is evaluated same as the following query, so the rules about null applies,
-- hence the above and following query will not return any record.    

select * from t1 where id <> 0 and id <> null and id <> 1;



-- to eliminate null side-effect, do this:
select * from t1 where id not in (select id from t2 where id is not null);

-- which is equivalent to this:
select * from t1 where id <> 0 and id <> 1;

最后两个查询返回 10 和 2，而前两个查询返回空集

现场测试:http://www.sqlfiddle.com/#!2/82865/1

希望这些示例消除您对 NULL 规则的困惑。

---

关于

but I not think the optimized sql equals the original one .

优化后的sql是这样的:

select `test`.`t1`.`id` AS `id` from `test`.`t1` where <not>((`
test`.`t1`.`id` <= (select max(`test`.`t2`.`id`) from `test`.`t2`)))

这实际上等同于您的原始查询:select id from t1 where id > all (select id from t2);

构造 t1.field > all (select t2.field from t2) 只是一个语法糖:

t1.field > (select max(t2.field) from t2)

如果您将在 MySql 优化的 SQL 上应用 DeMorgan 定理:

not (t1.id <= (select max(t2.id) from t2))

相当于:

t1.id > (select max(t2.id) from t2)

这又相当于语法糖ALL:

t1.id > ALL(select t2.id from t2)