JingDas created CALCITE-5881:
--------------------------------
Summary: 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
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)
