[MDEV-9181] (NULLIF(count(table.col)), 0) gives wrong result on 10.1.x - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.1.8, 10.1(EOL)
Fix Version/s: 10.1.11
Component/s: Optimizer
Labels:
- regression
Environment:
openbsd, windows

Sprint:
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

Ascending order - Click to sort in descending order

sippanson bernt created issue - 2015-11-24 23:00

sippanson bernt added a comment - 2015-11-25 14:40

might add that the mediacenter Kodi uses nullif in its views, so quite many are affected by this.

sippanson bernt added a comment - 2015-11-25 14:40 might add that the mediacenter Kodi uses nullif in its views, so quite many are affected by this.

Sergei Golubchik made changes - 2015-11-26 00:53

Field	Original Value	New Value
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');	I have 4 rows in col1, yet maria 10.1 says on {code:sql} select nullif(count(`lame`.`col1`),0) from lame {code} 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 {code:sql} 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'); {code}

Elena Stepanova made changes - 2015-11-26 19:44

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova added a comment - 2015-11-26 23:12

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.

Elena Stepanova added a comment - 2015-11-26 23:12 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.

Elena Stepanova made changes - 2015-11-26 23:12

Component/s		Optimizer [ 10200 ]
Component/s	OTHER [ 10125 ]
Fix Version/s		10.1 [ 16100 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s	10.1.9 [ 20301 ]
Assignee		Alexander Barkov [ bar ]
Labels		regression

Sergei Golubchik made changes - 2015-12-15 22:27

Sprint

10.1.10 [ 24 ]

Alexander Barkov added a comment - 2015-12-18 05:52

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.

Alexander Barkov added a comment - 2015-12-18 05:52 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.

Alexander Barkov added a comment - 2015-12-18 05:55 - edited

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    |

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

Alexander Barkov added a comment - 2015-12-18 05:55 - edited 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 | +------+------+------+

Alexander Barkov added a comment - 2015-12-18 06:41 - edited

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 |

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

Alexander Barkov added a comment - 2015-12-18 06:41 - edited 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 | +--------+---------+--------+

Alexander Barkov added a comment - 2015-12-18 07:02

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.

Alexander Barkov added a comment - 2015-12-18 07:02 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.

Alexander Barkov made changes - 2015-12-18 10:33

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2015-12-18 13:05

Assignee	Alexander Barkov [ bar ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2015-12-21 14:32

Summary

(NULLIF(table.col)0) gives wrong result on 10.1.x

(NULLIF(count(table.col)), 0) gives wrong result on 10.1.x

Sergei Golubchik made changes - 2015-12-24 11:39

Rank

Ranked higher

Rasmus Johansson (Inactive) made changes - 2016-01-21 12:08

Sprint

10.1.10 [ 24 ]

10.1.10, 10.1.11 [ 24, 30 ]

Rasmus Johansson (Inactive) made changes - 2016-01-21 12:08

Rank

Ranked lower

Sergei Golubchik made changes - 2016-01-21 14:26

Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexander Barkov made changes - 2016-01-26 13:31

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Alexander Barkov added a comment - 2016-01-26 17:48

Behaviour does not depend on the engine.
The same problem is demonstrated with ENGINE=MyISAM.

Alexander Barkov added a comment - 2016-01-26 17:48 Behaviour does not depend on the engine. The same problem is demonstrated with ENGINE=MyISAM.

Alexander Barkov made changes - 2016-01-26 18:52

Assignee	Alexander Barkov [ bar ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik added a comment - 2016-01-28 11:18

ok to push, thanks!

Sergei Golubchik added a comment - 2016-01-28 11:18 ok to push, thanks!

Sergei Golubchik made changes - 2016-01-28 11:18

Assignee	Sergei Golubchik [ serg ]	Alexander Barkov [ bar ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Alexander Barkov made changes - 2016-01-28 12:00

Fix Version/s		10.1.11 [ 21202 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

MT made changes - 2016-02-29 00:13

Comment

[ After what took almost a year to resolve, the latest update reverts the fix. ]

MT added a comment - 2016-02-29 00:14

This appears to have resurfaced with mysql Ver 15.1 Distrib 10.1.12-MariaDB

MT added a comment - 2016-02-29 00:14 This appears to have resurfaced with mysql Ver 15.1 Distrib 10.1.12-MariaDB

Elena Stepanova added a comment - 2016-02-29 00:35

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 Stepanova added a comment - 2016-02-29 00:35 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.

MT added a comment - 2016-02-29 22:41

@Elena apologies, you are indeed correct. After a bit more investigating it is 9637 already on the radar.

MT added a comment - 2016-02-29 22:41 @Elena apologies, you are indeed correct. After a bit more investigating it is 9637 already on the radar.

Sergei Golubchik made changes - 2021-12-06 21:42

Workflow

MariaDB v3 [ 72727 ]

MariaDB v4 [ 149862 ]

People

Assignee:: Alexander Barkov

Reporter:: sippanson bernt

Votes:: 6 Vote for this issue

Watchers:: 12 Start watching this issue

Dates

Created:: 2015-11-24 23:00

Updated:: 2016-02-29 22:41

Resolved:: 2016-01-28 12:00

Git Integration

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

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration