Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.1.8, 10.1(EOL)
-
openbsd, windows
-
10.1.10, 10.1.11
Description
I have 4 rows in col1, yet maria 10.1 says on
select nullif(count(`lame`.`col1`),0) from lame |
that I have 7.
maria 10.0 and mysql gives 4 here.
changing to ifnull instead of nullif works fine.
To recreate this simple bug just use that line on anything you have or to recreate my testdb see below
CREATE TABLE IF NOT EXISTS `lame` ( |
`col1` varchar(50) DEFAULT NULL |
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
|
/*!40000 ALTER TABLE `lame` DISABLE KEYS */; |
INSERT INTO `lame` (`col1`) VALUES |
('hello'), |
('hello\r\n'), |
('hello'), |
('hello'); |
Attachments
Activity
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.
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.
might add that the mediacenter Kodi uses nullif in its views, so quite many are affected by this.