[CALCITE-5881] Support to get foreign keys metadata in RelMetadataQuery

• Type: New Feature
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Labels: pull-request-available
• Assignee: JingDas
• Reporter: JingDas
• Created: 28/Jul/23 11:19
• Updated: 14/Aug/23 12:25

Description

We can get constraints by RelOptTable#getReferentialConstraints method, but maybe can't get appropriate constraints at top relNode.
For example:

SQL:

SELECT DEPT.name, emp_agg.deptno, emp_agg.ename, DEPT.deptno
FROM DEPT
RIGHT JOIN
(SELECT COUNT(sal), deptno, ename FROM EMP GROUP BY deptno, ename) emp_agg
ON DEPT.deptno = emp_agg.deptno
WHERE emp_agg.ename = 'job'

The relNode is:

LogicalProject(NAME=[$1], DEPTNO=[$3], ENAME=[$4], DEPTNO0=[$0])
  LogicalFilter(condition=[=($4, 'job')])
    LogicalJoin(condition=[=($0, $3)], joinType=[right])
      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
      LogicalProject(EXPR$0=[$2], DEPTNO=[$0], ENAME=[$1])
        LogicalAggregate(group=[{0, 1}], EXPR$0=[COUNT($2)])
          LogicalProject(DEPTNO=[$7], ENAME=[$1], SAL=[$5])
            LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])

where the foreign key is the DEPTNO column of CATALOG.SALES.EMP table,
reference the DEPTNO unique column of CATALOG.SALES.DEPT table.

When we want to get foreign keys metadata on `LogicalJoin` or top `LogicalProject`, There is no such method currently, it seems that we should trace the field column origin to get the foreign key and corresponding unique key.

 

The final result of this feature is something likely as following:

When we want to get foreign keys metadata on `LogicalJoin`, the `LogicalJoin` rowType is

RecordType(INTEGER DEPTNO, VARCHAR(10) NAME, BIGINT EXPR$0, INTEGER DEPTNO0, VARCHAR(20) ENAME).

We expect the foreign keys metadata:

foreignColumns bitset is {3}
uniqueColumns bitset is {0}

When we want to get foreign keys metadata on top `LogicalProject`, the `LogicalProject` rowType is

RecordType(VARCHAR(10) NAME, INTEGER DEPTNO, VARCHAR(20) ENAME, INTEGER DEPTNO0).

We expect the foreigns key metadata:

foreignColumns bitset is {1}
uniqueColumns bitset is {3}

All the foreign or unique columns is 0 based index.

Foreign keys metadata is very useful in many optimize scenes. Such as it can be used in join eliminate when join type is inner join and some other star schema query optimize.

Issue Links

• is depended upon by: CALCITE-5756 Expand ProjectJoinRemoveRule to support inner join removal by using the foreign-unique constraints
• links to: GitHub Pull Request #3345