Details
Major Description
In certain circumstances a LEFT JOIN can prevent a WHERE clause comparing a function's result with a subquery from working:
SELECT * FROM `employee` |
LEFT JOIN `division` ON `employee`.`division_id` = `division`.`id` |
WHERE pastPosition(`employee`.`id`, '1980-01-01') IN |
(SELECT `id` FROM `position` WHERE `is_management`=1); |
Expected results:
| id | name | division_id | id | name |
|---|---|---|---|---|
| 1 | Mrs Robinson | 2 | 2 | Song Character |
| 3 | Paul Simon | 1 | 1 | Songwriter |
| 4 | Art Garfunkel | 1 | 1 | Songwriter |
Actual results:
No rows returned
If LEFT is omitted from the JOIN it seems to work.
pastPosition function:
CREATE FUNCTION `pastPosition`(`who` INT(11), `when` DATE) RETURNS int(11)
|
BEGIN
|
DECLARE `result` int(11);
|
SELECT `position_id` INTO `result`
|
FROM `position_history`
|
WHERE `when`>=`from` AND `when`<=`to` AND `employee_id`=`who`;
|
RETURN `result`;
|
END$$
|
DELIMITER ;
|
Data:
employee table:
| id | name | division_id |
|---|---|---|
| U.INT(11) | VARCHAR(50) | U.INT(11) |
| 1 | 'Mrs Robinson' | 2 |
| 2 | 'Joe DiMaggio' | 2 |
| 3 | 'Paul Simon' | 1 |
| 4 | 'Art Garfunkel' | 1 |
position table:
| id | position_name | is_management |
|---|---|---|
| U.INT(11) | VARCHAR(50) | TINYINT(1) |
| 1 | 'Grand Poobah' | 1 |
| 2 | 'Average Poobah' | 1 |
| 3 | 'Serf' | 0 |
position_history table:
| from | to | employee_id | position_id |
|---|---|---|---|
| DATE | DATE | U.INT(11) | U.INT(11) |
| '1972-01-01' | '1988-12-31' | 3 | 1 |
| '1972-01-01' | '1988-12-31' | 4 | 1 |
| '1972-01-01' | '1988-12-31' | 1 | 2 |
| '1972-01-01' | '1988-12-31' | 2 | 3 |
division table:
| id | name |
|---|---|
| U.INT(11) | VARCHAR(50) |
| 1 | 'Songwriter' |
| 2 | 'Song Character' |