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

LEFT JOIN with stored routine produces incorrect result

    Details

      Description

      LEFT JOIN with stored routine produce incorrect result.
      I have a simple stored routine that replaces IFNULL.
      When I do a LEFT OUTER JOIN, the result should be the same whether I use IFNULL or a stored routine.
      But the test results are different.

      Does the optimizer use a stored routine to establish a different execution plan than to use IFNULL?

      stored routine

      CREATE FUNCTION `DB_NVL_NUMBER_5`(
        `p_num` DECIMAL(45,15)
      , `p_return` DECIMAL(45,15)
       
      )
      RETURNS decimal(33,5)
      LANGUAGE SQL
      DETERMINISTIC
      CONTAINS SQL
      SQL SECURITY INVOKER
      BEGIN
       
        IF p_num IS NULL THEN
          RETURN p_return;
        ELSE
          RETURN p_num;
        END IF;
       
      END;
      

      Test Case

      CREATE TABLE `cm_test_nvl` (
       `col1` DECIMAL(33,5) NULL DEFAULT NULL,
       `col2` DECIMAL(33,5) NULL DEFAULT NULL
      )
      COLLATE='utf8_bin'
      ENGINE=InnoDB;
       
      CREATE TABLE `cm_test_nvl1` (
       `col1` DECIMAL(33,5) NULL DEFAULT NULL,
       `col2` DECIMAL(33,5) NULL DEFAULT NULL,
       `col3` DECIMAL(33,5) NULL DEFAULT NULL
      )
      COLLATE='utf8_bin'
      ENGINE=InnoDB;
       
      insert into cm_test_nvl values (2, 1.1), (2, 2.1);
      insert into cm_test_nvl1 values (3, 3.1, 4), (1, 1, NULL);
       
      MariaDB [test]> SELECT ctl.col1
          ->       ,ctl1.col1
          ->       ,ctl1.col3
          -> FROM  cm_test_nvl ctl
          -> LEFT OUTER JOIN cm_test_nvl1 ctl1
          -> ON   ctl.col1 = ctl1.col2
          -> AND IFNULL(ctl1.col3,0) = 0;
      +---------+------+------+
      | col1    | col1 | col3 |
      +---------+------+------+
      | 2.00000 | NULL | NULL |
      | 2.00000 | NULL | NULL |
      +---------+------+------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> SELECT ctl.col1
          ->       ,ctl1.col1
          ->       ,ctl1.col3
          -> FROM  cm_test_nvl ctl
          -> LEFT OUTER JOIN cm_test_nvl1 ctl1
          -> ON   ctl.col1 = ctl1.col2
          -> WHERE DB_NVL_NUMBER_5(ctl1.col3,0) = 0;
      Empty set (0.00 sec)
       
      MariaDB [test]> SELECT ctl.col1
          ->       ,ctl1.col1
          ->       ,ctl1.col3
          ->       ,DB_NVL_NUMBER_5(ctl1.col3,0) AS FN
          ->       ,DB_NVL_NUMBER_5(ctl1.col3,0) = 0 AS FN_COMPARE
          -> FROM  cm_test_nvl ctl
          -> LEFT OUTER JOIN cm_test_nvl1 ctl1
          -> ON   ctl.col1 = ctl1.col2
          -> ;
      +---------+------+------+---------+------------+
      | col1    | col1 | col3 | FN      | FN_COMPARE |
      +---------+------+------+---------+------------+
      | 2.00000 | NULL | NULL | 0.00000 |          1 |
      | 2.00000 | NULL | NULL | 0.00000 |          1 |
      +---------+------+------+---------+------------+
      

        Attachments

          Activity

            People

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              Yunjung Yi Yun Jung
            • Votes:
              2 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: