Openrefine: Add new "Coalesce" function to GREL

[ostephens]

From #820

Add a new GREL function of 'coalesce'. The function should operate as the COALESCE operator in the ANSI SQL-92 standard (https://docs.faircom.com/doc/sqlref/33405.htm). It should take as input one or more expressions or values, and return the first non-null value.

If there are no non-null values supplied, coalesce should return null.

For example:

coalesce(null, "string") -> "string"
coalesce(null, null, "string") -> "string"
coalesce(null, 1) -> 1
coalesce(null, [array]) -> [array]
coalesce(null) -> null

[thadguidry]

@ostephens as stated on your pull request... I'm backtracking now and thinking that the last one

coalesce(null) -> null

might or should perhaps be

coalesce(null) -> <blank>

This needs wider discussion and just feeling around with it I think from the community. I should not be the sole decider on this one.

[ostephens]

I'm inclined to keep coalesce(null) -> null because that keeps us in line with the SQL function and treating null and blank in a more relaxed way is partly what has led to the need for the coalesce function in the first place.

Very happy to get other input on it @ettorerizza @eximius313

[thadguidry]

@ostephens good point.

[ettorerizza]

@ostephens I'm trying to understand how to use this function. If you have a column containing given names and another with the names, the safe concatenation can be done with coalesce(cells.given_name.value, cells.name.value), that's it ?

I can't figure out what's my mistake.

[ostephens]

Hi @ettorerizza all coalesce does is find the first non-null value in a list, it doesn't do any concatenation. You can use this to fall back on an alternative string value or empty string when the value in the cell is null. So it's a helper function to avoid trying to concatenate null values, but doesn't reduce it to a single step.

So for example if you try, from the 'name' column the GREL

cells["given_name"].value + " " + value.coalesce("")

OR

cells["given_name"].value + " " + coalesce(value,"")

You should see

The question @thadguidry is asking is whether rather than require the blank string in there could we allow coalesce(value) to automatically fall back to an empty string if no second string is specified. I'm slightly against this as it contradicts how the coalesce function works in SQL where it falls back to null if no non-null value is available.

Changing the behaviour would allow for the slightly simpler GREL:

cells["given_name"].value + " " + value.coalesce()

But I don't think that's a big enough saving to divert from the SQL usage of coalesce.

You can also have a long list of values in the coalesce function and it will pick the first non-null one - so if you had data spread across several columns in theory you could include each column.value in the coalesce function like

cells["given_name"].value + " " + coalesce(value,cells["other_name_1"].value,cells["other_name_2"].value,"")

However - there is another problem with this - which I've reported against #820 (and #1036) - which is that this doesn't work because if cells["other_name_1"] (etc.) is null, then cells["other_name_1"].value gives an error rather than null - and so coalesce fails with an error in this scenario :((( But I think that needs it's own solution (I may have one) and needs to be separated out from the addition of the coalesce function, even though from a user perspective until we solve this other problem the value of coalesce is limited.

[ettorerizza]

Sorry @ostephens, I had followed the threads distractedly and I remained on the idea that the primary purpose of this function was to facilitate concatenation. But I see by reading the totality of the discussions that you went to a transposition of coalesce exactly as in SQL. In this case, indeed, it seems better to not disorient those who are already familiar with this function.

[eximius313]

I think that coalesce should take at least 2 arguments, but if it must be at least one, then I think coalesce(null) -> null should be correct behavior

[ostephens]

@eximius313 good point about at least 2 arguments - that would be in line with the ANSI SQL definition - I will implement

[ostephens]

@eximius313 PR updated to require at least 2 arguments

[ettorerizza]

@ostephens Would turning errors into null _only in the coalesce function_ not solve the problem?

In my example, I do not see any other case that can produce an error than having a nullmiddle_name - since dates, numbers and booleans are casted to strings.

In other words, this would be equivalent to this formula :

value + " " + coalesce(if(isError(cells.middle_name.value), null, cells.middle_name.value), "") + " " + cells.name.value

But I suppose we can be even more explicit and limit the transformation of errors to null only if the error is "cannot retrieve field from null" (since this type of error cannot exist in a function which consists precisely in managing the null values)

So:
coalesce(null, null) -> null
coalesce(Error: Cannot retrieve field from null, "string") -> "string"

[ostephens]

I’d need to have a look & think about this. Making the function treat errors in a specific way probably possible but making it treat only a specific error like that could be difficult.

[wetneb]

It's not clear to me that it is possible to catch errors selectively like this: when GREL functions are evaluated, their arguments are already evaluated (so if there was any error upstream, the downstream function is not evaluated at all). This is related to other issues, such as #1145: our boolean operations are not lazy (which is annoying)… To solve this properly, we would need to redesign the way GREL is evaluated.

[ostephens]

Going to close this with the merge of #1536. Unfortunately as @wetneb says the suggestion by @ettorerizza won't work as the error comes from upstream, so can't be caught by the coalesce function.

I've made a relevant suggestion for discussion at #1036