Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Won't Do
-
1.1.5
-
None
-
Single Node UM/PM installation in Centos.
Description
The "is null" as well "is not null" operators do not work with Boolean predicate expressions. It works in the select list. The same queries work in MariaDB server product. Here is the output of queries i tried:
--------------
DROP TABLE if exists t1
--------------
--------------
DROP TABLE if exists t2
--------------
--------------
CREATE TABLE t1 ( c1 int not null, c2 float, c3 char(10)) engine=columnstore
--------------
--------------
insert into t1 values(1,1.0, 'one'), (2,2.0, 'two'), (3, 3.1, 'three')
--------------
--------------
CREATE TABLE t2 ( c1 int not null, c2 float, c3 char(10)) engine=innodb
--------------
--------------
insert into t2 values(1,1.0, 'one'), (2,2.0, 'two'), (3, 3.1, 'three')
--------------
--------------
select c1, c2+5 from t1 where (case when c1 > 2 then 1 else null end)
--------------
---------------------+
c1 | c2+5 |
---------------------+
3 | 8.099999904632568 |
---------------------+
--------------
select c1, c2+5 from t1 where c1 > 2 is not null
--------------
ERROR 1815 (HY000) at line 16: Internal error: IDB-2030: Predicate and Logic operators can not be used where an expression is expected.
--------------
select c1, c2+5, (c1 > 1) is null from t1
--------------
-------------------------------------
c1 | c2+5 | (c1 > 1) is null |
-------------------------------------
1 | 6 | 0 |
2 | 7 | 0 |
3 | 8.099999904632568 | 0 |
-------------------------------------
--------------
select c1, c2+5 from t2 where (case when c1 > 2 then 1 else null end)
--------------
---------------------+
c1 | c2+5 |
---------------------+
3 | 8.099999904632568 |
---------------------+
--------------
select c1, c2+5 from t2 where c1 > 2 is not null
--------------
---------------------+
c1 | c2+5 |
---------------------+
1 | 6 |
2 | 7 |
3 | 8.099999904632568 |
---------------------+
I have attached my sql script.