[Firebird-devel] Need for new COALESCE2 function?

[Leyne, S. <Se...@br...>]

All,

Recently we had a situation where the COALESCE function was failing on
VARCHAR fields.

Our investigation found that the function result was correct because the
fields contained an empty string (i.e. not NULL).  So, the problem was
with the data, which was quickly fixed and changes made to our
application to account for empty strings.

After working through the problem; a staff member suggested that it
would be a very good thing if the COALESCE function considered empty
strings as well as NULL values, since the way that this would need to be
handled at the database level is a little ugly.  i.e. Instead of a neat
COALESCE( Field1, Field2...) the logic would need to be CASE WHEN Field1
=3D '' THEN COALESCE( Field1, Field2) ELSE Field1 END.

Naturally, I pointed out that the COALESCE functionality is defined by
standard and that no such change would be made.

But as I thought about it, I came to think of a slightly different
approach...


What if there was a *new* COALESCE function (named COALESCE2, for
discussion) which provided the functionality required?


So, I thought I would solicit some comments about the need for a new
function.


To review, the current COALESCE overall functionality would be kept,
with the following additions:

- For VARCHAR fields, an empty string would be considered to be
equivalent to NULL for purposes of determining the value to be returned
by the function.

- For CHAR fields, if the field was equivalent to "spaces" (i.e. for
CHAR(10) then 10 Spaces) it would be considered to be equivalent to NULL
for purposes of determining the value to be returned by the function.


I look forward to all comments.


Sean