Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
1.38.0
Description
When joining with a singleton relation, the columns from the other side are considered to be "associative unique".
// 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()); }
In the case
@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()); }
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.
Attachments
Issue Links
- links to