mysql - 为什么这个简单的 Left Join 会从不匹配的行中返回数据？

Question

请看简单http://sqlfiddle.com/#!9/e853f/1针对运行中的这个问题。

我指的是 MySQL ver 5.6.12-log

据我了解，对于最右侧数据集中左侧数据集中的键在右侧数据集中不存在的列，左联接返回 NULL。

但是，即使右侧不存在左侧键，我也会从右侧获取返回的数据。

谁能解释一下这是怎么回事？

SQLfiddle 创建:

• 一个有 6 行的表，每行只包含一个整数 ID
• 第二个表有 3 行，其中包含一些整数 ID 加上两个更多 INT 字段
• 基于第二个表的 View ，该表返回 3 行，其中包含整数 ID 和一个从其他两个 INT 字段派生的文本字段

(很明显， View 中的3个ID对应6行表中的部分ID。)

SQLSELECT * FROM LEFT JOIN ON table_ID = view_ID;按预期返回 6 行，但 所有 文本字段中都有数据，而不是 3 个不匹配的行为 NULL

但是

如果在 View 中用于派生文本列的方法稍作更改，则 Left Join SQL 会给出正确的结果。(您可以通过在 sql fiddle 中有选择地注释掉两种方法中的一种或另一种来显示这一点)

但是优化器肯定不会首先评估 View ，所以数据是如何创建的并不重要，重要的是它包含什么？

(这是我之前的一个问题的简化版，我承认这个问题太复杂了，无法给出非法的明智答案)

有人建议 (Jeroen Mostert) 我展示数据和预期结果。在这里:

表人

personID
--------
   1
   2
   3
   4
   5
   6

查看付款状态

payment_personID  |   state
----------------------------
       1          |   'equal'
       2          |   'under'
       3          |   'over'

查询

SELECT * FROM  person 
LEFT JOIN   payment_state 
ON personID = payment_personID;

预期结果

personID | payment_personID  |state
-------------------------------------
    1    |      1            | 'equal'
    2    |      2            | 'under'
    3    |      3            | 'over'
    4    |     NULL          |  NULL
    5    |     NULL          |  NULL
    6    |     NULL          |  NULL

实际结果

personID | payment_personID  |state
-------------------------------------
    1    |      1            | 'equal'
    2    |      2            | 'under'
    3    |      3            | 'over'
    4    |     NULL          | 'equal'
    5    |     NULL          | 'equal'
    6    |     NULL          | 'equal'

Answer 1

我不同意其他答案。 这是一个MySQL缺陷。实际上是一个bug #83707在 MySQL 5.6 中。看起来它已在 MySQL 5.7 中修复

此错误已在 MariaDB 5.5 中修复。

Nested Loop Join、Merge Join 或 Hash Join 等内部连接策略无关紧要。无论如何，结果应该是正确的。

我在 PostgreSQL 和 Oracle 中尝试了相同的查询，它按预期工作，在最后三行返回空值。

Oracle 示例

CREATE TABLE person (personID INT); 

INSERT INTO person (personID) VALUES (1); 
INSERT INTO person (personID) VALUES(2); 
INSERT INTO person (personID) VALUES(3);
INSERT INTO person (personID) VALUES(4);
INSERT INTO person (personID) VALUES(5);
INSERT INTO person (personID) VALUES(6);

CREATE TABLE payments (
   payment_personID INT,
   Due INT,
   Paid INT) ;

INSERT INTO payments  (payment_personID, due, paid) VALUES (1, 5, 5);
INSERT INTO payments  (payment_personID, due, paid) VALUES (2, 5, 3);
INSERT INTO payments  (payment_personID, due, paid) VALUES (3, 5, 8);

CREATE VIEW payment_state AS (
SELECT
   payment_personID,
  CASE 
   WHEN COALESCE(paid,0) < COALESCE(due,0) AND due <> 0 THEN 'under' 
   WHEN COALESCE(paid,0) > COALESCE(due,0) THEN 'over' 
   WHEN COALESCE(paid,0) = COALESCE(due,0) THEN 'equal' 
   END AS state 
FROM payments);

SELECT *
FROM
    person
LEFT JOIN 
    payment_state   
ON personID = payment_personID;

结果:

PERSONID  PAYMENT_PERSONID  STATE
========  ================  =====
       1                 1  equal
       2                 2  under
       3                 3  over
       6            <null>  <null>
       5            <null>  <null>
       4            <null>  <null>

完美运行!

PostgreSQL 示例

CREATE TABLE person (personID INT); 
INSERT INTO person (personID) VALUES
(1),(2),(3),(4),(5),(6);

CREATE TABLE payments (
   payment_personID INT,
   Due INT,
   Paid INT) ;

INSERT INTO payments  (payment_personID, due, paid) VALUES
(1, 5, 5), (2, 5, 3), (3, 5, 8);

CREATE VIEW payment_state AS (
SELECT
   payment_personID,
  CASE 
   WHEN COALESCE(paid,0) < COALESCE(due,0) AND due <> 0 THEN 'under' 
   WHEN COALESCE(paid,0) > COALESCE(due,0) THEN 'over' 
   WHEN COALESCE(paid,0) = COALESCE(due,0) THEN 'equal' 
   END AS state 
FROM payments);

SELECT *
FROM
    person
LEFT JOIN 
    payment_state   
ON personID = payment_personID;

结果:

personid  payment_personid  state
========  ================  =====
       1                 1  equal
       2                 2  under
       3                 3  over
       4            <null>  <null>
       5            <null>  <null>
       6            <null>  <null>

此外，完美运行!