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

Column uniqueness constrain should only apply to inner join

    XMLWordPrintableJSON

Details

    Description

      When joining with a singleton relation, the columns from the other side are considered to be "associative unique".

      // RelMdColumnUniqueness.areColumnsUnique(Join rel, RelMetadataQuery mq ...
      
      final Double rightMaxRowCount = mq.getMaxRowCount(right);
      if (rightMaxRowCount != null && rightMaxRowCount <= 1.0) {
        leftColumns = leftColumns.union(joinInfo.leftSet());
      }
      final Double leftMaxRowCount = mq.getMaxRowCount(left);
      if (leftMaxRowCount != null && leftMaxRowCount <= 1.0) {
        rightColumns = rightColumns.union(joinInfo.rightSet());
      } 

      In the case

      @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))
            .assertThatUniqueKeysAre(bitSetOf());
      } 

      the join result A.ENAME is considered to be unique because A.EMPNO is the unique key. The test still passes when we change it to left join, in which case A.ENAME is not guaranteed to be unique. 

      Attachments

        Issue Links

          Activity

            People

              joeyutong Yu Tang
              joeyutong Yu Tang
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: