[
https://issues.apache.org/jira/browse/CALCITE-2914?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-2914:
---------------------------------
Fix Version/s: 1.20.0
> Improve how LatticeSuggester deduces foreign keys
> -------------------------------------------------
>
> Key: CALCITE-2914
> URL: https://issues.apache.org/jira/browse/CALCITE-2914
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Julian Hyde
> Assignee: Julian Hyde
> Priority: Major
> Fix For: 1.20.0
>
>
> Improve how LatticeSuggester deduces foreign keys.
> Currently LatticeSuggester uses SqlStatisticProvider and deduces that a join
> between two tables is "many to one" if the left table has more rows than the
> right table. Clearly this is wrong, because it does not take into account the
> keys of the join.
> I think we will change the method
> {code:java}
> double tableCardinality(List<String> qualifiedTableName){code}
> to
> {code:java}
> double tableCardinality(RelOptTable table){code}
> and add a method
> {code:java}
> boolean isForeignKey(RelOptTable fromTable, List<String> fromColumns,
> RelOptTable toTable, List<String> toColumns){code}
> For example, {{isForeignKey(empTable, ["DEPTNO"], deptTable, ["DEPTNO"])}}
> would return true.
> One valid implementation might look at the catalog for foreign key
> constraints; another might execute a query to see whether every row in emp is
> matched by exaclty one row in dept. Here is a query that might do
> it:{code:sql}
> select emp.deptno
> from (select distinct deptno from emp) as emp
> left join dept using (deptno)
> group by emp.deptno
> having count(dept.deptno) != 1{code}
> If the query returns any rows it is not a foreign key.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
