[ https://issues.apache.org/jira/browse/CALCITE-2914?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
ASF GitHub Bot updated CALCITE-2914: ------------------------------------ Labels: pull-request-available (was: ) > 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 > Labels: pull-request-available > 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)