[MCOL-1504] "is null" operator reports an error for a Created: 2018-06-25  Updated: 2022-11-05  Resolved: 2022-11-05

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.1.5
Fix Version/s: Icebox

Type: Bug Priority: Minor
Reporter: Ravi Prakash (Inactive) Assignee: Unassigned
Resolution: Won't Do Votes: 0
Labels: None
Environment:

Single Node UM/PM installation in Centos.

Attachments: File test3.sql    

 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.

 Comments   
Comment by Todd Stoffel (Inactive) [ 2022-11-05 ]

This item is being closed because it was well passed the expiration date with no activity. If you suspect this was done in error please create a new ticket.

Generated at Thu Feb 08 02:29:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.