[
https://issues.apache.org/jira/browse/CALCITE-6882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17940192#comment-17940192
]
Julian Hyde commented on CALCITE-6882:
--------------------------------------
I created [PR 4286|https://github.com/apache/calcite/pull/4286] with a test
case, and did some investigation. It appears that the problem is in the
{{areColumnsUnique(Join)}} method. It returns true for column set [0] and false
for a super set, [0, 10], and that should never happen.
The fix for CALCITE-3428 triggered the problem (by adding columns) but the
problem was already there. And the problem occurs in queries with no Filter.
> RelMdColumnUniqueness incorrectly claims fields are not unique if constant
> refinement occurs in a node above join
> -----------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-6882
> URL: https://issues.apache.org/jira/browse/CALCITE-6882
> Project: Calcite
> Issue Type: Bug
> Reporter: Ian Bertolacci
> Priority: Major
> Labels: pull-request-available
>
> There is some overly conservative logic in determining uniqueness of columns
> in RelNodes which have joins beneath them (not necessarily as children)
> In this case, this occurs when a filter condition involving the non-unique
> side of a join can engage in predicate pullup/refinement.
> Demonstration: inner join between emp and dept
> testUniqueKeysOnInnerJoinOnKeysWithParentNoFilter has no filter after the join
> testUniqueKeysOnInnerJoinOnKeysWithParentNonUniqueInclusiveFilter has a
> filter after the filter dep.name using `=` (this demonstrates the bug)
> testUniqueKeysOnInnerJoinOnKeysWithParentNonUniqueExclusiveFilter has a
> filter after the filter dep.name using `!=`
> In all three cases, the field position 0 (empno) should be listed as unique.
> But testUniqueKeysOnInnerJoinOnKeysWithParentNonUniqueInclusiveFilter reports
> it as not-unique.
> Specifically,
> testUniqueKeysOnInnerJoinOnKeysWithParentNonUniqueInclusiveFilter asks if
> column 0 is unique after the project, which asks if column 0 is unique after
> the filter; the pullup then adds column 10 to the bitset for unique columns.
> Column 10 is not unique, and so the result is that column 0 is marked as not
> unique.
> In testUniqueKeysOnInnerJoinOnKeysWithParentNonUniqueExclusiveFilter, the
> `!=` operator precludes the predicate pullup/refinement, and so the bitset is
> unmodified, and the analysis correctly marks column 0 after the join as
> unique.
> it seems like there is a combination of issues:
> # Join being too conservative
> # Incorrect processing for columns which can engage in predicate
> pullup/refinement.
> [Spawned by discussion in mailing
> list|https://lists.apache.org/thread/fmqyzokcqzv7vzq8lvg35h9opf5kwo33]
> Tests inside RelMetadataTest:
> {code:java}
> // Passes
> @Test void testUniqueKeysOnInnerJoinOnKeysWithParentNoFilter() {
> /** Project(EMPNO=[$0], DEPTNO=[$9])
> * Join(condition=[=($7, $9)], joinType=[inner])
> * TableScan(table=[[CATALOG, SALES, EMP]])
> * TableScan(table=[[CATALOG, SALES, DEPT]])
> */
> sql(
> "select emp.empno, dept.deptno\n"
> + "from emp\n"
> + " inner join dept\n"
> + "on emp.deptno = dept.deptno"
> )
> .assertThatAreColumnsUnique(ImmutableBitSet.of(0), is(true))
> .assertThatAreColumnsUnique(ImmutableBitSet.of(1), is(false));
> }
> // Fails (but should pass)
> @Test void
> testUniqueKeysOnInnerJoinOnKeysWithParentNonUniqueInclusiveFilter() {
> /** Project(EMPNO=[$0], DEPTNO=[$9])
> * Filter(condition=[=($10, 'foo')])
> * Join(condition=[=($7, $9)], joinType=[inner])
> * TableScan(table=[[CATALOG, SALES, EMP]])
> * TableScan(table=[[CATALOG, SALES, DEPT]])
> */
> sql(
> "select emp.empno, dept.deptno\n"
> + "from emp\n"
> + " inner join dept\n"
> + "on emp.deptno = dept.deptno\n"
> + "where dept.name = 'foo'"
> )
> .assertThatAreColumnsUnique(ImmutableBitSet.of(0), is(true))
> .assertThatAreColumnsUnique(ImmutableBitSet.of(1), is(false));
> }
> // Passes
> @Test void
> testUniqueKeysOnInnerJoinOnKeysWithParentNonUniqueExclusiveFilter() {
> /** Project(EMPNO=[$0], DEPTNO=[$9])
> * Filter(condition=[!=($10, 'foo')])
> * Join(condition=[=($7, $9)], joinType=[inner])
> * TableScan(table=[[CATALOG, SALES, EMP]])
> * TableScan(table=[[CATALOG, SALES, DEPT]])
> */
> sql(
> "select emp.empno, dept.deptno\n"
> + "from emp\n"
> + " inner join dept\n"
> + "on emp.deptno = dept.deptno\n"
> + "where dept.name <> 'foo'"
> )
> .assertThatAreColumnsUnique(ImmutableBitSet.of(0), is(true))
> .assertThatAreColumnsUnique(ImmutableBitSet.of(1), is(false));
> }{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
