[
https://issues.apache.org/jira/browse/CALCITE-6727?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17905023#comment-17905023
]
Yu Tang commented on CALCITE-6727:
----------------------------------
{quote}assuming there is a primary key on {{empno}} and a unique key on
{{ename}}
{quote}
I can only find out that {{empno}} is the unique key from the test, but not
about {{{}ename{}}}. Do I miss something? Would you mind pointing it out?
[~julianhyde]
> 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)
