[Bug]: Coalesce doesn't work because it won't accept null values because of stringize...

[SlyDave]

What happened?

You can't actually pass the Coalesce function a null value, because of

TypeError  Tpetry\QueryExpressions\Function\Conditional\ManyArgumentsExpression::stringize():
Return value must be of type string|int|float, null returned.

The getValue of Coalesce calls getExpressions, which calls stringize, which doesn't accept or return nulls - to work around it not accepting nulls you can wrap the null in Expression, but then stringize crashes with the above noted error because it won't return a null.

How to reproduce the bug

Testing the intersection of 2 date ranges where either range could be open-ended ($end can be null and assignments.ends_at can be null)

$start = '2023-09-14';
$end = null;

Assignments::where($start, '<=', new \Tpetry\QueryExpressions\Function\Conditional\Coalesce(
[
    'assignments.ends_at', 
    new \Illuminate\Database\Query\Expression($start)
]))
->where(new \Tpetry\QueryExpressions\Function\Conditional\Coalesce(
[
    new \Illuminate\Database\Query\Expression($end),
    'assignments.starts_at'
]), '<=', 'assignments.starts_at')->get();

No work arounds, you have to not use the package for this and revert to using DB::Raw();

Package Version

0.7.0

PHP Version

8.1

Laravel Version

10.19

Which operating systems does with happen with?

Windows

Notes

No response

[tpetry]

Do you have an example why you want to pass NULL? It is automatically the result when not passed a non-null value. So passing NULL to it doesn‘t make sense for me.

[SlyDave]

To replicate:

// Intersection of 2 date ranges where either range could be open-ended
// (StartA <= Coalesce(EndB, StartA)) And (Coalesce(EndA, StartB) <= StartB)

Assignment::whereRaw('? <= COALESCE(ends_at, ?)', [$contract->starts_at, $contract->starts_at])
->whereRaw('COALESCE(?, starts_at) <= starts_at', [$contract->ends_at])
->get();

The non-bound ends_at (for the assignment) is fine as mysql handles that without issue, but you need to be able to pass in a null for the $contract->ends_at, the above works just fine because the binding is handled correctly when $contract->ends_at is null. This sadly isn't the case from what I can tell with the Coalesce function.

[tpetry]

You've been using the expression wrong.

Any non-expression passed to an expression is used as a column name. So you can't pass in $contract->starts_at to the constructor as it would be recognized as a column name.

The correct approach is using the Value class which supports nullable values:

$query->where(new Coalesce([new Value($contract->starts_at), 'starts_at']), '<=', 'starts_at');

[SlyDave]

ooooh, that explains so much. Thank you.