首页   注册   登录
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
华为云
V2EX  ›  MySQL

mysql 视图 条件语句 疑似 bug

  •  
  •   sycxyc · 50 天前 · 836 次点击
    这是一个创建于 50 天前的主题,其中的信息可能已经有所发展或是发生改变。

    什么情况下,下列语句会输出 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

    11 回复  |  直到 2018-08-28 03:50:47 +08:00
        1
    choury   50 天前 via Android
    说了这么多没用的……你直接 select * from t1_view 就怎知道了
        2
    sycxyc   50 天前
    @choury 你用手机所以可能不太清楚情况. t1 和 t1_view 数据是是一样的, 但结果应该都是 0. 实际程序比这个复杂很多, 复现条件是视图中至少要使用 join 和 order by, 我是很难再简化了.
        3
    choury   50 天前
    你这个不带条件的 JOIN 就是做了一个迪卡尔乘积
    所以你这个视图里面的数据应该实施这样的
    id t2.id
    1 null
    2 null
    3 null
    null 1
    想明白怎么回事没?
        4
    sycxyc   50 天前
    @choury 笛卡尔乘积那来的 null?
    执行一下就知道了 select a.id,t2.id from t1 a join t2 order by a.id;
    现在我很混乱,你不要乱说啊
        5
    msg7086   49 天前
    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 子查询」和「视图」混在一起用。
    我没找到具体的文档,估计你得去找开发者论坛问了。
        6
    sycxyc   49 天前
    @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);
        7
    blodside   49 天前
    实际上我用 mysql 8.0.12 复现不出来这个问题,也就是例子里这几个 count 都是 0。
        8
    sycxyc   49 天前
    @blodside 感谢反馈
    看来是 MariaDB 分支引入的 bug,
    版本 5.6.37 MySQL Community Server (GPL) 也无法复现
        9
    msg7086   49 天前
    @sycxyc 我说的「不支持」就是说可能代码里没有考虑到这种情况所以出的 Bug。
    建议还是回报到开发者论坛。
    上面我贴的那两句查询已经足够说明问题了。
        10
    sycxyc   49 天前
    @msg7086 恩, mariadb 那边已经确认 bug 了
    https://jira.mariadb.org/browse/MDEV-17074
        11
    msg7086   48 天前
    @sycxyc 好像是物化视图翻车……
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2221 人在线   最高记录 3762   ·  
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.1 · 27ms · UTC 13:18 · PVG 21:18 · LAX 06:18 · JFK 09:18
    ♥ Do have faith in what you're doing.
    沪ICP备16043287号-1