[
https://issues.apache.org/jira/browse/CALCITE-6727?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17904994#comment-17904994
]
Julian Hyde commented on CALCITE-6727:
--------------------------------------
In the query
{code}
select a.empno as a_empno,
a.ename as a_ename,
b.empno as b_empno,
b.ename as b_ename
from emp as a
left join (select * from emp limit 1) as b
on a.empno = b.empno
{code}
I think that {{a_empno}} and {{a_ename}} should be unique (assuming there is a
primary key on {{empno}} and a unique key on {{ename}}).
Are {{b_empno}} and {{b_ename}} unique? No, because
{{assertThatAreColumnsUnique}} calls {{areColumnsUnique}} with {{ignoreNulls =
false}}, and there may be more than one null value in {{b_empno}} and
{{b_ename}}.
Same (no) if join type is {{full}}. If join type is {{inner}} or {{right}},
yes, they are unique.
> Column uniqueness constrain should only apply to inner join
> -----------------------------------------------------------
>
> Key: CALCITE-6727
> URL: https://issues.apache.org/jira/browse/CALCITE-6727
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.38.0
> Reporter: Yu Tang
> Priority: Major
>
> When joining with a singleton relation, the columns from the other side are
> considered to be "associative unique".
> {code:java}
> // RelMdColumnUniqueness.areColumnsUnique(Join rel, RelMetadataQuery mq ...
> final Double rightMaxRowCount = mq.getMaxRowCount(right);
> if (rightMaxRowCount != null && rightMaxRowCount <= 1.0) {
> leftColumns = leftColumns.union(joinInfo.leftSet());
> }
> final Double leftMaxRowCount = mq.getMaxRowCount(left);
> if (leftMaxRowCount != null && leftMaxRowCount <= 1.0) {
> rightColumns = rightColumns.union(joinInfo.rightSet());
> } {code}
> In the case
> {code:java}
> @Test void testColumnUniquenessForJoinOnLimit1() {
> final String sql = ""
> + "select *\n"
> + "from emp A\n"
> + "join (\n"
> + " select * from emp\n"
> + " limit 1) B\n"
> + "on A.empno = B.empno";
> sql(sql)
> .assertThatAreColumnsUnique(bitSetOf(0), is(true))
> .assertThatAreColumnsUnique(bitSetOf(1), is(true))
> .assertThatAreColumnsUnique(bitSetOf(9), is(true))
> .assertThatAreColumnsUnique(bitSetOf(10), is(true))
> .assertThatAreColumnsUnique(bitSetOf(), is(true))
> .assertThatUniqueKeysAre(bitSetOf());
> } {code}
> the join result {{A.ENAME}} is considered to be unique because {{A.EMPNO}} is
> the unique key. The test still passes when we change it to left join, in
> which case {{A.ENAME}} is not guaranteed to be unique.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
