[
https://issues.apache.org/jira/browse/CALCITE-5881?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17754069#comment-17754069
]
JingDas edited comment on CALCITE-5881 at 8/14/23 12:19 PM:
------------------------------------------------------------
[~shenlang] Foreign key is a special constrait, it contains not only
foreignKey but also the uniqueKey that it reference, and foreignKey can be
combined. In some scene, such as we want to know the join condition is
foreign-unique key or not. Maybe `RelMetadataQuery #getForeignKeys` is better,
just like `RelMetadataQuery#getUniqueKeys`.
was (Author: JIRAUSER292370):
[~shenlang] Foreign key is a special constrait, it contains not only
foreignKey but also the uniqueKey that if reference, and foreignKey can be
combined. In some scene, such as we want to know the join condition is
foreign-unique key or not. Maybe `RelMetadataQuery #getForeignKeys` is better,
just like `RelMetadataQuery#getUniqueKeys`.
> 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)
