[MDEV-11958] LEFT JOIN with stored routine produces incorrect result - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.0.28, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 10.1.24, 5.5.57, 10.0.32, 10.2.7
Component/s: Stored routines
Labels:
- upstream
Environment:
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 \|
+---------+------+------+---------+------------+

Attachments

Activity

Ascending order - Click to sort in descending order

View 14 older comments

Oleksandr Byelkin added a comment - 2017-05-22 05:11

revision-id: 46a1ae7283364741e926b50bbd5cc7a8019a8db1 (mariadb-5.5.56-13-g46a1ae72833)
parent(s): 7d57ba6e28f8dd5f6ab48b0b99d110c2363b576d
committer: Oleksandr Byelkin
timestamp: 2017-05-22 07:09:49 +0200
message:

~~MDEV-11958~~: LEFT JOIN with stored routine produce incorrect result

Added forgoten method of Item_func_sp to make it correctly work with LEFT/RIGHT JOIN.

—

Oleksandr Byelkin added a comment - 2017-05-22 05:11 revision-id: 46a1ae7283364741e926b50bbd5cc7a8019a8db1 (mariadb-5.5.56-13-g46a1ae72833) parent(s): 7d57ba6e28f8dd5f6ab48b0b99d110c2363b576d committer: Oleksandr Byelkin timestamp: 2017-05-22 07:09:49 +0200 message: MDEV-11958 : LEFT JOIN with stored routine produce incorrect result Added forgoten method of Item_func_sp to make it correctly work with LEFT/RIGHT JOIN. —

Oleksandr Byelkin added a comment - 2017-05-22 05:12

github branch is bb-5.5-~~MDEV-11958~~

Oleksandr Byelkin added a comment - 2017-05-22 05:12 github branch is bb-5.5- MDEV-11958

Igor Babaev added a comment - 2017-05-22 23:01 - edited

If we look at the output of EXPLAIN EXTENDED for the query

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;

we see

MariaDB [matt_test]> EXPLAIN EXTENDED

    -> 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;

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                           |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+

|    1 | SIMPLE      | ctl   | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 |                                                 |

|    1 | SIMPLE      | ctl1  | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where; Using join buffer (flat, BNL join) |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+

2 rows in set, 1 warning (7.19 sec)

MariaDB [matt_test]> SHOW WARNINGS;

+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message                                                                                                                                                                                                                                                                                                                  |

+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Note  | 1003 | select `matt_test`.`ctl`.`col1` AS `col1`,`matt_test`.`ctl1`.`col1` AS `col1`,`matt_test`.`ctl1`.`col3` AS `col3` from `matt_test`.`cm_test_nvl` `ctl` left join `matt_test`.`cm_test_nvl1` `ctl1` on((`matt_test`.`ctl1`.`col2` = `matt_test`.`ctl`.`col1`)) where (`DB_NVL_NUMBER_5`(`matt_test`.`ctl1`.`col3`,0) = 0) |

+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

that the LEFT JOIN was converted to INNER JOIN.
It should not have happened with the WHERE condition

DB_NVL_NUMBER_5(ctl1.col3,0) = 0;

Yet it happened. It happened because the class Item_func_sp lacks it's own
implementation of the virtual method eval_not_null_tables() and
Item_func::eval_not_null_tables() is used instead. The latter updates
Item_func_sp::not_null_tables_cache incorrectly.

Igor Babaev added a comment - 2017-05-22 23:01 - edited If we look at the output of EXPLAIN EXTENDED for the query 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; we see MariaDB [matt_test]> EXPLAIN EXTENDED -> 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; +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ | 1 | SIMPLE | ctl | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | | | 1 | SIMPLE | ctl1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------------------------------------------+ 2 rows in set, 1 warning (7.19 sec) MariaDB [matt_test]> SHOW WARNINGS; +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `matt_test`.`ctl`.`col1` AS `col1`,`matt_test`.`ctl1`.`col1` AS `col1`,`matt_test`.`ctl1`.`col3` AS `col3` from `matt_test`.`cm_test_nvl` `ctl` left join `matt_test`.`cm_test_nvl1` `ctl1` on((`matt_test`.`ctl1`.`col2` = `matt_test`.`ctl`.`col1`)) where (`DB_NVL_NUMBER_5`(`matt_test`.`ctl1`.`col3`,0) = 0) | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ that the LEFT JOIN was converted to INNER JOIN. It should not have happened with the WHERE condition DB_NVL_NUMBER_5(ctl1.col3,0) = 0; Yet it happened. It happened because the class Item_func_sp lacks it's own implementation of the virtual method eval_not_null_tables() and Item_func::eval_not_null_tables() is used instead. The latter updates Item_func_sp::not_null_tables_cache incorrectly.

Igor Babaev added a comment - 2017-05-23 03:14

Sanja,
See my observations in your mail-box.

Igor Babaev added a comment - 2017-05-23 03:14 Sanja, See my observations in your mail-box.

Oleksandr Byelkin added a comment - 2017-05-23 09:08

revision-id: b8405c853fa30002d164d5fe2b4f8ea8979c09b8 (mariadb-5.5.56-13-gb8405c853fa)
parent(s): 7d57ba6e28f8dd5f6ab48b0b99d110c2363b576d
committer: Oleksandr Byelkin
timestamp: 2017-05-23 11:06:31 +0200
message:

~~MDEV-11958~~: LEFT JOIN with stored routine produces incorrect result

Added forgoten method of Item_func_sp to make it correctly work with LEFT/RIGHT JOIN.
Fixed inconsistency with the null table caches.

—

Oleksandr Byelkin added a comment - 2017-05-23 09:08 revision-id: b8405c853fa30002d164d5fe2b4f8ea8979c09b8 (mariadb-5.5.56-13-gb8405c853fa) parent(s): 7d57ba6e28f8dd5f6ab48b0b99d110c2363b576d committer: Oleksandr Byelkin timestamp: 2017-05-23 11:06:31 +0200 message: MDEV-11958 : LEFT JOIN with stored routine produces incorrect result Added forgoten method of Item_func_sp to make it correctly work with LEFT/RIGHT JOIN. Fixed inconsistency with the null table caches. —

People

Assignee:: Oleksandr Byelkin

Reporter:: Yi Yun Jung (Inactive)

Votes:: 2 Vote for this issue

Watchers:: 7 Start watching this issue

Dates

Created:: 2017-02-01 08:27

Updated:: 2024-07-08 00:59

Resolved:: 2017-05-23 09:10

Git Integration

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

MariaDB Server