怪异的“所有"在“哪里" [英] Weird "all" in"where"

问题描述

该问题存在于MySQL的最新版本中，因此我什至怀疑这可能是一个错误.

The problem exists in MySQL's newest version, so I even doubt that that may be a 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，就像这样:

The statement should return empty result! Because each judgement in "where" leads to null, like this:

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进行了优化，实际上符合结果集.

We can see that MySQL optimizes the original SQL to this one, which actually fits the result set.

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

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

我错了吗?

推荐答案

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

Update: Upon further analysis and unfolding of MySQL's > ALL odd implementation. This answer should be considered as MySQL-specific. So for further disclaimer, explanation on answer here regarding > ALL is not applicable to other RDBMSes (unless there are other RDBMSes that copied MySQL implementation). Internal translation from > ALL to a MAX construct, applies to MySQL only.

此:

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，因此第二个查询实现了:

Since select max(id) from t2 returns 1, the second query materializes to this:

select id from t1 where id > 1

这就是为什么它从表t1中同时返回10和2

That's why it returns both 10 and 2 from table t1

其中一个应用空规则的实例是当您使用NOT IN时，例如:

One of the instances where NULL rules is being applied is when you use NOT IN, an example:

DDL:

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，而前两个查询返回空集

The last two queries returns 10 and 2, whereas the first two queries returns empty set

实时测试: http://www.sqlfiddle.com/#!2/82865/1

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

Hope these examples erases your confusion with NULL rules.

关于

但是我不认为优化的sql等于原始的sql.

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

经过优化的sql是这样的:

Optimized sql being this:

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

That is really equivalent to your original query: select id from t1 where id > all (select id from t2);

构造t1.field > all (select t2.field from t2)只是以下方面的语法糖:

The construct t1.field > all (select t2.field from t2) is just a syntactic sugar for:

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

如果要通过MySql在优化的SQL上应用DeMorgan定理:

If you will apply DeMorgan theorem on the optimized SQL by MySql:

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)

这篇关于怪异的“所有"在“哪里"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！