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!

Answer 2

Processing algorithm of your view causes this result. For default, MySQL usually chooses MERGE, because it is more efficient. If you create a view with "TEMPTABLE" algorithm you will be able to see NULL for the unmatched rows.

http://www.mysqltutorial.org/create-sql-views-mysql.aspx

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

Answer 3

This is the normal way LEFT JOIN works. It appends new columns to the result, then fills them with:

• values pulled from the table being JOINed if the JOIN succeeds,
• NULLs if the JOIN doesn't match (that includes the fields you joined ON)!

Normally there is no distinction between NULLs pulled from real tables (where JOIN matched) and NULLs filled in because the JOIN didn't match. The CASE + IFNULL just look for NULLs and swaps them to 0s (no matter their source). That's why you have results in the state column even in unmatched rows.

As a matter of fact, if you want to know if a given NULL you are looking at was a result of not matching a JOIN, you need to explicitly check this - if all key fields you JOINed on are NULLs, when the NULL in this column is a result of a fill-in. If the fields from key are present in this row yet there is still a NULL in other column, then it is there because it was pulled from the table you JOINed.