Skip to content

Incorrect CASE WHEN + ELSE NULL behavior #13885

Closed

Description

@richox

Describe the bug

some queries containing case when .. ELSE NULL end produces incorrect results.

To Reproduce

preparing test table:

$ datafusion-cli
DataFusion CLI v43.0.0
> create table t1(s string);
0 row(s) fetched.
Elapsed 0.003 seconds.

> insert t1 (s) values ('aaa'), ('bbb');
+-------+
| count |
+-------+
| 2     |
+-------+
1 row(s) fetched.
Elapsed 0.002 seconds.

> select * from t1;
+-----+
| s   |
+-----+
| aaa |
| bbb |
+-----+
2 row(s) fetched.
Elapsed 0.001 seconds.

query:

> select case when (NULL and (s = 'aaa')) then 'unreachable!' else null end from t1;
+-------------------------------------------------------------------------------+
| CASE WHEN NULL AND t1.s = Utf8("aaa") THEN Utf8("unreachable!") ELSE NULL END |
+-------------------------------------------------------------------------------+
|                                                                               |
|                                                                               |
+-------------------------------------------------------------------------------+
2 row(s) fetched.
Elapsed 0.002 seconds.

> select case when (NULL and (s = 'aaa')) then s else 'always here!' end from t1;
+-------------------------------------------------------------------------------+
| CASE WHEN NULL AND t1.s = Utf8("aaa") THEN t1.s ELSE Utf8("always here!") END |
+-------------------------------------------------------------------------------+
| always here!                                                                  |
| always here!                                                                  |
+-------------------------------------------------------------------------------+
2 row(s) fetched.
Elapsed 0.001 seconds.

> select case when (NULL and (s = 'aaa')) then s else null end from t1;
+---------------------------------------------------------------+
| CASE WHEN NULL AND t1.s = Utf8("aaa") THEN t1.s ELSE NULL END |
+---------------------------------------------------------------+
| aaa                                                           | <- BUGGY, should be NULL
|                                                               |
+---------------------------------------------------------------+
2 row(s) fetched.
Elapsed 0.001 seconds.

Expected behavior

select case when (NULL and (s = 'aaa')) then s else null end from t1;

the above query should always output nulls, since the only WHEN condition is always null (not true)

Additional context

No response

Activity

  1. richox commented on Dec 23, 2024

    @richox
    ContributorAuthor

    this happens in case_column_or_null() where nullable when-conditions are not correctly handled.

  2. Omega359 commented on Dec 23, 2024

    @Omega359
    Contributor

    possibly related: #13779

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions