Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
1.35.0
Description
A single-row relation can result from a LIMIT 1 or an aggregation without GROUP BY. Every column in one of these relations should be unique by virtue of having a max row count of 1.
When joining with a single-row relation on a key field, the join result should no longer require that key field for uniqueness. For example, suppose the emp table had a composite key (empno,hiredate). If we join on hiredate=max(hiredate) then empno alone should be a unique column:
SELECT * FROM emp A JOIN ( SELECT MAX(hiredate) last_hired FROM emp) B ON A.hiredate = B.last_hired
join(A,B).empno should be unique because (A.empno,A.hiredate) is unique and A.hiredate is effectively constant because it is equal to B.last_hired.
Here are some RelMetadataTests cases that I think should pass.
@Test void testColumnUniquenessForLimit1() { final String sql = "" + "select *\n" + "from emp\n" + "limit 1"; sql(sql) .assertThatAreColumnsUnique(bitSetOf(0), is(true)) .assertThatAreColumnsUnique(bitSetOf(1), is(true)) .assertThatAreColumnsUnique(bitSetOf(), is(true)); } @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)); } @Test void testColumnUniquenessForJoinOnAggregation() { final String sql = "" + "select *\n" + "from emp A\n" + "join (\n" + " select max(empno) AS maxno from emp) B\n" + "on A.empno = B.maxno"; sql(sql) .assertThatAreColumnsUnique(bitSetOf(0), is(true)) .assertThatAreColumnsUnique(bitSetOf(9), is(true)) .assertThatAreColumnsUnique(bitSetOf(1), is(true)) .assertThatAreColumnsUnique(bitSetOf(), is(true)); } @Test void testColumnUniquenessForCorrelatedSubquery() { final String sql = "" + "select *\n" + "from emp A\n" + "where empno = (\n" + " select max(empno) from emp)"; sql(sql) .assertThatAreColumnsUnique(bitSetOf(0), is(true)) .assertThatAreColumnsUnique(bitSetOf(1), is(true)) .assertThatAreColumnsUnique(bitSetOf(), is(true)); }
Attachments
Issue Links
- is related to
-
CALCITE-6126 Return type check fails with OVER
- Open
- relates to
-
CALCITE-3428 Refine RelMdColumnUniqueness for Filter by considering constant columns
- Closed
- links to