I spent some more time thinking about my previous comments, as shortly after sending them I realized that it isn't as simple as I originally thought. I agree that a complete solution requires semantic analysis and is unrealistic.
I ended up with a prototype that might slightly improve how these scenarios are handled. I attached two small patches containing it. These should handle most incorrect result issues, on the other hand they might result in some missed pushdowns. However, unless I'm mistaken, common cases should still be pushed down correctly as before? > The difficulty is that cathing such cases requires semantic analysis > of the function > ... > (e.g., f exposes bytewise differences > that record_ops = considers equal) Instead of a full semantic analysis, wouldn't it be a good heuristic to rely on btree's equalimage, or something similar? If there's a wrapped non-image-faithful grouping column in a having clause, we don't push it down, as we can't be sure about its use. This works correctly for the above examples, but it still results in some missed opportunities, for example `GROUP BY n HAVING abs(n) > 100`, where n is a numeric column. There's also the question of collations which I simply ignored in this poc. > For operators not in btree/hash opfamily, we have no way to know their > equivalence relation, so we can't prove a conflict. Yes, my suggestion was to instead of saying "if we can't provide a conflict, it's safe" we could say "if we can't provide that it's safe, it is a conflict". - if (!OidIsValid(opno) || !op_is_safe_index_member(opno)) + if (!OidIsValid(opno) || get_op_rettype(opno) != BOOLOID) By itself, this is way too eager and rejects cases it shouldn't, but by depending on the same equalimage logic as above, that seems (mostly) fixable to me? (my original second example was incorrect, the language sql operator get's inlined so it only showcases the first issue differently. a proper example for that requires a plpgsql or other non sql function)
nocfbot-0001-Catch-grouping-columns-wrapped-in-an-express.patch
Description: Binary data
nocfbot-0002-Catch-finer-comparison-operators-outside-any.patch
Description: Binary data
