I have a view in MySQL, which has some calculated fields — COALESCE
and the like. If I do a left outer join on that view (so records from that view may be missing), then the calculated fields are still calculated for the missing records, instead of showing up as NULL
.
Example
I've tested the following example on MySQL versions 5.5.40-36.1-log and 5.5.53-38.5 (because that's what I've got on hand) as well as on SQL Fiddle‡ versions 5.5 and 5.6.
Setup
-- A simple view
CREATE OR REPLACE VIEW foo
AS
SELECT 1 AS x
UNION SELECT 2
UNION SELECT 3;
-- Another simple view
CREATE OR REPLACE VIEW bar
AS
SELECT 1 AS y
UNION SELECT 2
UNION SELECT 3;
-- A (contrived) view with a calculated column
CREATE OR REPLACE VIEW baz
AS
SELECT
f.x,
b.y,
SIGN(COALESCE(b.y, 0)) AS z
FROM foo f
LEFT JOIN bar b ON f.x = 2 * b.y;
Query
-- A query that does a left outer join
-- on the view with the calculated column
SELECT *
FROM foo f
LEFT JOIN baz b ON f.x = b.y;
Expected result
I would expect the following result, where we see one row with records from baz
and two rows without.
| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | - |
| 3 | - | - | - |
Actual result
But instead I get the following, with indeed one row with records from baz
, but two rows where there are no records from baz
except for the calculated column, which is calculated for all three rows!
| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | 0 |
| 3 | - | - | 0 |
So z
is calculated, even where baz
returns no records (as evidenced by the NULLs for x
and y
).
My actual case involves a join between the view and a table the view is based on. I've replicated this in the example, using views only. I've also tested it without the "self-join"; this makes no difference.
Workaround
What does work for me, is hiding baz
in a sub-select, like so.
SELECT *
FROM foo f
LEFT JOIN (
SELECT *
FROM baz
) b ON f.x = b.y;
This yields the expected result:
| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | - |
| 3 | - | - | - |
Is this expected behaviour? Why?
‡: worked for me yesterday, can't get it to work today.