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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5, 10.0, 10.1, 10.1.38, 10.3.13, 10.2, 10.3, 10.4
    • 10.2.25, 5.5.65, 10.1.41, 10.3.16, 10.4.6
    • Optimizer, Server
    • None
    • 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. test.sql
          0.2 kB
        2. prime.sql
          1 kB

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.