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
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 |
|
+------+
|
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.
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.
I can reproduce this with 10.1.13 also:
C:\Program Files (x86)\MariaDB 10.1\bin>mysql -uroot -proot -P3316 testWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 2Server version: 10.1.13-MariaDB mariadb.org binary distributionCopyright (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 notnull;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)) isnot 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 | rows | 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: NoteCode: 1003Message: select `test`.`t1`.`c1` AS `c1`,(`test`.`t1`.`c1` is not null) AS `c1 is not null`,(((`test`.`t1`.`c1` is not null) >= (not(1))) is not null) AS `a` from `test`.`t1` where (((`test`.`t1`.`c1` is not null) >= <cache>((not(1)))) is not 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.