[ https://issues.apache.org/jira/browse/CALCITE-5881?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
ASF GitHub Bot updated CALCITE-5881: ------------------------------------ Labels: pull-request-available (was: ) > Support to get foreign keys metadata in RelMetadataQuery > -------------------------------------------------------- > > Key: CALCITE-5881 > URL: https://issues.apache.org/jira/browse/CALCITE-5881 > Project: Calcite > Issue Type: New Feature > Reporter: JingDas > Assignee: JingDas > Priority: Minor > Labels: pull-request-available > > We can get constraints by RelOptTable#getReferentialConstraints method, but > maybe can't get appropriate constraints at top relNode. > For example: > SQL: > {code:java} > 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'{code} > The relNode is: > {code:java} > 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]]){code} > 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 > {code:java} > RecordType(INTEGER DEPTNO, VARCHAR(10) NAME, BIGINT EXPR$0, INTEGER DEPTNO0, > VARCHAR(20) ENAME).{code} > We expect the foreign keys metadata: > {code:java} > foreignColumns bitset is {3} > uniqueColumns bitset is {0}{code} > When we want to get foreign keys metadata on top `LogicalProject`, the > `LogicalProject` rowType is > {code:java} > RecordType(VARCHAR(10) NAME, INTEGER DEPTNO, VARCHAR(20) ENAME, INTEGER > DEPTNO0).{code} > We expect the foreigns key metadata: > {code:java} > foreignColumns bitset is {1} > uniqueColumns bitset is {3}{code} > 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. -- This message was sent by Atlassian Jira (v8.20.10#820010)