[CALCITE-5247] FilterJoinRule cannot simplify left join to inner join for `WHERE RHS.C1 IS NOT NULL OR RHS.C2 IS NOT NULL`

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.25.0, 1.31.0
• Fix Version/s: 1.32.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Ali Mansour
• Created: 26/Aug/22 14:28
• Updated: 10/Sep/22 08:40
• Resolved: 01/Sep/22 02:52

Description

For the following query:

SELECT 
  * 
FROM 
  (
    SELECT 
      A.C0 AS L, 
      B.C1 AS R1, 
      B.C2 AS R2 
    FROM 
      A 
      LEFT JOIN B
  ) 
WHERE 
  (R1 IS NOT NULL) 
  OR (R2 IS NOT NULL)

it should be optimized to

   SELECT 
      A.C0 AS L, 
      B.C1 AS R1, 
      B.C2 AS R2 
    FROM 
      A 
      INNER JOIN B

 but it didn't.

The FilterJoinRule fails to simplify this specific case, however if we have only 1 IS_NOT_NULL in the filter condition, it is able to do so.
I think the problem is in the Strong class.

Issue Links

• links to: GitHub Pull Request #2887