Why does this simple Left Join return data from unmatched rows?
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:
(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 sa 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'
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!
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);
This is the normal way LEFT JOIN works. It appends new columns to the result, then fills them with:
JOIN ed if the JOIN succeeds, NULL s if the JOIN doesn't match (that includes the fields you joined ON )! Normally there is no distinction between NULL s pulled from real tables (where JOIN matched) and NULL s filled in because the JOIN didn't match. The CASE + IFNULL just look for NULL s and swaps them to 0 s (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 JOIN ed on are NULL s, 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 JOIN ed.
上一篇: 在XCUITest中强制触摸/ 3D触摸