Search code examples
delphimacrosfiredac

In Delphi Rio (10.3.3) fdQuery does not correctly translate conditional function 'iif' when a macro is NULL

I have the following SQL command to run a query with a TFDQuery using PostgreSQL as the database:

fdqr.SQL.Text: = 
  'select * from (values (current_date-1), (current_date), (current_date + 1)) as t (datetest) ' +
  '{iif (! Datevalue, where datetest =! Datevalue)}';

When I used the Clear method to clear the macro value, I expected the script to be interpreted so that the filter would be eliminated, but it wasn't.

fdqr.MacroByName('datevalue').Clear;
fdqr.Open;

Resulting database log:

select * from (values(current_date-1), (current_date), (current_date+1)) as t(datetest) 
where datetest = NULL

However, if you include an assignment via AsRaw, it works as expected, even if the previous value was '' (empty string) and IsNull is True.

fdqr.MacroByName('datevalue').Clear;
{ at this point IsNull = True and AsRaw is '' }
fdqr.MacroByName('datevalue').AsRaw := '';
fdqr.Open;

Resulting database log:

select * from (values(current_date-1), (current_date), (current_date+1)) as t(datetest)

Is this a FireDAC bug or a feature that I don't understand correctly?

Solution

  • In order to apply substitution of {IIF(…)} or {IF}…{FI} based on a macro variable, the variable value must not be empty. That is what documentation for Conditional Substitution says. It literally means that the substitution is applied if the macro variable expands to a non-empty string. This fact can be verified by inspecting FireDAC source code, specifically method TFDPhysConnectionMetadata.TranslateEscapeSequence in unit FireDAC.Phys.Meta. In case for eskIF you can find:

    s := Trim(ASeq.FArgs[0]);
…
else if s <> '' then
  Result := 'True';

    Let's see what happens when you Clear the macro variable. Its Value is set to Null and its DataType is set to mdUnknown. In this case the variable is expanded to NULL literal by the preprocessor, which is not an empty string. The value of AsRaw is irrelevant, because preprocessor uses SQL property for substitution.

    There seems to be no other way to expand macro variable to an empty string than setting AsRaw := ''. In this case its DataType is set to mdRaw and the value SQL property is equal to AsRaw.

    I'm not sure whether this behaviour is a bug or feature, but changing it could break exiting code bases, so I don't expect this to be ever fixed. I'd rather say that it eventually became a feature.

    Apart from the above, you can avoid macros by using simple parameters:

    select * from (values(current_date-1), (current_date), (current_date+1)) as t(datetest) 
where :Datevalue is null or datetest = :Datevalue

    This way you can clear or set the value of Datevalue parameter as you would expect:

    { to bind null value }
fdqr.ParamByName('Datevalue').Clear;
{ to bind some value }
fdqr.ParamByName('Datevalue').AsDate := Today;