slim bouguerra, that is correct. However we should adhere to the SQL standard and return what the user is asking for in the query, otherwise we might be creating an even bigger deficit.
To give an example, consider the filter that you specified in the test query:
\"product_id\" = cast(NULL as varchar)
That filter evaluates always to false in SQL, since comparing a column to NULL using the = operator returns UNKNOWN. However, with this fix, the filter is evaluated to true for those records for which \"product_id\" is empty. In another setting, if constant folding would kick in and fold the expression to false, we will return different results for otherwise equivalent queries.
IMO, if there is a limitation in the storage engine, we should expose this to the end user instead of making an assumption on the query, as maybe the user does not even know about this limitation in the first place. Or at least, as an alternative, this should be a configurable option.