[jira] [Updated] (CALCITE-793) The compiler asks for unnecessary collation trait on plan with materialized view

["Maryann Xue (JIRA)" <ji...@apache.org>]

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

Maryann Xue updated CALCITE-793:
--------------------------------
    Attachment: CALCITE-793.patch

Ran through all the tests, but one failed: LatticeTest.testJG(). The only difference with the ref was that the SUM function hadn't been changed to $SUM0 function:

Expected:
JdbcToEnumerableConverter
  JdbcAggregate(group=[{7, 16, 25, 27, 31, 37}], m0=[COUNT()], m1=[$SUM0($5)], m2=[$SUM0($7)])
    JdbcJoin(condition=[=($8, $33)], joinType=[inner])
      JdbcJoin(condition=[=($1, $23)], joinType=[inner])
        JdbcJoin(condition=[=($0, $9)], joinType=[inner])
          JdbcTableScan(table=[[foodmart, sales_fact_1997]])
          JdbcTableScan(table=[[foodmart, product]])
        JdbcTableScan(table=[[foodmart, time_by_day]])
      JdbcTableScan(table=[[foodmart, product_class]])

Actual:
JdbcToEnumerableConverter
  JdbcAggregate(group=[{7, 16, 25, 27, 31, 37}], m0=[COUNT()], m1=[SUM($5)], m2=[SUM($7)])
    JdbcJoin(condition=[=($8, $33)], joinType=[inner])
      JdbcJoin(condition=[=($1, $23)], joinType=[inner])
        JdbcJoin(condition=[=($0, $9)], joinType=[inner])
          JdbcTableScan(table=[[foodmart, sales_fact_1997]])
          JdbcTableScan(table=[[foodmart, product]])
        JdbcTableScan(table=[[foodmart, time_by_day]])
      JdbcTableScan(table=[[foodmart, product_class]])

The LogicalAggregate rel was an identical copy of the original LogicalAggregate rel after going through the line of code I added:
{code}
      result = result.copy(result.getTraitSet().replace(RelCollations.EMPTY), result.getInputs());
{code}
I suspect it might be the unstable costing of the Aggregate rel with different sets of agg functions (SUM and $SUM0 have the same cost), so I made an adjustment to Aggregate.computeSelfCost() just for test purpose:
{code}
   @Override public RelOptCost computeSelfCost(RelOptPlanner planner) {
     // REVIEW jvs 24-Aug-2008:  This is bogus, but no more bogus
     // than what's currently in Join.
     double rowCount = RelMetadataQuery.getRowCount(this);
     // Aggregates with more aggregate functions cost a bit more
     float multiplier = 1f + (float) aggCalls.size() * 0.125f;
+    for (AggregateCall aggCall : aggCalls) {
+    	if (aggCall.getAggregation().getName().equals("SUM")) {
+        multiplier += 0.0125f;
+    	}
+    }
     return planner.getCostFactory().makeCost(rowCount * multiplier, 0, 0);
  }
{code}
That way the test case passed.

I'll proceed to add a new test case if this first patch looks ok.

> The compiler asks for unnecessary collation trait on plan with materialized view
> --------------------------------------------------------------------------------
>
>                 Key: CALCITE-793
>                 URL: https://issues.apache.org/jira/browse/CALCITE-793
>             Project: Calcite
>          Issue Type: Bug
>    Affects Versions: 1.4.0-incubating
>            Reporter: Maryann Xue
>            Assignee: Julian Hyde
>             Fix For: next
>
>         Attachments: CALCITE-793.patch
>
>   Original Estimate: 120h
>  Remaining Estimate: 120h
>
> When a query does not have an ORDER BY clause, we should ignore the collation trait of the main table plan and should not request the materialized view plan to have the same collation.
> For example, we have a table 'A' sorted by primary key 'id', and we have a materialized view 'V' projected from 'A' which is sorted by column 'col1'. And now we have a query like "select id, col0, col1, col2 from A where col1 < '10'".
> The main table plan will come out like a Filter on top of a TableScan of 'A', while the materialized view plan should also be something like a Filter on top of a TableScan of 'V' and it should not have a Sort, so that if doing a col1 related filter on col1 ordered table 'V' is cheaper, the materialized view plan will be chosen.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)