Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19580

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

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.1.38, 10.3.13
    • Fix Version/s: 5.5.65, 10.1.41, 10.2.25, 10.3.16, 10.4.6
    • Component/s: Optimizer, Server
    • Labels:
      None
    • Environment:
      Debian 9 with MariaDB 10.1.38
      Gentoo with MariaDB 10.3.13

      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

        1. prime.sql
          1 kB
        2. test.sql
          0.2 kB

          Activity

            People

            • Assignee:
              igor Igor Babaev
              Reporter:
              skotos Scott Tester
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: