ExpressionEqualsMaybeNull extension

[SteveAlexander]

I'm using the following extension in my code, as I kept forgetting that when I'm writing Dart SQL expressions I'm really writing SQL, and the rules for Dart's null as translated into SQL are not the same as the rules for NULL in SQL. Specifically around comparisons.

extension ExpressionEqualsMaybeNull on Expression {
  Expression<bool> equalsMaybeNull<T extends Object?>(T value) {
    return value == null ? this.isNull() : this.equals(value);
  }
}

as an alternative to writing conditional logic like this

    var value = whatever;
    var? someNullableValue = whatever;

    final whereCondition = (Something t) =>
        t.value.equals(value) &
        (someNullableValue == null
            ? t.other.isNull()
            : t.other.equals(someNullableValue));

it can be written like this

    var value = whatever;
    var? someNullableValue = whatever;

    final whereCondition = (Something t) =>
        t.value.equals(value) &
        t.other.equalsMaybeNull(someNullableValue);

I guess this applies to other kinds of comparisons too, but I haven't found the need for those yet.

Alternatively, perhaps Moor could (as a future breaking change) handle this implicitly. How often, even when writing SQL in SQL, do we actually mean to compare something nullable with = or some other operator?

[simolus3]

Thanks for the suggestion. I agree that something like this would be nice in general.

However, I'm not sure if the check you suggest is enough: It would cover value.equals(null), but then we should also make sure that nullableValue.equals(3) doesn't evaluate to NULL either. So in that case, we'd have to generate something like COALESCE(nullableValue == 3, FALSE) for every equals which might slow down queries (equals is used a lot internally, like for replacing rows with an update).

For that reason, I think an extra method is the better approach here, I'm just not sold on the name (since the special property of equalsMaybeNull is that it doesn't evaluate to null). I'll think about this some more.