COALESCE expr in datafusion should perform lazy evaluation of the operands

[coderfender]

Describe the bug

We observed that datafusion-spark's coalesce should perform lazy evaluation of the operands from left to right similar to spark . Current implementation throws an exception in case any operand throws an error which is not in alignment with spark which does lazy evaluation to return first non NULL values

To Reproduce

select coalesce(100, 1/0) from tbl

Expected behavior

result : 100 but throws an exception

Additional context

No response

[coderfender]

cc : @andygrove , @kazuyukitanimura

[andygrove]

For reference, this is the behavior in Postgres:

postgres=# select coalesce(1, 1/0);
 coalesce 
----------
        1
(1 row)

postgres=# select coalesce(1/0, 1);
ERROR:  division by zero

This differs from DataFusion's behavior:

DataFusion CLI v48.0.0
> select coalesce(1, 1/0);

Arrow error: Divide by zero error

[andygrove]

@coderfender This issue is for DataFusion's coalesce expression and is not specific to datafusion-spark as far as I know.

[coderfender]

Ah that explains it (fixed the title) . Perhaps we are adhering to postgreSQL 's coalesce implementation ?

[coderfender]

I can work on this once the comet's side impl is fixed .

[alamb]

We merged a fix for this in the 51 release, but we have found as subtle problem so I am planning to revert it for 51 (the fix should appear in 52):

• [branch-51] Revert rewrite for coalesce, nvl and nvl2 simplification #18567