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' |
Major 