[jira] [Updated] (CALCITE-6044) RelMetadataQuery should regard single-row relational expressions as unique

["Julian Hyde (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-6044?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde updated CALCITE-6044:
---------------------------------
    Summary: RelMetadataQuery should regard single-row relational expressions as unique  (was: Column uniqueness for single-row relations)

> RelMetadataQuery should regard single-row relational expressions as unique
> --------------------------------------------------------------------------
>
>                 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)