mysql 视图 条件语句 疑似 bug

[sycxyc]

什么情况下,下列语句会输出 t1_view 总行数?

select count(*) from t1_view where id not in (select id from t1_view);

以下为操作:

create table t1 (id int primary key);
create table t2 (id int primary key);
insert t1 values (1),(2),(3);
insert t2 values (1);
create view t1_view as select a.id from t1 a join t2 order by a.id;

MariaDB [tmp]> select count(*) from t1_view where id not in (select id from t1_view);
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

MariaDB [tmp]> select count(*) from t1_view a where not exists (select id from t1_view b where a.id=b.id);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

当前用 not exists 结果合理,但在一些情况下也有问题.

MariaDB [tmp]> select count(*) from t1_view a where not exists (select id from t1 b where a.id=b.id);
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

不知道是什么情况,现在很混乱. 希望懂的人解释一下原因.

版本 10.1.29-MariaDB-6+b1 Debian buildd-unstable

[choury]

说了这么多没用的……你直接 select * from t1_view 就怎知道了

[sycxyc]

@choury 你用手机所以可能不太清楚情况. t1 和 t1_view 数据是是一样的, 但结果应该都是 0. 实际程序比这个复杂很多, 复现条件是视图中至少要使用 join 和 order by, 我是很难再简化了.

[choury]

你这个不带条件的 JOIN 就是做了一个迪卡尔乘积
所以你这个视图里面的数据应该实施这样的
id t2.id
1 null
2 null
3 null
null 1
想明白怎么回事没？

[sycxyc]

@choury 笛卡尔乘积那来的 null?
执行一下就知道了 select a.id,t2.id from t1 a join t2 order by a.id;
现在我很混乱,你不要乱说啊

[msg7086]

SELECT * FROM t1_view c WHERE c.id NOT IN (SELECT id FROM t1);
SELECT * FROM t1_view c WHERE c.id IN (SELECT id FROM t1);

这两句的结果完全一样。

所以我觉得是，MySQL 不支持你把「同表查询」和「表连接」和「 IN 子查询」和「视图」混在一起用。
我没找到具体的文档，估计你得去找开发者论坛问了。

[sycxyc]

@msg7086 不支持应该会报错,例如下面这种

MariaDB [tmp]> select * from t1_view c where c.id not in (select id from t1 limit 1);
ERROR 1235 (42000): This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

我认为是 mysql 优化器对视图支持存在缺陷, 目前发现新版本可以

set @@optimizer_switch='materialization=off';

关闭后对 not exists 的结果会有所改善
禁用优化的另一种方法是,条件不要"简单直接"

select count(*) from t1_view where 0+id not in (select id from t1_view);

[blodside]

实际上我用 mysql 8.0.12 复现不出来这个问题，也就是例子里这几个 count 都是 0。

[sycxyc]

@blodside 感谢反馈
看来是 MariaDB 分支引入的 bug,
版本 5.6.37 MySQL Community Server (GPL) 也无法复现

[msg7086]

@sycxyc 我说的「不支持」就是说可能代码里没有考虑到这种情况所以出的 Bug。
建议还是回报到开发者论坛。
上面我贴的那两句查询已经足够说明问题了。

[sycxyc]

@msg7086 恩, mariadb 那边已经确认 bug 了
https://jira.mariadb.org/browse/MDEV-17074

[msg7086]

@sycxyc 好像是物化视图翻车……