[
https://issues.apache.org/jira/browse/CALCITE-5881?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17750925#comment-17750925
]
Chunwei Lei commented on CALCITE-5881:
--------------------------------------
Sounds reasonable.
> 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: Major
> 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)
