0

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"
          }
        }
      }
    }
  }
}
  • 1
    Looks worthy of a bug report to me. jira.mariadb.org – danblack May 23 at 22:20
  • Please provide 4 EXPLAIN SELECT ... (one per case per server). And, if available, EXPLAIN FORMAT=JSON SELECT ... – Rick James May 23 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. – skotos May 23 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.) – skotos May 23 at 23:23
  • 1
    Starting to agree with @danblack . Submitted a bug. (jira.mariadb.org/browse/MDEV-19580) – skotos May 24 at 5:52
1

This has been confirmed as a bug:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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