[ https://issues.apache.org/jira/browse/CALCITE-6044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17786969#comment-17786969 ]
Julian Hyde edited comment on CALCITE-6044 at 11/16/23 11:27 PM: ----------------------------------------------------------------- I reviewed the PR. It needs a few revisions, but I'm sure we can complete it before 1.37. Should each implementation of {{getUniqueKeys}} check that the result is minimal, per the contract? Maybe use the following function: {code} Set<ImmutableBitSet> assertMinimal(Set<ImmutableBitSet> sets) { for (ImmutableBitSet set0 : sets) { for (ImmutableBitSet set1 : sets) { if (set0 != set1 && set0.contains(set1))) { throw new AssertionError(); } } } } {code} was (Author: julianhyde): I reviewed the PR. It needs a few revisions, but I'm sure we can complete it before 1.37. Should each implementation of {{getUniqueKeys}} check that the result is minimal, per the contract? Maybe use the following function: {code} Set<ImmutableBitSet> assertMinimal(Set<ImmutableBitSet> sets) { for (ImmutableBitSet set0 : sets) { for (ImmutableBitSet set1 : sets) { if (set0 != set 1 && set0.contains(set1))) { throw new AssertionError(); } } } } {code} > Column uniqueness for single-row relations > ------------------------------------------ > > Key: CALCITE-6044 > URL: https://issues.apache.org/jira/browse/CALCITE-6044 > Project: Calcite > Issue Type: Bug > Components: core > Affects Versions: 1.35.0 > Reporter: Paul Jackson > Assignee: Paul Jackson > Priority: Major > Labels: pull-request-available > Fix For: 1.37.0 > > > 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: > > {code:java} > SELECT * FROM emp A > JOIN ( SELECT MAX(hiredate) last_hired FROM emp) B > ON A.hiredate = B.last_hired > {code} > {{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. > {code:java} > @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)); > } {code} > -- This message was sent by Atlassian Jira (v8.20.10#820010)