Cannot get a user function to co-operate with a subquery in mariadb

Question

The following is a demo of an issue we are having with a function and a subquery. I'm interested to know if it is a bug or if there is a reasonable explaination why it is failing.

Notes:

Problem only in MariaDB, not in MySQL.
We are using version 10.3.13 of MariaDB.
Issue shows in both Gentoo and Debian distros.
If a constant is used instead of the function, it behaves.
If a list is used instead of the subquery, it behaves.
In our testing on the real application, multiple JOINs (with or without LEFT) also broke the query.

Why is the JOIN affecting the result at all?

USE `test`;
DROP FUNCTION IF EXISTS `pastPosition`;
DELIMITER $$
USE `test`$$
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 ;

CREATE TEMPORARY TABLE `employee` (
    `id` INT(11) UNSIGNED NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `division_id` INT(11) UNSIGNED NOT NULL
);
INSERT INTO `employee` VALUES 
    (1, 'Mrs Robinson', 2),
    (2, 'Joe DiMaggio', 2),
    (3, 'Paul Simon', 1),
    (4, 'Art Garfunkel', 1)
;

CREATE TEMPORARY TABLE `position` (
    `id` INT(11) UNSIGNED NOT NULL,
    `position_name` VARCHAR(50) NOT NULL,
    `is_management` TINYINT(1) NOT NULL
);
INSERT INTO `position` VALUES 
    (1, 'Grand Poobah', 1),
    (2, 'Average Poobah', 1),
    (3, 'Serf', 0)
;

CREATE TEMPORARY TABLE `position_history` (
    `from` DATE NOT NULL,
    `to` DATE NOT NULL,
    `employee_id` int(11) NOT NULL,
    `position_id` int(11) NOT NULL
);
INSERT INTO `position_history` VALUES 
    ('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)
;

CREATE TEMPORARY TABLE `division` (
    `id` INT(11) UNSIGNED NOT NULL,
    `name` VARCHAR(50) NOT NULL
);
INSERT INTO `division` VALUES 
    (1, 'Songwriter'),
    (2, 'Song Character')
;

SELECT '  employees in management in 1980:' AS 'WITH_LEFT_JOIN';
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);

SELECT '  employees in management in 1980:' AS 'WITH_JOIN';
SELECT *
    FROM `employee`
    JOIN `division` ON `employee`.`division_id` = `division`.`id`
    WHERE pastPosition(`employee`.`id`, '1980-01-01') IN
        (SELECT `id` FROM `position` WHERE `is_management`=1);

results on mysql, (working):

WITH_LEFT_JOIN
  employees in management in 1980:
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
WITH_JOIN
  employees in management in 1980:
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

results on mariadb, (not working):

WITH_LEFT_JOIN
  employees in management in 1980:
WITH_JOIN
  employees in management in 1980:
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

Here is the EXPLAIN SELECT outputs:

LEFT JOIN

MySQL (working):

+----+--------------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | employee | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
|  1 | PRIMARY            | division | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
|  2 | DEPENDENT SUBQUERY | position | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+--------------------+----------+------+---------------+------+---------+------+------+-------------+

Maridb (non-working):

+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------------------------------------------+
| id   | select_type  | table       | type   | possible_keys | key          | key_len | ref  | rows | Extra                                           |
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------------------------------------------+
|    1 | PRIMARY      | employee    | ALL    | NULL          | NULL         | NULL    | NULL |    4 |                                                 |
|    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 4       | func |    1 | Using where                                     |
|    1 | PRIMARY      | division    | ALL    | NULL          | NULL         | NULL    | NULL |    2 | Using where; Using join buffer (flat, BNL join) |
|    2 | MATERIALIZED | position    | ALL    | NULL          | NULL         | NULL    | NULL |    3 | Using where                                     |
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------------------------------------------+

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "employee",
      "access_type": "ALL",
      "rows": 4,
      "filtered": 100
    },
    "table": {
      "table_name": "<subquery2>",
      "access_type": "eq_ref",
      "possible_keys": ["distinct_key"],
      "key": "distinct_key",
      "key_length": "4",
      "used_key_parts": ["id"],
      "ref": ["func"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "pastPosition(employee.`id`,'1980-01-01') = position.`id`",
      "materialized": {
        "unique": 1,
        "query_block": {
          "select_id": 2,
          "table": {
            "table_name": "position",
            "access_type": "ALL",
            "rows": 3,
            "filtered": 100,
            "attached_condition": "position.is_management = 1"
          }
        }
      }
    },
    "block-nl-join": {
      "table": {
        "table_name": "division",
        "access_type": "ALL",
        "rows": 2,
        "filtered": 100
      },
      "buffer_type": "flat",
      "buffer_size": "256Kb",
      "join_type": "BNL",
      "attached_condition": "trigcond(pastPosition(employee.`id`,'1980-01-01') = position.`id`) and trigcond(division.`id` = employee.division_id)"
    }
  }
}

JOIN

MySQL (working):

+----+--------------------+----------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type        | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+--------------------+----------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | PRIMARY            | division | ALL  | NULL          | NULL | NULL    | NULL |    2 |                                |
|  1 | PRIMARY            | employee | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer |
|  2 | DEPENDENT SUBQUERY | position | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where                    |
+----+--------------------+----------+------+---------------+------+---------+------+------+--------------------------------+

MariaDB (working):

+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------------------------------------------+
| id   | select_type  | table       | type   | possible_keys | key          | key_len | ref  | rows | Extra                                           |
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------------------------------------------+
|    1 | PRIMARY      | division    | ALL    | NULL          | NULL         | NULL    | NULL |    2 |                                                 |
|    1 | PRIMARY      | employee    | ALL    | NULL          | NULL         | NULL    | NULL |    4 | Using where; Using join buffer (flat, BNL join) |
|    1 | PRIMARY      | <subquery2> | eq_ref | distinct_key  | distinct_key | 4       | func |    1 | Using where                                     |
|    2 | MATERIALIZED | position    | ALL    | NULL          | NULL         | NULL    | NULL |    3 | Using where                                     |
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------------------------------------------+

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "division",
      "access_type": "ALL",
      "rows": 2,
      "filtered": 100
    },
    "block-nl-join": {
      "table": {
        "table_name": "employee",
        "access_type": "ALL",
        "rows": 4,
        "filtered": 100
      },
      "buffer_type": "flat",
      "buffer_size": "256Kb",
      "join_type": "BNL",
      "attached_condition": "employee.division_id = division.`id`"
    },
    "table": {
      "table_name": "<subquery2>",
      "access_type": "eq_ref",
      "possible_keys": ["distinct_key"],
      "key": "distinct_key",
      "key_length": "4",
      "used_key_parts": ["id"],
      "ref": ["func"],
      "rows": 1,
      "filtered": 100,
      "attached_condition": "pastPosition(employee.`id`,'1980-01-01') = position.`id`",
      "materialized": {
        "unique": 1,
        "query_block": {
          "select_id": 2,
          "table": {
            "table_name": "position",
            "access_type": "ALL",
            "rows": 3,
            "filtered": 100,
            "attached_condition": "position.is_management = 1"
          }
        }
      }
    }
  }
}

Please provide 4 EXPLAIN SELECT ... (one per case per server). And, if available, EXPLAIN FORMAT=JSON SELECT ... — Rick James, Commented May 23, 2019 at 22:32
I've added the EXPLAINS for MariaDB to the question. MySQL coming soon. I'm new to EXPLAIN SELECT, so I'll be working through the output too. — ΣΚΟΤ, Commented May 23, 2019 at 23:09
MySQL explations are up. (It is running v5.5 which doesn't include the FORMAT=JSON option. I could spin up a later version if needed.) — ΣΚΟΤ, Commented May 23, 2019 at 23:23
Starting to agree with @danblack . Submitted a bug. (jira.mariadb.org/browse/MDEV-19580) — ΣΚΟΤ, Commented May 24, 2019 at 5:52

Mr.Brownstone · Accepted Answer · 2019-05-24 21:35:56Z

1

This has been confirmed as a bug:

Unrelated JOINs corrupt usage of 'WHERE function() IN (subquery)'

edited May 24, 2019 at 21:35

Mr.Brownstone

13.1k4 gold badges38 silver badges54 bronze badges

answered May 24, 2019 at 20:57

ΣΚΟΤ

1113 bronze badges

Add a comment |

Stack Exchange Network

Cannot get a user function to co-operate with a subquery in mariadb

LEFT JOIN

JOIN

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
join
mariadb
functions
subquery
or ask your own question.

Hot Network Questions

Cannot get a user function to co-operate with a subquery in mariadb

LEFT JOIN

JOIN

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged joinmariadbfunctionssubquery or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
join
mariadb
functions
subquery
or ask your own question.