Why does this simple Left Join return data from unmatched rows?

Question

Please see the simple http://sqlfiddle.com/#!9/e853f/1 for this problem in operation.

I refer to MySQL ver 5.6.12-log

As I understand it, a left join returns NULL for columns in the rightmost dataset where the key in the left dataset does not exist in the right dataset.

However, I am getting data returned from the right hand side even where the left hand key does not exist in the right.

Can anyone explain what is going on here?

The SQLfiddle creates:

• A table with 6 rows, each containing just an integer ID
• A second table with 3 rows containing some of those integer IDs plus two more INT fields
• A view based upon that second table that returns 3 rows containing the integer ID plus a textual field, derived from the two other INT fields

(Obviously, the 3 IDs in the view correspond to some of the IDs in the 6 row table.)

The SQL SELECT * FROM LEFT JOIN ON table_ID = view_ID; returns 6 rows as expected but all of them have data in the textual field instead of the 3 unmatched ones being NULL

BUT

If the method used in the view to derive the textual column is slightly altered, then the Left Join SQL gives the correct result. (You can show this by selectively commenting out one or other of the two methods in sql fiddle)

But surely doesn't the optimiser evaluate the view first, so it shouldn't matter how the data is created, just what it contains?

(This s a much simplified version of an earlier question of mine that I admit was rather too complicated to illicit sensible answers)

It has been suggested (Jeroen Mostert)that I show data and expected results. Here it is:

Table person

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

View payment_state

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

Query

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

Expected result

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

Actual result

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

Answer 1

I beg to disagree with other answers. This is a MySQL defect. Actually it is bug #83707 in MySQL 5.6. It looks it's fixed in MySQL 5.7

This bug is already fixed in MariaDB 5.5.

The internal join strategy such as Nested Loop Join, Merge Join, or Hash Join does not matter. The result should be correct in any case.

I tried the same query in PostgreSQL and Oracle and it works as expected, returning null values on the last three rows.

Oracle Example

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;

Result:

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

Works perfectly!

PostgreSQL Example

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;

Result:

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

Also, works perfectly!