[jira] [Created] (CALCITE-6044) Column uniqueness for single-row relations

[Paul Jackson (Jira)]

Paul Jackson created CALCITE-6044:
-------------------------------------

             Summary: 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


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)