[CALCITE-5881] Support to get foreign keys metadata in RelMetadataQuery

[JingDas]

We can get constraints by RelOptTable#getReferentialConstraints method, but maybe can't get appropriate constraints at top relNode. 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. So support to get foreign keys metadata in RelMetadataQuery, it support to get composite foreign keys and get the corresponding unique key on the arbitrary relNode if you want. Tests are added to RelMetadataTest, the test code takes up a large part of the PR.

[LakeShen]

Hi @JingDas ,your pr's workflow has some abnormal information,please check it. You could run this cmd in your local idea:

./gradlew check

[sonarqubecloud[bot]]

Kudos, SonarCloud Quality Gate passed!   

0 Bugs
0 Vulnerabilities
0 Security Hotspots
1 Code Smell

82.4% Coverage
0.0% Duplication

[LakeShen]

Another point,Do you need add a class GeneratedMetadata_ForeignXXX like GeneratedMetadata_CollationHandler.java?

[LakeShen]

Maybe we could close this PR,because we would add the functional dependency,the jira is https://issues.apache.org/jira/browse/CALCITE-5913

[LakeShen]

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.

[github-actions[bot]]

This pull request has been marked as stale due to 30 days of inactivity. It will be closed in 90 days if no further activity occurs. If you think that’s incorrect or this pull request requires a review, please simply write any comment. If closed, you can revive the PR at any time and @mention a reviewer or discuss it on the [email protected] list. Thank you for your contributions.

[github-actions[bot]]

This pull request has been marked as stale due to 30 days of inactivity. It will be closed in 90 days if no further activity occurs. If you think that’s incorrect or this pull request requires a review, please simply write any comment. If closed, you can revive the PR at any time and @mention a reviewer or discuss it on the [email protected] list. Thank you for your contributions.

[github-actions[bot]]

This pull request has been marked as stale due to 30 days of inactivity. It will be closed in 90 days if no further activity occurs. If you think that’s incorrect or this pull request requires a review, please simply write any comment. If closed, you can revive the PR at any time and @mention a reviewer or discuss it on the [email protected] list. Thank you for your contributions.

[github-actions[bot]]

This pull request has been marked as stale due to 30 days of inactivity. It will be closed in 90 days if no further activity occurs. If you think that’s incorrect or this pull request requires a review, please simply write any comment. If closed, you can revive the PR at any time and @mention a reviewer or discuss it on the [email protected] list. Thank you for your contributions.