Possible bug when psSQLFilter is combined with DF_FILE_SQL_FILTER

[Alberto Gutiérrez Arroyo]

Hello, I have detected a bug which is not new. It affects older DataFlex versions. If you set a SQL filter in a DataDictionary (pbUseDDSQLFilters enabled and psSQLFilter set) and you have a global SQL filter, the global SQL Filter might be bypassed. This might be quite bad specially if you use the global SQL Filter for user rights/multi tenancy/...

// Global SQL Filter code
Set_Attribute DF_FILE_SQL_FILTER of iTable to "<SQL Filter>"
Set_Attribute DF_FILE_SQL_FILTER_EQ of iTable to"<SQL Filter>"
Set_Attribute DF_FILE_SQL_FILTER_ACTIVE of iTable to"<SQL Filter>"

// DataDictionary filter that breaks the global filter
Set psSQLFilter to "FIELD >=0 OR FIELD<=10"

This breaks the global SQL filter. My guess is the psSQLFilter is appended to the global filter by the framework. It should always add "(" and ")" to the psSQLFilter when it has any content, otherwise you might find unexpedted results.
This is easily fixed changing the line

Set psSQLFilter to "FIELD >=0 OR FIELD<=10"

to

Set psSQLFilter to "(FIELD >=0 OR FIELD<=10)"

but this should be handled correctly by the framework in my opinion.

Note: This already happened in DF 19.1 and is still present in newer DataFlex versions, including DF23.

[Peter Bragg]

Are you using pbApplyGlobalSQLFilters here? This will control whether or not any global SQL filters will be combined with local DDO ones. If this is False, then any global ones will indeed be overwritten (or rather, not applied).

[Alberto Gutiérrez Arroyo]

Peter: Indeed, I have pbApplyGlobalSQLFilters and pbUseDDSQLFilters enabled. The filter is applied but it breaks the global SQL filter. As I said, parenthesis should be added internally or at least it should be documented in the psSQLFilter DataDictionary page.

[Vincent Oorsprong]

Alberto,

I am not sure if the framework should interfer with the filter conditions you wrote in the code. If it would results may be different and unwanted as well.

[Alberto Gutiérrez Arroyo]

Vincent,
I agree, changing this might be worse for existing applications. However, what about adding some lines in the psSQLFilter help page explaining this? Just a warning, something like "If global SQL Filters are also applied you should be careful with the psSQLFilter value. While using an OR clause it is advised to add parenthesis to the SQL filter...". It might help someone in the future.

[Samuel Pizarro]

+1

a note on both pages would be very welcome! In the global and in the local filters

[Dennis Piccioni]

Hi Samuel,

done.

[Samuel Pizarro]

Thanks Dennis !

[Dennis Piccioni]

Hi Alberto,

done.

[Alberto Gutiérrez Arroyo]

Thanks Dennis!