Currently, the default metadata row count for join RelMdRowCount#getRowCount(Join rel, RelMetadataQuery mq) relies on RelMdUtil.getJoinRowCount. This method has several issues:
- In case of ANTI join, it returns the same estimation as a SEMI join
- In other cases (INNER, LEFT, RIGHT, FULL), it returns always the same formula:
leftRowCount * rightRowCount * mq.getSelectivity(join, condition)
which seems valid for an INNER join, but not for LEFT / RIGHT / FULL.