[MDEV-8740] Wrong result for SELECT..WHERE year_field=10 AND NULLIF(year_field,2011.1)='2011' - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1
Fix Version/s: 10.1.8
Component/s: Optimizer
Labels:
- propagation
- upstream

Description

This script:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a YEAR);
INSERT INTO t1 VALUES (2010),(2011);
SELECT a=10 AND NULLIF(a,2011.1)='2011' AS cond FROM t1;

returns

+------+
| cond |
+------+
|    0 |
|    0 |
+------+

Now if I put the same expression into WHERE:

SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011';

it erroneously returns one row:

+------+
| a    |
+------+
| 2010 |
+------+

The expected result is to have the expression evaluate into the same result in the SELECT list and in WHERE.

The problem is that Item_func_nullif::const_item() returns true and its val_real() is called from eval_const_cond().
In fact, it has constant items in args[0] and args[1]:

(gdb) p args[0]->const_item()
$14 = true
(gdb) p args[1]->const_item()
$15 = true

but the returned value referenced by m_args0_copy is not a constant item:

(gdb) p this->m_args0_copy
$17 = (Item_field *) 0x7fff98001668

The return argument should probably be stored in arg[2] instead, so the standard Item_func methods can see it and update Used_tables_cache taking into account the return value (not only the compared values).

Gliffy Diagrams

Attachments

Issue Links

blocks

MDEV-8728 Fix a number of problems in equal field and equal expression propagation

Closed

is blocked by

MDEV-8785 Wrong results for EXPLAIN EXTENDED...WHERE NULLIF(latin1_col, _utf8'a' COLLATE utf8_bin) IS NOT NULL

Closed

Activity

Hide

Permalink

Alexander Barkov added a comment - 2015-09-12 23:23

MySQL-5.7.8 seems to return a correct result:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a YEAR);
INSERT INTO t1 VALUES (2010),(2011);
SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011';
SHOW WARNINGS;

but the EXPLAIN output is not really correct:

+---------+------+---------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                         |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                                               |
| Note    | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and (nullif(2010,2011) = '2011')) |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------+

MariaDB returns a more correct result:

+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                   |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and ((case when 2010 = 2011 then NULL else `test`.`t1`.`a` end) = '2011')) |
+-------+------+---------------------------------------------------------------------------

Show

Alexander Barkov added a comment - 2015-09-12 23:23 MySQL-5.7.8 seems to return a correct result: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a YEAR); INSERT INTO t1 VALUES (2010),(2011); SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011'; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10 AND NULLIF(a,2011.1)='2011'; SHOW WARNINGS; but the EXPLAIN output is not really correct: +---------+------+---------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release. | | Note | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and (nullif(2010,2011) = '2011')) | +---------+------+---------------------------------------------------------------------------------------------------------------------------------+ MariaDB returns a more correct result: +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = 2010) and ((case when 2010 = 2011 then NULL else `test`.`t1`.`a` end) = '2011')) | +-------+------+---------------------------------------------------------------------------

Wrong result for SELECT..WHERE year_field=10 AND NULLIF(year_field,2011.1)='2011'

Details

Description

Gliffy Diagrams

Attachments

Issue Links

Activity

People

Dates

Agile