[jira] [Updated] (CALCITE-2914) Improve how LatticeSuggester deduces foreign keys

["Julian Hyde (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-2914?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde updated CALCITE-2914:
---------------------------------
    Description: 
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.

  was:
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.


> Improve how LatticeSuggester deduces foreign keys
> -------------------------------------------------
>
>                 Key: CALCITE-2914
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2914
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Julian Hyde
>            Priority: Major
>
> 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)