[MDEV-8740] Wrong result for SELECT..WHERE year_field=10 AND NULLIF(year_field,2011.1)='2011' Created: 2015-09-03  Updated: 2015-09-12  Resolved: 2015-09-10

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1
Fix Version/s: 10.1.8

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: propagation, upstream

Issue Links:
Blocks
blocks MDEV-8728 Fix a number of problems in equal fie... Closed
is blocked by MDEV-8785 Wrong results for EXPLAIN EXTENDED...... Closed

 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).

 Comments   
Comment by Alexander Barkov [ 2015-09-12 ]

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')) |
 
+-------+------+---------------------------------------------------------------------------

Generated at Thu Feb 08 07:29:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.