[
https://issues.apache.org/jira/browse/CALCITE-6044?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-6044:
------------------------------------
Labels: pull-request-available (was: )
> 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
>
> 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)
