Hello, We are using PostgreSQL to execute some SQL scripts "auto translated" from HIVE QL, where the join operator "<=>" is heavily used. The semantic same operator in PostgreSQL is "IS NOT DISTINCT FROM".
However, we found when "IS NOT DISTINCT FROM" is used in joins, only nested loop plan can be generated, which is confirmed here https://www.postgresql.org/message-id/13950.1511879733%40sss.pgh.pa.us and here https://postgrespro.com/list/thread-id/2059856 . In another discussion someone suggests using coalesce(...) to replace NULLs to some special value, but in similar situation as in that thread, we have no reliable way to conclude a special value for any expression. So I hacked the PG10 code to support using "IS NOT DISTINCT FROM" in hash and merge joins (not touching the indexes). It works in our environment, but I want to know if my approach is making sense, or is going to make damage. There are 6 kinds of changes, and to be honest, none of them I am confident is doing in correct way...so please advise: - I do this by first reversing the meaning of DistinctExpr, from "IS DISTINCT FROM" to "IS NOT DISTINCT FROM", which will be simpler to process in joins, because "IS NOT DISTINCT FROM" is closer to "=". (backend/parser/parse_expr.c, backend/utils/adt/ruleutils.c) - The execution logic of DistinctExpr internally already reverts the result, because above change cancels it out, I revert it back. (backend/executor/execExprInterp.c, backend/optimizer/path/clausesel.c) - In hash joins, I need to tell the executor that "NULL matches NULL" when the operator is "IS NOT DISTINCT FROM". I cannot figure out the best way for passing such information down, so I just ORed 0x8000000 to the operator Oid List. As no code in other parts is doing so, please advise a better approach, should I add a Bitmapset to pass the flags? Or should I define a new Node type to include both Oid and a bool flag? (backend/executor/nodeHashjoin.c, backend/executor/nodeHash.c) - To support merge join, I added a nulleqnull bool flag in SortSupportData to bypass the "stop merging earlier when NULLs is reached" logic when the join operator is DistinctExpr. I think there is a padding gap after "bool abbreviate;", so I add the new flag after that, just want to keep binary compatibility in case something depends on it... (backend/executor/nodeMergejoin.c, include/utils/sortsupport.h) - In create_join_clause, reconsider_outer_join_clause, and reconsider_full_join_clause functions, the derived expression generated by call to build_implied_join_equality outputs OpExpr for DistictExpr, because they are same in definition, I just patch the resulting node back to DistinctExpr if input is DistinctExpr. (backend/optimizer/path/equivclass.c) - All other changes are for necessary code paths only allow OpExpr, I added logic to allow DistinctExpr too. The patch in attachment is based on commit 821200405cc3f25fda28c5f58d17d640e25559b8. Thanks! Gao, Chi Beijing Microfun Co. Ltd.
enable_is_not_distinct_from_in_hash_and_merge_joins.patch
Description: enable_is_not_distinct_from_in_hash_and_merge_joins.patch
