|
Thanks for the report and test case.
Reproducible on MariaDB 5.5-10.2 and MySQL 5.5-5.7.
|
|
|
Please note that the test queries are different.
|
|
... LEFT JOIN ON ctl.col1 = ctl1.col2 AND <func or expr depending on right table column>
|
vs
|
... LEFT JOIN ON ctl.col1 = ctl1.col2 WHERE <func or expr depending on right table column>
|
|
With test case:
|
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,
|
IFNULL(ctl1.col3,0) AS IFNULL_COMPARE
|
FROM cm_test_nvl ctl LEFT OUTER JOIN cm_test_nvl1 ctl1
|
ON ctl.col1 = ctl1.col2
|
AND DB_NVL_NUMBER_5(ctl1.col3,0)=0;
|
|
+---------+------+------+---------+------------+----------------+
|
| col1 | col1 | col3 | FN | FN_COMPARE | IFNULL_COMPARE |
|
+---------+------+------+---------+------------+----------------+
|
| 2.00000 | NULL | NULL | 0.00000 | 1 | 0.00000 |
|
| 2.00000 | NULL | NULL | 0.00000 | 1 | 0.00000 |
|
+---------+------+------+---------+------------+----------------+
|
2 rows in set (0.00 sec)
|
|
|
|
Simplified test case:
CREATE TABLE t (x INT);
|
INSERT INTO t VALUES(1),(NULL);
|
CREATE FUNCTION f (val INT, ret INT) RETURNS INT DETERMINISTIC RETURN IFNULL(val, ret);
|
|
# The function above simply wraps IFNULL() and should always yield same result which is not the case
|
# when it is used in WHERE clause
|
# Moving it to HAVING clause produces same result as IFNULL()
|
|
MariaDB [bugs]> SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
|
FROM t t1 LEFT JOIN t t2
|
ON t1.x = t2.x;
|
+------+------+----------------+-----------+
|
| x | x | IFNULL(t2.x,0) | f(t2.x,0) |
|
+------+------+----------------+-----------+
|
| 1 | 1 | 1 | 1 |
|
| NULL | NULL | 0 | 0 |
|
+------+------+----------------+-----------+
|
2 rows in set (0.00 sec)
|
|
MariaDB [bugs]> SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
|
FROM t t1 LEFT JOIN t t2
|
ON t1.x = t2.x
|
AND IFNULL(t2.x,0)=0;
|
+------+------+----------------+-----------+
|
| x | x | IFNULL(t2.x,0) | f(t2.x,0) |
|
+------+------+----------------+-----------+
|
| 1 | NULL | 0 | 0 |
|
| NULL | NULL | 0 | 0 |
|
+------+------+----------------+-----------+
|
2 rows in set (0.00 sec)
|
|
MariaDB [bugs]> SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
|
FROM t t1 LEFT JOIN t t2
|
ON t1.x = t2.x
|
AND f(t2.x,0)=0;
|
+------+------+----------------+-----------+
|
| x | x | IFNULL(t2.x,0) | f(t2.x,0) |
|
+------+------+----------------+-----------+
|
| 1 | NULL | 0 | 0 |
|
| NULL | NULL | 0 | 0 |
|
+------+------+----------------+-----------+
|
2 rows in set (0.00 sec)
|
|
MariaDB [bugs]> SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
|
FROM t t1 LEFT JOIN t t2
|
ON t1.x = t2.x
|
WHERE IFNULL(t2.x,0)=0;
|
+------+------+----------------+-----------+
|
| x | x | IFNULL(t2.x,0) | f(t2.x,0) |
|
+------+------+----------------+-----------+
|
| NULL | NULL | 0 | 0 |
|
+------+------+----------------+-----------+
|
1 row in set (0.00 sec)
|
|
MariaDB [bugs]> SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
|
FROM t t1 LEFT JOIN t t2
|
ON t1.x = t2.x
|
WHERE f(t2.x,0)=0;
|
Empty set (0.00 sec)
|
|
MariaDB [bugs]> SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
|
FROM t t1 LEFT JOIN t t2
|
ON t1.x = t2.x
|
HAVING IFNULL(t2.x,0)=0;
|
+------+------+----------------+-----------+
|
| x | x | IFNULL(t2.x,0) | f(t2.x,0) |
|
+------+------+----------------+-----------+
|
| NULL | NULL | 0 | 0 |
|
+------+------+----------------+-----------+
|
1 row in set (0.00 sec)
|
|
MariaDB [bugs]> SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
|
FROM t t1 LEFT JOIN t t2
|
ON t1.x = t2.x
|
HAVING f(t2.x,0)=0;
|
+------+------+----------------+-----------+
|
| x | x | IFNULL(t2.x,0) | f(t2.x,0) |
|
+------+------+----------------+-----------+
|
| NULL | NULL | 0 | 0 |
|
+------+------+----------------+-----------+
|
1 row in set (0.00 sec)
|
|
These queries with a function in AND clause return two rows. IFNULL and DB_NVL_NUMBER_5 give the same result.
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 |
|
+---------+------+------+
|
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 DB_NVL_NUMBER_5(ctl1.col3,0) = 0;
|
+---------+------+------+
|
| col1 | col1 | col3 |
|
+---------+------+------+
|
| 2.00000 | NULL | NULL |
|
| 2.00000 | NULL | NULL |
|
+---------+------+------+
|
These queries with a function in WHERE clause give different results for IFNULL and DB_NVL_NUMBER_5:
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 IFNULL(ctl1.col3,0) = 0;
|
+---------+------+------+
|
| col1 | col1 | col3 |
|
+---------+------+------+
|
| 2.00000 | NULL | NULL |
|
| 2.00000 | NULL | NULL |
|
+---------+------+------+
|
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;
|
Looks like a bug.
|
|
The above query with DB_NVL_NUMBER_5 in WHERE clause seems to be wrong, and the query with IFNULL looks correct.
This query demonstrates that both IFNULL and DB_NVL_NUMBER_5 are evaluated to 0, and the conditions on them are both evaluated as TRUE:
SELECT
|
ctl.col1,
|
ctl1.col1,
|
ctl1.col3,
|
IFNULL(ctl1.col3,0) AS f1,
|
DB_NVL_NUMBER_5(ctl1.col3,0) AS f2,
|
IFNULL(ctl1.col3,0) = 0 AS fcond1,
|
DB_NVL_NUMBER_5(ctl1.col3,0) = 0 AS fcond2
|
FROM cm_test_nvl ctl
|
LEFT OUTER JOIN cm_test_nvl1 ctl1
|
ON ctl.col1 = ctl1.col2;
|
+---------+------+------+---------+---------+--------+--------+
|
| col1 | col1 | col3 | f1 | f2 | fcond1 | fcond2 |
|
+---------+------+------+---------+---------+--------+--------+
|
| 2.00000 | NULL | NULL | 0.00000 | 0.00000 | 1 | 1 |
|
| 2.00000 | NULL | NULL | 0.00000 | 0.00000 | 1 | 1 |
|
+---------+------+------+---------+---------+--------+--------+
|
|
|
Tracing in gdb the queries with NULLIF and DB_NVL_NUMBER_5 in WHERE clause showed the following details:
- With NULLIF, Item_func_eq::val_int() is called six times with the following arguments and results:
(Item_field,Item_field) res=0
|
(Item_field,Item_field) res=0
|
(Item_field Item_field) res=0
|
(Item_field Item_field) res=0
|
(Item_func_ifnull,Item_int) res=1 -- 0.0 vs 0.0
|
(Item_func_ifnull,Item_int) res=1 -- 0.0 vs 0.0
|
- With DB_NVL_NUMBER_5, Item_func_eq::val_int() is called four times with the following arguments and results:
(Item_func_sp,Item_cache_decimal) res=0 -- 4.0 vs 0.0
|
(Item_func_sp,Item_cache_decimal) res=1 -- 0.0 vs 0.0
|
(Item_field Item_field) res=0
|
(Item_field Item_field) res=0
|
Observation:
- Calculation of the ON and WHERE clauses is done in different order with NULLIF and DB_NVL_NUMBER_5
|
|
CREATE TABLE t (x INT);
|
INSERT INTO t VALUES(1),(NULL);
|
CREATE FUNCTION f (val INT, ret INT) RETURNS INT DETERMINISTIC RETURN IFNULL(val, ret);
|
|
|
SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
|
FROM t t1 LEFT JOIN t t2
|
ON t1.x = t2.x
|
WHERE IFNULL(t2.x,0)=0;
|
SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
|
FROM t t1 LEFT JOIN t t2
|
ON t1.x = t2.x
|
WHERE f(t2.x,0)=0;
|
|
drop function f;
|
drop table t;
|
|
|
During JOIN::optimize used tables bitmap in case of the function changes and start showing 2 tables instead of one.
|
|
equal condition made substitution of the function argument to t1.x
|
|
Substiyution is visible here:
explain extended
|
SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
|
FROM t t1 LEFT JOIN t t2
|
ON t1.x = t2.x
|
WHERE IFNULL(t2.x,0)=0;
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
|
Warnings:
|
Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` left join `test`.`t` `t2` on((`test`.`t2`.`x` = `test`.`t1`.`x`)) where (ifnull(`test`.`t2`.`x`,0) = 0)
|
explain extended
|
SELECT t1.x, t2.x, IFNULL(t2.x,0), f(t2.x,0)
|
FROM t t1 LEFT JOIN t t2
|
ON t1.x = t2.x
|
WHERE f(t2.x,0)=0;
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where
|
1 SIMPLE t2 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
|
Warnings:
|
Note 1003 select `test`.`t1`.`x` AS `x`,`test`.`t2`.`x` AS `x`,ifnull(`test`.`t2`.`x`,0) AS `IFNULL(t2.x,0)`,`f`(`test`.`t2`.`x`,0) AS `f(t2.x,0)` from `test`.`t` `t1` join `test`.`t` `t2` where ((`test`.`t2`.`x` = `test`.`t1`.`x`) and (`f`(`test`.`t1`.`x`,0) = 0))
|
|
|
in first case ON condition is not moved to JOIN::conds, in second case simplify_joins() made it.
|
|
Problem is that Item_func_isnotnull has very not_null_tables(), Item_func_isnotnull has usual for function function, it has nothing about semantic the function so can't build it correctly.
|
|
Above should be discussed...
|
|
I found that not_null_tables() used only for LEFT JOIN and also UDFs has this method which return 0, so for Item_func_sp it was just forgotten.
|
|
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.
—
|
|
github branch is bb-5.5-MDEV-11958
|
|
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.
|
|
Sanja,
See my observations in your mail-box.
|
|
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.
—
|