Skip to content

ILIKE string operator not working for NULL literal (SQLancer) #11872

@2010YOUY01

Description

@2010YOUY01

Describe the bug

ILIKE string operator is not working for NULL literal, see reproducer in datafusion-cli

DataFusion CLI v40.0.0
> select ((NULL) NOT ILIKE (NULL));
Arrow error: Invalid argument error: Invalid string operation: Null NILIKE Null

However, the same query is supported if null is a value within the table, it's better to keep the behaviour consistent

> create table t1(v1 text, v2 text);
0 row(s) fetched.
Elapsed 0.020 seconds.

> insert into t1 values ('foo', 'foo'), (null, null), ('foo', 'bar');
+-------+
| count |
+-------+
| 3     |
+-------+
1 row(s) fetched.
Elapsed 0.031 seconds.

> select v1 not ilike v2 from t1;
+-----------------------+
| t1.v1 NOT ILIKE t1.v2 |
+-----------------------+
| false                 |
|                       |
| true                  |
+-----------------------+
3 row(s) fetched.
Elapsed 0.013 seconds.

To Reproduce

No response

Expected behavior

No response

Additional context

Found by SQLancer #11030

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions