[CALCITE-6401] JDBC adapter cannot push down JOIN with condition includes IS TRUE、IS NULL、Dynamic parameter、CAST、Literal comparison

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.36.0
• Fix Version/s: 1.38.0
• Labels: pull-request-available
• Assignee: xiong duan
• Reporter: Ulrich Kramer
• Created: 07/May/24 14:14
• Updated: 17/Oct/24 22:41
• Resolved: 17/Oct/24 22:41

Description

JDBC adapter (in JdbcJoinRule) cannot push down joins with conditions which include operations not listed in JdbcJoinRule::canJoinOnCondition.

See also CALCITE-4907

For example the following statement is executed using an EnumerableNestedLoopJoin

SELECT
  *
FROM
   A
  JOIN (
    SELECT
      D."userId",
      MAX(D."id") as "id"
    FROM
       D
    GROUP BY
      D."userId"
  ) B ON (
     A."id"  = B."id" AND A."userId" IS NOT NULL
  )
  OR (
    A."userId"  = B."userId" AND A."id" IS NOT NULL
  )

Adding the cases IS_NULL and IS_NOT_NULL to JdbcJoinRule::canJoinOnCondition fixes the problem for this statement. But I was not able to find out which cases are also missing here. E.g. a join condition which compares a RexInputRef with a RexLiteral also fails.

Where could I find the associated code in JdbcJoin::implement that makes it impossible to create an appropriate SQL statement if all operations were allowed?

Issue Links

• links to: GitHub Pull Request #3843