Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-5881

Support to get foreign keys metadata in RelMetadataQuery

Attach filesAttach ScreenshotAdd voteVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • New Feature
    • Status: In Progress
    • Major
    • Resolution: Unresolved
    • None
    • None
    • None

    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.

      Attachments

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            jingda JingDas
            jingda JingDas

            Dates

              Created:
              Updated:

              Slack

                Issue deployment