Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.10, 10.1.13, 5.5(EOL), 10.0(EOL), 10.1(EOL)
-
None
-
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
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.
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 |
|
+------+
|
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.
The problem is also repeatable with MyISAM, with more than one records inserted:
returns empty set.