coalesce returning null rather than empty string

BIZ TMS RemoteDB
1

I have an issue with coalesce returning null rather than empty string when using TXDataset, if I test the same SQL statement with TFDQuery is works correctly and returns empty string '' (not null).
example
XDataset1.SQL.Add('select coalesce(list(TOUNIT),'''') as ITEMUNITLIST from ITEMUNITS where ITEMCODE = ''xx''');
XDataset1.Open;
if XDataset1.FieldByName('ITEMUNITLIST').IsNull then
showmessage('null')
else
showmessage('not null');

I could easily code around this in my application but we use a scripting engine for clients to do custom scripts and this issue came from one of those, so I really need to fix In our application rather than get clients to change their script code.
Thanks

2

Can you please be more specific as:

  1. The database structure
  2. What is "list"?
  3. The RDBMS you are using

Ideally, steps to reproduce.

3
  1. Don't think the structure matters here
  2. "List" is a Firebird aggregate function that concatenates non-NULL values from a group into a single string. Note: you can replicate the issue without using the list function, but you have separate null handling for blob and non blob fields in (RemoteDB.Client.Dataset.pas) so this is good for testing as the "list" function returns a blob field.
  3. I am using a Firebird 5 database
  4. Steps to reproduce, do a simple query 'select coalesce(your_string_field_that_is_null,'') from yourtable' using TXdataset the field is [null] (it should be an empty string) do the same SQL with TFDQuery the field is an empty string (correct)

I managed to make changes to fix local connection, but then had another issue specifically with remote, have now got that to work.
A quick summary of the areas that I changed to get this to work...
RemoteDB.Client.Dataset.pas FillRecordData, GetFieldData
RemoteDB.Server.Wrappers.pas FieldsToStream, ResultRowToStream
RemoteDB.Client.Database.pas TRemoteDBProvider, LoadRowData, IsNull, Close