Left join filters null values ​​using a custom function in which

Question

I have a table referencing a city table with a key cityId. I am extracting data from it with this query:

SELECT t.ID, city.areaId FROM transp t
LEFT JOIN city ON city.ID = t.cityId;

Similarly, it returns the table as is, with NULL for city.areaIdIf cityIdis null.

But when I add a function to the where clause, using city.areaIdeven a function that is always true, the query does not display strings that are cityIdnull. eg:

SELECT t.ID, city.areaId FROM transp t
LEFT JOIN city ON city.ID = t.cityId
WHERE always_true(city.areaId);

Do not show rows with a null value cityId. I don’t understand why this is happening because I use left join, and if I put the function in SELECT, I see that it is really always true.

SQLFiddle

Answer 1

Well, although it looks like MySQL is doing some internal optimization, which I would say is buggy (converting the left connection to the internal one is wrong in this case) there is a workaround

SELECT t.ID, city.areaId
FROM transp t
LEFT JOIN city ON city.ID = t.cityId
WHERE always_true(coalesce(city.areaId, null));