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

MV recognition fails when query has constant filter for group by list in mv, but without group by

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.26.0
    • Component/s: None

      Description

      MV recognition fails when query has constant filter for group by list in mv, but has no group by itself.

      @Test void testConstantFilterInAgg() {
          final String mv = ""
              + "select \"name\", count(distinct \"deptno\") as cnt\n"
              + "from \"emps\" group by \"name\"";
          final String query = ""
              + "select count(distinct \"deptno\") as cnt\n"
              + "from \"emps\" where \"name\" = 'hello'";
          sql(mv, query).ok();
        }
      

      This case fails, however we can see that, we can rewrite the query with mv like this, suppose mv is the name of the table.

      select cnt from mv where name = 'hello';
      

      However, this query has the same semantics with the query in the case, using this query for mv recognition, the case will success

      final String query = ""
              + "select cnt from(\n"
              + " select \"name\", count(distinct \"deptno\") as cnt "
              + " from \"emps\" group by \"name\") t\n"
              + "where \"name\" = 'hello'";
      

      But we cannot force users to only write their queries in this way, so maybe we need to enhance  the mv recognition ability of AggregateOnCalcToAggregateUnifyRule to cover this case.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                yanlin-Lynn Wang Yanlin
                Reporter:
                yanlin-Lynn Wang Yanlin
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 1h 10m
                  1h 10m