Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5, 10.0, 10.1, 10.0.28, 10.2
-
Red Hat Enterprise Linux 64bit
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 | |
+---------+------+------+---------+------------+ |