|
might add that the mediacenter Kodi uses nullif in its views, so quite many are affected by this.
|
|
Thanks for the report.
The problem appeared in 10.1.8 with the following commit:
commit 4aebba3aeba2d413268455c3c8c7cbfd04e2f94f
|
Author: Alexander Barkov <bar@mariadb.org>
|
Date: Thu Sep 10 17:13:35 2015 +0400
|
|
MDEV-8740 Wrong result for SELECT..WHERE year_field=10 AND NULLIF(year_field,2011.1)='2011'
|
MDEV-8754 Wrong result for SELECT..WHERE year_field=2020 AND NULLIF(year_field,2010)='2020'
|
Problems:
|
1. Item_func_nullif stored a copy of args[0] in a private member m_args0_copy,
|
which was invisible for the inherited Item_func menthods, like
|
update_used_tables(). As a result, after equal field propagation
|
things like Item_func_nullif::const_item() could return wrong result
|
and a non-constant NULLIF() was erroneously treated as a constant
|
at optimize_cond() time.
|
Solution: removing m_args0_copy and storing the return value item
|
in args[2] instead.
|
2. Equal field propagation did not work well for Item_fun_nullif.
|
Solution: using ANY_SUBST for args[0] and args[1], as they are in
|
comparison, and IDENTITY_SUBST for args[2], as it's not in comparison.
|
|
|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE IF NOT EXISTS t1 (
|
c1 varchar(50) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
INSERT INTO t1 (c1) VALUES
|
('hello'),
|
('hello\r\n'),
|
('hello'),
|
('hello');
|
|
SELECT NULLIF(COUNT(c1),0) FROM t1;
|
returns 7 in the latest MariaDB versions, and 4 in earlier versions.
|
|
Note, the equivalent CASE still returns a correct result:
MariaDB [test]> SELECT NULLIF(COUNT(c1),0), CASE WHEN COUNT(c1)=0 THEN NULL ELSE COUNT(c1) END FROM t1;
|
+---------------------+----------------------------------------------------+
|
| NULLIF(COUNT(c1),0) | CASE WHEN COUNT(c1)=0 THEN NULL ELSE COUNT(c1) END |
|
+---------------------+----------------------------------------------------+
|
| 7 | 4 |
|
+---------------------+----------------------------------------------------+
|
With DISTINCT both NULLIF and CASE return the same result:
MariaDB [test]> SELECT NULLIF(COUNT(DISTINCT c1),0), CASE WHEN COUNT(DISTINCT c1)=0 THEN NULL ELSE COUNT(DISTINCT c1) END FROM t1;
|
+------------------------------+----------------------------------------------------------------------+
|
| NULLIF(COUNT(DISTINCT c1),0) | CASE WHEN COUNT(DISTINCT c1)=0 THEN NULL ELSE COUNT(DISTINCT c1) END |
|
+------------------------------+----------------------------------------------------------------------+
|
| 2 | 2 |
|
+------------------------------+----------------------------------------------------------------------+
|
If I wrap COUNT(c1) into some expression, it also returns a good result:
MariaDB [test]> SELECT NULLIF(COUNT(c1)+0,0) AS c1,NULLIF(CAST(COUNT(c1) AS SIGNED),0) AS c2,NULLIF(CONCAT(COUNT(c1)),0) AS c3 FROM t1;
|
+------+------+------+
|
| c1 | c2 | c3 |
|
+------+------+------+
|
| 4 | 4 | 4 |
|
+------+------+------+
|
|
|
|
The same problem is repeatable with AVG:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (c1 INT DEFAULT NULL);
|
INSERT INTO t1 (c1) VALUES (1),(2),(3),(4);
|
SELECT NULLIF(AVG(c1),0),CASE WHEN AVG(c1)=0 THEN NULL ELSE AVG(c1) END FROM t1;
|
returns
+-------------------+------------------------------------------------+
|
| NULLIF(AVG(c1),0) | CASE WHEN AVG(c1)=0 THEN NULL ELSE AVG(c1) END |
|
+-------------------+------------------------------------------------+
|
| 2.7143 | 2.5000 |
|
+-------------------+------------------------------------------------+
|
Earlier versions correctly return 2.5000 for both expressions.
Similar to COUNT, if AVG is wrapped into an expression, it works fine:
MariaDB [test]> SELECT NULLIF(AVG(c1)+0,0) AS c1,NULLIF(CAST(AVG(c1) AS DECIMAL(10,5)),0) AS c2,NULLIF(CONCAT(AVG(c1)),0) AS c3 FROM t1;
|
+--------+---------+--------+
|
| c1 | c2 | c3 |
|
+--------+---------+--------+
|
| 2.5000 | 2.50000 | 2.5000 |
|
+--------+---------+--------+
|
|
|
The problems is also repeatable with GROUP BY:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE IF NOT EXISTS t1 (
|
id INT NOT NULL,
|
c1 INT DEFAULT NULL
|
);
|
INSERT INTO t1 VALUES (1,1),(1,2),(2,3),(2,4);
|
SELECT NULLIF(COUNT(c1),0) AS c1,NULLIF(COUNT(c1)+0,0) AS c1_wrapped,CASE WHEN COUNT(c1) IS NULL THEN 0 ELSE COUNT(c1) END AS c1_case FROM t1 GROUP BY id;
|
returns
+------+------------+---------+
|
| c1 | c1_wrapped | c1_case |
|
+------+------------+---------+
|
| 3 | 2 | 2 |
|
| 3 | 2 | 2 |
|
+------+------------+---------+
|
Earlier versions return 2 in all columns and rows.
|
|
Behaviour does not depend on the engine.
The same problem is demonstrated with ENGINE=MyISAM.
|
|
ok to push, thanks!
|
|
This appears to have resurfaced with mysql Ver 15.1 Distrib 10.1.12-MariaDB
|
|
javamarket,
I've just tried the test case from the description, and it seems to work okay, so it's not exactly resurfaced.
We do have a problem in 10.1.12, MDEV-9637, it's a bit different from this one; and if you have another test case, please paste it as well.
|
|
@Elena apologies, you are indeed correct. After a bit more investigating it is 9637 already on the radar.
|