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' |
Attachments
Issue Links
Activity
| Field | Original Value | New Value |
|---|---|---|
| Remote Link | This issue links to "dba.stackexchange.com (origin with few explain outputs) (Web Link)" [ 29014 ] |
| Assignee | Alice Sherepa [ alice ] |
| Priority | Minor [ 4 ] | Major [ 3 ] |
| Affects Version/s | 5.5 [ 15800 ] | |
| Affects Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 10.1 [ 16100 ] | |
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.3 [ 22126 ] | |
| Affects Version/s | 10.4 [ 22408 ] |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Assignee | Alice Sherepa [ alice ] | Igor Babaev [ igor ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| Assignee | Igor Babaev [ igor ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Igor Babaev [ igor ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 5.5.65 [ 23402 ] | |
| Fix Version/s | 10.1.41 [ 23406 ] | |
| Fix Version/s | 10.2.25 [ 23408 ] | |
| Fix Version/s | 10.3.16 [ 23410 ] | |
| Fix Version/s | 10.4.6 [ 23412 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 96940 ] | MariaDB v4 [ 156250 ] |