[3/3] calcite git commit: [CALCITE-1876] In CSV example, tweak cost to ensure that Project is pushed through Aggregate (Luis Fernando Kauer)

[jh...@apache.org]

[CALCITE-1876] In CSV example, tweak cost to ensure that Project is pushed through Aggregate (Luis Fernando Kauer)

Solved by implementing CsvTableScan.computeSelfCost.

Smooth the function by adding 2 to top and bottom of fraction (Julian Hyde).

Close apache/calcite#562


Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/77a35491
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/77a35491
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/77a35491

Branch: refs/heads/master
Commit: 77a35491794724c32ad2fb7b3333744b84f1e18f
Parents: 5a0403c
Author: Luis Fernando Kauer <lf...@yahoo.com.br>
Authored: Tue Nov 7 17:29:26 2017 -0200
Committer: Julian Hyde <jh...@apache.org>
Committed: Wed Nov 8 08:41:33 2017 -0800

----------------------------------------------------------------------
 .../calcite/adapter/csv/CsvTableScan.java       | 16 +++++++++
 .../java/org/apache/calcite/test/CsvTest.java   | 35 ++++++++++++++++++++
 2 files changed, 51 insertions(+)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/calcite/blob/77a35491/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java
----------------------------------------------------------------------
diff --git a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java
index a667a44..e55655c 100644
--- a/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java
+++ b/example/csv/src/main/java/org/apache/calcite/adapter/csv/CsvTableScan.java
@@ -25,12 +25,14 @@ import org.apache.calcite.linq4j.tree.Blocks;
 import org.apache.calcite.linq4j.tree.Expressions;
 import org.apache.calcite.linq4j.tree.Primitive;
 import org.apache.calcite.plan.RelOptCluster;
+import org.apache.calcite.plan.RelOptCost;
 import org.apache.calcite.plan.RelOptPlanner;
 import org.apache.calcite.plan.RelOptTable;
 import org.apache.calcite.plan.RelTraitSet;
 import org.apache.calcite.rel.RelNode;
 import org.apache.calcite.rel.RelWriter;
 import org.apache.calcite.rel.core.TableScan;
+import org.apache.calcite.rel.metadata.RelMetadataQuery;
 import org.apache.calcite.rel.type.RelDataType;
 import org.apache.calcite.rel.type.RelDataTypeFactory;
 import org.apache.calcite.rel.type.RelDataTypeField;
@@ -79,6 +81,20 @@ public class CsvTableScan extends TableScan implements EnumerableRel {
     planner.addRule(CsvProjectTableScanRule.INSTANCE);
   }
 
+  @Override public RelOptCost computeSelfCost(RelOptPlanner planner,
+      RelMetadataQuery mq) {
+    // Multiply the cost by a factor that makes a scan more attractive if it
+    // has significantly fewer fields than the original scan.
+    //
+    // The "+ 2D" on top and bottom keeps the function fairly smooth.
+    //
+    // For example, if table has 3 fields, project has 1 field,
+    // then factor = (1 + 2) / (3 + 2) = 0.6
+    return super.computeSelfCost(planner, mq)
+        .multiplyBy(((double) fields.length + 2D)
+            / ((double) table.getRowType().getFieldCount() + 2D));
+  }
+
   public Result implement(EnumerableRelImplementor implementor, Prefer pref) {
     PhysType physType =
         PhysTypeImpl.of(

http://git-wip-us.apache.org/repos/asf/calcite/blob/77a35491/example/csv/src/test/java/org/apache/calcite/test/CsvTest.java
----------------------------------------------------------------------
diff --git a/example/csv/src/test/java/org/apache/calcite/test/CsvTest.java b/example/csv/src/test/java/org/apache/calcite/test/CsvTest.java
index 785688b..af9d997 100644
--- a/example/csv/src/test/java/org/apache/calcite/test/CsvTest.java
+++ b/example/csv/src/test/java/org/apache/calcite/test/CsvTest.java
@@ -241,6 +241,41 @@ public class CsvTest {
         .ok();
   }
 
+  @Test public void testPushDownProjectAggregate() throws SQLException {
+    final String sql = "explain plan for\n"
+        + "select gender, count(*) from EMPS group by gender";
+    final String expected = "PLAN="
+        + "EnumerableAggregate(group=[{0}], EXPR$1=[COUNT()])\n"
+        + "  CsvTableScan(table=[[SALES, EMPS]], fields=[[3]])\n";
+    sql("smart", sql).returns(expected).ok();
+  }
+
+  @Test public void testPushDownProjectAggregateWithFilter() throws SQLException {
+    final String sql = "explain plan for\n"
+        + "select max(empno) from EMPS where gender='F'";
+    final String expected = "PLAN="
+        + "EnumerableAggregate(group=[{}], EXPR$0=[MAX($0)])\n"
+        + "  EnumerableCalc(expr#0..1=[{inputs}], expr#2=['F'], "
+        + "expr#3=[=($t1, $t2)], proj#0..1=[{exprs}], $condition=[$t3])\n"
+        + "    CsvTableScan(table=[[SALES, EMPS]], fields=[[0, 3]])\n";
+    sql("smart", sql).returns(expected).ok();
+  }
+
+  @Test public void testPushDownProjectAggregateNested() throws SQLException {
+    final String sql = "explain plan for\n"
+        + "select gender, max(qty)\n"
+        + "from (\n"
+        + "  select name, gender, count(*) qty\n"
+        + "  from EMPS\n"
+        + "  group by name, gender) t\n"
+        + "group by gender";
+    final String expected = "PLAN="
+        + "EnumerableAggregate(group=[{1}], EXPR$1=[MAX($2)])\n"
+        + "  EnumerableAggregate(group=[{0, 1}], QTY=[COUNT()])\n"
+        + "    CsvTableScan(table=[[SALES, EMPS]], fields=[[1, 3]])\n";
+    sql("smart", sql).returns(expected).ok();
+  }
+
   @Test public void testFilterableSelect() throws SQLException {
     sql("filterable-model", "select name from EMPS").ok();
   }