[
https://issues.apache.org/jira/browse/CALCITE-5881?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17753913#comment-17753913
]
LakeShen commented on CALCITE-5881:
-----------------------------------
Hi [~jingda] IMO,A foreign key is one of the table constraints, the current in
Calcite, we could use Statistic# getReferentialConstraints method to get the
table's foreign key.
So in an SQL plan(RelNode Tree),for example,if a RelNode is Project and its
child is a TableScan,we want to use the foreign key information of TableScan.
Instead of getting the foreign key information of Project, we should use
functional dependencies.
In other words, only tables have foreign keys.The above is my understanding,If
I understand this wrong,pls correct me,thanks.
> 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)
