MariaDB 10.2.11+左连接视图/子查询丢失NULL行问题咨询

先补全你的测试场景，方便更直观地重现问题：

测试表与数据 #

-- 创建表t1
create table t1 ( 
    id int not null auto_increment, 
    name varchar(50), 
    primary key (id) 
) engine = innodb auto_increment = 1 default character set = utf8;

-- 创建表t2
create table t2 ( 
    id int not null auto_increment, 
    pid int not null, 
    subname varchar(50), 
    primary key (id) 
) engine = innodb auto_increment = 1 default character set = utf8;

-- 插入测试数据
INSERT INTO t1 (name) VALUES ('A'), ('B'), ('C'); -- t1中C在t2无关联数据
INSERT INTO t2 (pid, subname) VALUES (1, 'A_sub1'), (1, 'A_sub2'), (2, 'B_sub1');

假设你用如下方式进行LEFT JOIN操作：

示例问题SQL #

比如先创建关联t2的视图：

CREATE VIEW v_t2 AS 
SELECT pid, COUNT(*) AS sub_count 
FROM t2 
GROUP BY pid;

再执行LEFT JOIN查询：

SELECT t1.id, t1.name, v_t2.sub_count
FROM t1
LEFT JOIN v_t2 ON t1.id = v_t2.pid;

或者直接使用子查询关联：

SELECT t1.id, t1.name, sub.sub_count
FROM t1
LEFT JOIN (
    SELECT pid, COUNT(*) AS sub_count 
    FROM t2 
    GROUP BY pid
) sub ON t1.id = sub.pid;

在MariaDB 10.2.10及以下版本，你应该能得到3行结果（其中id=3的行sub_count为NULL）；但在10.2.11及以上版本，可能只会返回id=1、2的两行，丢失了原本该保留的NULL行。

成因解释 #

这个现象的核心是MariaDB 10.2.11版本对查询优化器做了升级，新增了“推导连接类型”的优化逻辑：当优化器判断LEFT JOIN右侧的视图/子查询/表中，关联字段不可能出现NULL值时，会自动将LEFT JOIN转换为INNER JOIN，从而过滤掉原本应该保留的无匹配行。

具体到你的场景，触发优化的关键点：

• t2的pid字段是NOT NULL约束，子查询/视图通过GROUP BY pid聚合后，返回的pid列必然是非空的
• 优化器默认开启的derived_merge（子查询合并）等参数，会让优化器进一步推导：既然右侧结果集的pid不可能为NULL，那么LEFT JOIN中t1无匹配的情况（即右侧关联字段为NULL）根本不存在，因此可以安全转为INNER JOIN，最终丢失了NULL行。

验证与解决方法 #

1. 验证优化器行为 #

可以通过EXPLAIN查看执行计划，确认是否被转换为INNER JOIN：

EXPLAIN 
SELECT t1.id, t1.name, v_t2.sub_count
FROM t1
LEFT JOIN v_t2 ON t1.id = v_t2.pid;

如果执行计划中显示连接类型为INNER JOIN，或者Extra字段提示了相关优化逻辑，就说明优化器确实做了转换。

2. 可行的解决方式 #

• 强制保留NULL行判断：在关联条件中加入一个恒真的NULL兼容判断，让优化器意识到需要保留无匹配行：

  SELECT t1.id, t1.name, v_t2.sub_count
  FROM t1
  LEFT JOIN v_t2 ON t1.id = v_t2.pid OR v_t2.pid IS NULL;
  

• 修改子查询/视图，手动加入NULL行：通过UNION ALL给右侧结果集添加一条pid为NULL的记录，打破优化器的非空推导：

  SELECT t1.id, t1.name, sub.sub_count
  FROM t1
  LEFT JOIN (
      SELECT pid, COUNT(*) AS sub_count 
      FROM t2 
      GROUP BY pid
      UNION ALL
      SELECT NULL AS pid, 0 AS sub_count
  ) sub ON t1.id = sub.pid;
  

• 临时关闭对应优化参数：会话级关闭derived_merge优化（不推荐全局修改）：

  SET optimizer_switch='derived_merge=off';
  -- 执行查询
  SELECT t1.id, t1.name, v_t2.sub_count FROM t1 LEFT JOIN v_t2 ON t1.id = v_t2.pid;
  -- 恢复默认设置
  SET optimizer_switch='derived_merge=on';
  

内容的提问来源于stack exchange，提问作者Kelvin Jin