[MDEV-10236] Where expression with NOT function gives incorrect result Created: 2016-06-15  Updated: 2016-07-04  Resolved: 2016-06-28

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1.10, 10.1.13, 5.5, 10.0, 10.1
Fix Version/s: 10.1.15

Type: Bug Priority: Major
Reporter: Dylan Su Assignee: Alexander Barkov
Resolution: Fixed Votes: 1
Labels: None

Sprint: 10.1.15

 Description   

Output:
===

mysql> create table t1(c1 int);
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
mysql> insert into t1 values(1);
 
elect *Query OK, 1 row affected (0.00 sec)
 
 
 
mysql> select * from t1 where ( (c1 is not null) >= (not true) ) is not null;
 
Empty set (0.00 sec)
 
 
 
mysql> select version();
 
+---------------------+
 
| version()           |
 
+---------------------+
 
| 10.1.10-MariaDB-log |
 
+---------------------+
 
1 row in set (0.00 sec)

Problem:
===
Where condition is expected to be true and rows expected.

Recreate:
===

drop table t1;
 
create table t1(c1 int);
 
insert into t1 values(1);
 
select * from t1 where ( (c1 is not null) >= (not true) ) is not null;
 
 



 Comments   
Comment by Valerii Kravchuk [ 2016-06-15 ]

I can reproduce this with 10.1.13 also:

C:\Program Files (x86)\MariaDB 10.1\bin>mysql -uroot -proot -P3316 test
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 2
 
Server version: 10.1.13-MariaDB mariadb.org binary distribution
 
 
 
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [test]> drop table t1;
 
Query OK, 0 rows affected (0.47 sec)
 
 
 
MariaDB [test]> create table t1(c1 int);
 
Query OK, 0 rows affected (0.19 sec)
 
 
 
MariaDB [test]> insert into t1 values(1);
 
Query OK, 1 row affected (0.14 sec)
 
 
 
MariaDB [test]> select * from t1 where ( (c1 is not null) >= (not true) ) is not
 
 null;
 
Empty set (0.08 sec)
 
 
 
MariaDB [test]> select c1, c1 is not null, not true, ((c1 is not null) >= (not true)) as a from t1;
 
+------+----------------+----------+---+
 
| c1   | c1 is not null | not true | a |
 
+------+----------------+----------+---+
 
|    1 |              1 |        0 | 1 |
 
+------+----------------+----------+---+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select c1, c1 is not null, not true, ((c1 is not null) >= (not true)) is not null as a from t1;
 
+------+----------------+----------+---+
 
| c1   | c1 is not null | not true | a |
 
+------+----------------+----------+---+
 
|    1 |              1 |        0 | 1 |
 
+------+----------------+----------+---+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select c1, c1 is not null, (((c1 is not null) >= (not true)) is
 
not null) as a from t1 where ( (c1 is not null) >= (not true) ) is not null;
 
Empty set (0.00 sec)
 
 
 
MariaDB [test]> explain extended select c1, c1 is not null, (((c1 is not null) >
 
= (not true)) is not null) as a from t1 where ( (c1 is not null) >= (not true) )
 
 is not null;
 
+------+-------------+-------+------+---------------+------+---------+------+---
 
---+----------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | ro
 
ws | filtered | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+------+---
 
---+----------+-------------+
 
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |
 
 1 |   100.00 | Using where |
 
+------+-------------+-------+------+---------------+------+---------+------+---
 
---+----------+-------------+
 
1 row in set, 1 warning (0.06 sec)
 
 
 
MariaDB [test]> show warnings\G
 
*************************** 1. row ***************************
 
  Level: Note
 
   Code: 1003
 
Message: select `test`.`t1`.`c1` AS `c1`,(`test`.`t1`.`c1` is not null) AS `c1 i
 
s not null`,(((`test`.`t1`.`c1` is not null) >= (not(1))) is not null) AS `a` fr
 
om `test`.`t1` where (((`test`.`t1`.`c1` is not null) >= <cache>((not(1)))) is n
 
ot null)
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select 1 is not null;
 
+---------------+
 
| 1 is not null |
 
+---------------+
 
|             1 |
 
+---------------+
 
1 row in set (0.00 sec)

So, it seems expression is evaluated differently somehow when it's in WHERE clause comparing to it's value in SELECT clause.

Comment by Valerii Kravchuk [ 2016-06-15 ]

With Oracle's MySQL 5.6.23 we have correct result:

C:\Program Files (x86)\MariaDB 10.1\bin>mysql -uroot -proot -P3314 test
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MySQL connection id is 1
 
Server version: 5.6.23-log MySQL Community Server (GPL)
 
 
 
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MySQL [test]> drop table t1;
 
Query OK, 0 rows affected (0.22 sec)
 
 
 
MySQL [test]> create table t1(c1 int);
 
Query OK, 0 rows affected (0.03 sec)
 
 
 
MySQL [test]> insert into t1 values(1);
 
Query OK, 1 row affected (0.03 sec)
 
 
 
MySQL [test]> select * from t1 where ( (c1 is not null) >= (not true) ) is not null;
 
+------+
 
| c1   |
 
+------+
 
|    1 |
 
+------+

Comment by Elena Stepanova [ 2016-06-15 ]

Importantly, in order to reproduce the problem, the table needs to be InnoDB (not MyISAM).

drop table if exists t1;
 
create table t1(c1 int) engine=InnoDB;
 
insert into t1 values(1);
 
select * from t1 where ( (c1 is not null) >= (not true) ) is not null;

bar, please take a look, maybe it falls into your area.
Please feel free to modify 'Fix version/s' field as you like.

Comment by Alexander Barkov [ 2016-06-22 ]

The problem is also repeatable with MyISAM, with more than one records inserted:

drop table if exists t1;
 
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (1),(2),(3);
 
SELECT * FROM t1 WHERE ((c1 IS NOT NULL) >= (NOT TRUE)) IS NOT NULL;

returns empty set.

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