[MDEV-10236] Where expression with NOT function gives incorrect result - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1.10, 10.1.13, 5.5(EOL), 10.0(EOL), 10.1(EOL)
Fix Version/s: 10.1.15
Component/s: Optimizer
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;

Attachments

Activity

Ascending order - Click to sort in descending order

Dylan Su created issue - 2016-06-15 07:19

Valerii Kravchuk added a comment - 2016-06-15 08:03

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.

Valerii Kravchuk added a comment - 2016-06-15 08:03 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.

Valerii Kravchuk made changes - 2016-06-15 08:03

Field	Original Value	New Value
Status	Open [ 1 ]	Confirmed [ 10101 ]

Valerii Kravchuk made changes - 2016-06-15 08:04

Affects Version/s

10.1.13 [ 21803 ]

Valerii Kravchuk added a comment - 2016-06-15 08:08

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 |

+------+

Valerii Kravchuk added a comment - 2016-06-15 08:08 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 | +------+

Valerii Kravchuk made changes - 2016-06-15 09:32

Fix Version/s

10.1.15 [ 22018 ]

Valerii Kravchuk made changes - 2016-06-15 09:33

Assignee

Elena Stepanova [ elenst ]

Elena Stepanova added a comment - 2016-06-15 12:12

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.

Elena Stepanova added a comment - 2016-06-15 12:12 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.

Elena Stepanova made changes - 2016-06-15 12:12

Fix Version/s		10.1 [ 16100 ]
Fix Version/s		10.2 [ 14601 ]
Fix Version/s	10.1.15 [ 22018 ]
Affects Version/s		5.5 [ 15800 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.2 [ 14601 ]
Assignee	Elena Stepanova [ elenst ]	Alexander Barkov [ bar ]
Priority	Critical [ 2 ]	Major [ 3 ]

Rasmus Johansson (Inactive) made changes - 2016-06-22 07:58

Sprint

10.1.15 [ 75 ]

Rasmus Johansson (Inactive) made changes - 2016-06-22 07:58

Rank

Ranked higher

Alexander Barkov made changes - 2016-06-22 09:57

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Alexander Barkov added a comment - 2016-06-22 12:25

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.

Alexander Barkov added a comment - 2016-06-22 12:25 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.

Alexander Barkov made changes - 2016-06-22 12:31

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Alexander Barkov made changes - 2016-06-22 12:32

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2016-06-22 12:32

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

Sergei Golubchik made changes - 2016-06-26 19:27

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

Alexander Barkov made changes - 2016-06-27 08:02

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2016-06-27 08:02

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

Sergei Golubchik made changes - 2016-06-27 13:51

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

Alexander Barkov made changes - 2016-06-28 09:12

Component/s		OTHER [ 10125 ]
Fix Version/s		10.1.15 [ 22018 ]
Fix Version/s		10.2.1 [ 22012 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2016-06-29 20:10

Fix Version/s

10.2.1 [ 22012 ]

Sergei Golubchik made changes - 2016-06-29 20:10

Affects Version/s

10.2 [ 14601 ]

Sergei Golubchik made changes - 2016-07-04 08:20

Component/s		Optimizer [ 10200 ]
Component/s	OTHER [ 10125 ]

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

Workflow

MariaDB v3 [ 76015 ]

MariaDB v4 [ 150523 ]

People

Assignee:: Alexander Barkov

Reporter:: Dylan Su

Votes:: 1 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2016-06-15 07:19

Updated:: 2016-07-04 08:20

Resolved:: 2016-06-28 09:12

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