Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-6044

RelMetadataQuery should regard single-row relational expressions as unique

    XMLWordPrintableJSON

Details

    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

          Activity

            People

              pauljackson123 Paul Jackson
              pauljackson123 Paul Jackson
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: