You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2014/08/01 00:14:53 UTC
[1/3] git commit: Add fluent method withHook,
to more easily add hooks in tests.
Repository: incubator-optiq
Updated Branches:
refs/heads/master d861a0887 -> e4b5fe77a
Add fluent method withHook, to more easily add hooks in tests.
Previously you had to call Hook.addThread outside the test, and remember to close it in a 'finally' block. Now the AssertQuery instance has a list of hooks, and remembers to close them all. You can use hooks to, for example, enable non-standard combinations of planner rules.
Fix TPC-DS row-count estimates.
Project: http://git-wip-us.apache.org/repos/asf/incubator-optiq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-optiq/commit/249b00cb
Tree: http://git-wip-us.apache.org/repos/asf/incubator-optiq/tree/249b00cb
Diff: http://git-wip-us.apache.org/repos/asf/incubator-optiq/diff/249b00cb
Branch: refs/heads/master
Commit: 249b00cb7c50f91f1f3878f0b95296f77b24eb45
Parents: d861a08
Author: Julian Hyde <jh...@apache.org>
Authored: Mon Jul 28 17:08:37 2014 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Mon Jul 28 17:08:37 2014 -0700
----------------------------------------------------------------------
.../net/hydromatic/optiq/test/JdbcTest.java | 48 ++++++------
.../net/hydromatic/optiq/test/OptiqAssert.java | 48 ++++++++----
.../optiq/impl/tpcds/TpcdsSchema.java | 10 ++-
.../hydromatic/optiq/impl/tpcds/TpcdsTest.java | 77 ++++++++++----------
4 files changed, 101 insertions(+), 82 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/249b00cb/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java b/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
index 4ae47d5..f7042cc 100644
--- a/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
+++ b/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
@@ -5660,32 +5660,28 @@ public class JdbcTest {
/** Tests {@link SqlDialect}. */
@Test public void testDialect() {
final String[] sqls = {null};
- final Hook.Closeable hook = Hook.QUERY_PLAN.addThread(
- new Function<String, Void>() {
- public Void apply(String sql) {
- sqls[0] = sql;
- return null;
- }
- });
- try {
- OptiqAssert.that()
- .with(OptiqAssert.Config.JDBC_FOODMART)
- .query(
- "select count(*) as c from \"foodmart\".\"employee\" as e1\n"
- + " where \"first_name\" = 'abcde'\n"
- + " and \"gender\" = 'F'")
- .returns("C=0\n");
- switch (OptiqAssert.CONNECTION_SPEC) {
- case HSQLDB:
- assertThat(Util.toLinux(sqls[0]), equalTo(
- "SELECT COUNT(*) AS \"C\"\n"
- + "FROM (SELECT 0 AS \"DUMMY\"\n"
- + "FROM \"foodmart\".\"employee\"\n"
- + "WHERE \"first_name\" = 'abcde' AND \"gender\" = 'F') AS \"t0\""));
- break;
- }
- } finally {
- hook.close();
+ OptiqAssert.that()
+ .with(OptiqAssert.Config.JDBC_FOODMART)
+ .query(
+ "select count(*) as c from \"foodmart\".\"employee\" as e1\n"
+ + " where \"first_name\" = 'abcde'\n"
+ + " and \"gender\" = 'F'")
+ .withHook(Hook.QUERY_PLAN,
+ new Function<String, Void>() {
+ public Void apply(String sql) {
+ sqls[0] = sql;
+ return null;
+ }
+ })
+ .returns("C=0\n");
+ switch (OptiqAssert.CONNECTION_SPEC) {
+ case HSQLDB:
+ assertThat(Util.toLinux(sqls[0]), equalTo(
+ "SELECT COUNT(*) AS \"C\"\n"
+ + "FROM (SELECT 0 AS \"DUMMY\"\n"
+ + "FROM \"foodmart\".\"employee\"\n"
+ + "WHERE \"first_name\" = 'abcde' AND \"gender\" = 'F') AS \"t0\""));
+ break;
}
}
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/249b00cb/core/src/test/java/net/hydromatic/optiq/test/OptiqAssert.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/net/hydromatic/optiq/test/OptiqAssert.java b/core/src/test/java/net/hydromatic/optiq/test/OptiqAssert.java
index 336ce56..7147e3c 100644
--- a/core/src/test/java/net/hydromatic/optiq/test/OptiqAssert.java
+++ b/core/src/test/java/net/hydromatic/optiq/test/OptiqAssert.java
@@ -36,6 +36,7 @@ import org.eigenbase.util.*;
import com.google.common.base.Function;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMultiset;
+import com.google.common.collect.Lists;
import java.io.PrintWriter;
import java.io.StringWriter;
@@ -352,14 +353,19 @@ public class OptiqAssert {
String sql,
int limit,
boolean materializationsEnabled,
+ List<Pair<Hook, Function>> hooks,
Function1<ResultSet, Void> resultChecker,
Function1<Throwable, Void> exceptionChecker) throws Exception {
final String message =
"With materializationsEnabled=" + materializationsEnabled
+ ", limit=" + limit;
+ final List<Hook.Closeable> closeableList = Lists.newArrayList();
try {
((OptiqConnection) connection).getProperties().setProperty(
"materializationsEnabled", Boolean.toString(materializationsEnabled));
+ for (Pair<Hook, Function> hook : hooks) {
+ closeableList.add(hook.left.addThread(hook.right));
+ }
Statement statement = connection.createStatement();
statement.setMaxRows(limit <= 0 ? limit : Math.max(limit, 1));
ResultSet resultSet;
@@ -390,6 +396,10 @@ public class OptiqAssert {
connection.close();
} catch (Throwable e) {
throw new RuntimeException(message, e);
+ } finally {
+ for (Hook.Closeable closeable : closeableList) {
+ closeable.close();
+ }
}
}
@@ -884,6 +894,7 @@ public class OptiqAssert {
private String plan;
private int limit;
private boolean materializationsEnabled = false;
+ private final List<Pair<Hook, Function>> hooks = Lists.newArrayList();
private AssertQuery(ConnectionFactory connectionFactory, String sql) {
this.sql = sql;
@@ -918,7 +929,7 @@ public class OptiqAssert {
Function1<ResultSet, Void> checker) {
try {
assertQuery(createConnection(), sql, limit, materializationsEnabled,
- checker, null);
+ hooks, checker, null);
return this;
} catch (Exception e) {
throw new RuntimeException(
@@ -933,7 +944,7 @@ public class OptiqAssert {
public AssertQuery throws_(String message) {
try {
assertQuery(createConnection(), sql, limit, materializationsEnabled,
- null, checkException(message));
+ hooks, null, checkException(message));
return this;
} catch (Exception e) {
throw new RuntimeException(
@@ -944,7 +955,7 @@ public class OptiqAssert {
public AssertQuery runs() {
try {
assertQuery(createConnection(), sql, limit, materializationsEnabled,
- null, null);
+ hooks, null, null);
return this;
} catch (Exception e) {
throw new RuntimeException(
@@ -954,9 +965,8 @@ public class OptiqAssert {
public AssertQuery typeIs(String expected) {
try {
- assertQuery(
- createConnection(), sql, limit, false,
- checkResultType(expected), null);
+ assertQuery(createConnection(), sql, limit, false,
+ hooks, checkResultType(expected), null);
return this;
} catch (Exception e) {
throw new RuntimeException(
@@ -1020,7 +1030,7 @@ public class OptiqAssert {
if (plan != null) {
return;
}
- final Hook.Closeable hook = Hook.JAVA_PLAN.addThread(
+ addHook(Hook.JAVA_PLAN,
new Function<String, Void>() {
public Void apply(String a0) {
plan = a0;
@@ -1029,13 +1039,11 @@ public class OptiqAssert {
});
try {
assertQuery(createConnection(), sql, limit, materializationsEnabled,
- null, null);
+ hooks, null, null);
assertNotNull(plan);
} catch (Exception e) {
throw new RuntimeException(
"exception while executing [" + sql + "]", e);
- } finally {
- hook.close();
}
}
@@ -1044,8 +1052,8 @@ public class OptiqAssert {
* what it wants. This method can be used to check whether a particular
* MongoDB or SQL query is generated, for instance. */
public AssertQuery queryContains(Function1<List, Void> predicate1) {
- final List<Object> list = new ArrayList<Object>();
- final Hook.Closeable hook = Hook.QUERY_PLAN.addThread(
+ final List<Object> list = Lists.newArrayList();
+ addHook(Hook.QUERY_PLAN,
new Function<Object, Void>() {
public Void apply(Object a0) {
list.add(a0);
@@ -1054,14 +1062,12 @@ public class OptiqAssert {
});
try {
assertQuery(createConnection(), sql, limit, materializationsEnabled,
- null, null);
+ hooks, null, null);
predicate1.apply(list);
return this;
} catch (Exception e) {
throw new RuntimeException(
"exception while executing [" + sql + "]", e);
- } finally {
- hook.close();
}
}
@@ -1089,6 +1095,18 @@ public class OptiqAssert {
this.materializationsEnabled = enable;
return this;
}
+
+ /** Adds a hook and a handler for that hook. Optiq will create a thread
+ * hook (by calling {@link Hook#addThread(com.google.common.base.Function)})
+ * just before running the query, and remove the hook afterwards. */
+ public <T> AssertQuery withHook(Hook hook, Function<T, Void> handler) {
+ addHook(hook, handler);
+ return this;
+ }
+
+ private <T> void addHook(Hook hook, Function<T, Void> handler) {
+ hooks.add(Pair.of(hook, (Function) handler));
+ }
}
public enum Config {
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/249b00cb/plus/src/main/java/net/hydromatic/optiq/impl/tpcds/TpcdsSchema.java
----------------------------------------------------------------------
diff --git a/plus/src/main/java/net/hydromatic/optiq/impl/tpcds/TpcdsSchema.java b/plus/src/main/java/net/hydromatic/optiq/impl/tpcds/TpcdsSchema.java
index ccfb7d3..9a3d35f 100644
--- a/plus/src/main/java/net/hydromatic/optiq/impl/tpcds/TpcdsSchema.java
+++ b/plus/src/main/java/net/hydromatic/optiq/impl/tpcds/TpcdsSchema.java
@@ -61,14 +61,15 @@ public class TpcdsSchema extends AbstractSchema {
.put("catalog_page", 11718)
.put("catalog_returns", 144067)
.put("catalog_sales", 1441548)
- .put("customer", 50000)
- .put("demographics", 1920800)
+ .put("customer", 100000)
+ .put("customer_address", 50000)
+ .put("customer_demographics", 1920800)
.put("date_dim", 73049)
.put("household_demographics", 7200)
.put("income_band", 20)
.put("inventory", 11745000)
.put("item", 18000)
- .put("promotions", 300)
+ .put("promotion", 300)
.put("reason", 35)
.put("ship_mode", 20)
.put("store", 12)
@@ -113,7 +114,8 @@ public class TpcdsSchema extends AbstractSchema {
@Override public Statistic getStatistic() {
Bug.upgrade("add row count estimate to TpcdsTable, and use it");
- double rowCount = TABLE_ROW_COUNTS.get(tpcdsTable.name);
+ Integer rowCount = TABLE_ROW_COUNTS.get(tpcdsTable.name);
+ assert rowCount != null : tpcdsTable.name;
return Statistics.of(rowCount, Collections.<BitSet>emptyList());
}
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/249b00cb/plus/src/test/java/net/hydromatic/optiq/impl/tpcds/TpcdsTest.java
----------------------------------------------------------------------
diff --git a/plus/src/test/java/net/hydromatic/optiq/impl/tpcds/TpcdsTest.java b/plus/src/test/java/net/hydromatic/optiq/impl/tpcds/TpcdsTest.java
index 27862e0..38e2005 100644
--- a/plus/src/test/java/net/hydromatic/optiq/impl/tpcds/TpcdsTest.java
+++ b/plus/src/test/java/net/hydromatic/optiq/impl/tpcds/TpcdsTest.java
@@ -42,6 +42,16 @@ import net.hydromatic.tpcds.query.Query;
* command-line.
* (See {@link net.hydromatic.optiq.test.OptiqAssert#ENABLE_SLOW}.)</p> */
public class TpcdsTest {
+ /** Hook-handler that enables bushy-join optimization. */
+ public static final
+ Function<Pair<List<Prepare.Materialization>, Program[]>, Void> HANDLER =
+ new Function<Pair<List<Prepare.Materialization>, Program[]>, Void>() {
+ public Void apply(Pair<List<Prepare.Materialization>, Program[]> a0) {
+ a0.right[0] = Programs.heuristicJoinOrder(Programs.RULE_SET, true);
+ return null;
+ }
+ };
+
private static String schema(String name, String scaleFactor) {
return " {\n"
+ " type: 'custom',\n"
@@ -98,42 +108,33 @@ public class TpcdsTest {
@Test public void testQuery17Plan() {
//noinspection unchecked
- Hook.Closeable closeable = Hook.PROGRAM.addThread(
- new Function<Pair<List<Prepare.Materialization>, Program[]>, Void>() {
- public Void apply(Pair<List<Prepare.Materialization>, Program[]> a0) {
- a0.right[0] = Programs.heuristicJoinOrder(Programs.RULE_SET, true);
- return null;
- }
- });
- try {
- checkQuery(17).explainMatches("including all attributes ",
- OptiqAssert.checkMaskedResultContains(""
- + "EnumerableProjectRel(I_ITEM_ID=[$0], I_ITEM_DESC=[$1], S_STATE=[$2], STORE_SALES_QUANTITYCOUNT=[$3], STORE_SALES_QUANTITYAVE=[$4], STORE_SALES_QUANTITYSTDEV=[$5], STORE_SALES_QUANTITYCOV=[/($5, $4)], AS_STORE_RETURNS_QUANTITYCOUNT=[$6], AS_STORE_RETURNS_QUANTITYAVE=[$7], AS_STORE_RETURNS_QUANTITYSTDEV=[$8], STORE_RETURNS_QUANTITYCOV=[/($8, $7)], CATALOG_SALES_QUANTITYCOUNT=[$9], CATALOG_SALES_QUANTITYAVE=[$10], CATALOG_SALES_QUANTITYSTDEV=[/($11, $10)], CATALOG_SALES_QUANTITYCOV=[/($11, $10)]): rowcount = 5.434029018852197E26, cumulative cost = {1.618185849567114E30 rows, 6.412154242245593E28 cpu, 0.0 io}\n"
- + " EnumerableSortRel(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[ASC], dir1=[ASC], dir2=[ASC]): rowcount = 5.434029018852197E26, cumulative cost = {1.6176424466652288E30 rows, 5.597049889417763E28 cpu, 0.0 io}\n"
- + " EnumerableProjectRel(I_ITEM_ID=[$0], I_ITEM_DESC=[$1], S_STATE=[$2], STORE_SALES_QUANTITYCOUNT=[$3], STORE_SALES_QUANTITYAVE=[CAST(/($4, $5)):JavaType(class java.lang.Integer)], STORE_SALES_QUANTITYSTDEV=[CAST(POWER(/(-($6, /(*($4, $4), $5)), CASE(=($5, 1), null, -($5, 1))), 0.5)):JavaType(class java.lang.Integer)], AS_STORE_RETURNS_QUANTITYCOUNT=[$7], AS_STORE_RETURNS_QUANTITYAVE=[CAST(/($8, $7)):JavaType(class java.lang.Integer)], AS_STORE_RETURNS_QUANTITYSTDEV=[CAST(POWER(/(-($9, /(*($8, $8), $7)), CASE(=($7, 1), null, -($7, 1))), 0.5)):JavaType(class java.lang.Integer)], CATALOG_SALES_QUANTITYCOUNT=[$10], CATALOG_SALES_QUANTITYAVE=[CAST(/($11, $10)):JavaType(class java.lang.Integer)], $f11=[CAST(POWER(/(-($12, /(*($11, $11), $10)), CASE(=($10, 1), null, -($10, 1))), 0.5)):JavaType(class java.lang.Integer)]): rowcount = 5.434029018852197E26, cumulative cost = {1.1954863841615548E28 rows, 5.5427095992292415E28 cpu, 0.0 io}\n"
- + " EnumerableAggregateRel(group=[{0, 1, 2}], STORE_SALES_QUANTITYCOUNT=[COUNT()], agg#1=[SUM($3)], agg#2=[COUNT($3)], agg#3=[SUM($6)], AS_STORE_RETURNS_QUANTITYCOUNT=[COUNT($4)], agg#5=[SUM($4)], agg#6=[SUM($7)], CATALOG_SALES_QUANTITYCOUNT=[COUNT($5)], agg#8=[SUM($5)], agg#9=[SUM($8)]): rowcount = 5.434029018852197E26, cumulative cost = {1.1411460939730328E28 rows, 4.890626116966977E28 cpu, 0.0 io}\n"
- + " EnumerableProjectRel(I_ITEM_ID=[$58], I_ITEM_DESC=[$61], S_STATE=[$24], SS_QUANTITY=[$89], SR_RETURN_QUANTITY=[$140], CS_QUANTITY=[$196], $f6=[*($89, $89)], $f7=[*($140, $140)], $f8=[*($196, $196)]): rowcount = 5.434029018852197E27, cumulative cost = {1.0868058037845108E28 rows, 4.890626116966977E28 cpu, 0.0 io}\n"
- + " EnumerableJoinRel(condition=[AND(=($82, $133), =($81, $132), =($88, $139))], joinType=[inner]): rowcount = 5.434029018852197E27, cumulative cost = {5.434029018992911E27 rows, 5065780.0 cpu, 0.0 io}\n"
- + " EnumerableJoinRel(condition=[=($0, $86)], joinType=[inner]): rowcount = 2.3008402586892598E13, cumulative cost = {4.8588854672852766E13 rows, 3044518.0 cpu, 0.0 io}\n"
- + " EnumerableTableAccessRel(table=[[TPCDS, STORE]]): rowcount = 12.0, cumulative cost = {12.0 rows, 13.0 cpu, 0.0 io}\n"
- + " EnumerableJoinRel(condition=[=($0, $50)], joinType=[inner]): rowcount = 1.2782445881607E13, cumulative cost = {1.279800620431134E13 rows, 3044505.0 cpu, 0.0 io}\n"
- + " EnumerableFilterRel(condition=[=(CAST($15):VARCHAR(6) CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\", '1998Q1')]): rowcount = 10957.35, cumulative cost = {84006.35 rows, 146099.0 cpu, 0.0 io}\n"
- + " EnumerableTableAccessRel(table=[[TPCDS, DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu, 0.0 io}\n"
- + " EnumerableJoinRel(condition=[=($0, $24)], joinType=[inner]): rowcount = 7.7770908E9, cumulative cost = {7.783045975286664E9 rows, 2898406.0 cpu, 0.0 io}\n"
- + " EnumerableTableAccessRel(table=[[TPCDS, ITEM]]): rowcount = 18000.0, cumulative cost = {18000.0 rows, 18001.0 cpu, 0.0 io}\n"
- + " EnumerableTableAccessRel(table=[[TPCDS, STORE_SALES]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 2880405.0 cpu, 0.0 io}\n"
- + " EnumerableJoinRel(condition=[AND(=($31, $79), =($30, $91))], joinType=[inner]): rowcount = 6.9978029381741304E16, cumulative cost = {6.9978054204658736E16 rows, 2021262.0 cpu, 0.0 io}\n"
- + " EnumerableJoinRel(condition=[=($28, $0)], joinType=[inner]): rowcount = 7.87597881975E8, cumulative cost = {7.884434222216867E8 rows, 433614.0 cpu, 0.0 io}\n"
- + " EnumerableFilterRel(condition=[OR(=($15, '1998Q1'), =($15, '1998Q2'), =($15, '1998Q3'))]): rowcount = 18262.25, cumulative cost = {91311.25 rows, 146099.0 cpu, 0.0 io}\n"
- + " EnumerableTableAccessRel(table=[[TPCDS, DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu, 0.0 io}\n"
- + " EnumerableTableAccessRel(table=[[TPCDS, STORE_RETURNS]]): rowcount = 287514.0, cumulative cost = {287514.0 rows, 287515.0 cpu, 0.0 io}\n"
- + " EnumerableJoinRel(condition=[=($28, $0)], joinType=[inner]): rowcount = 3.94888649445E9, cumulative cost = {3.9520401026966867E9 rows, 1587648.0 cpu, 0.0 io}\n"
- + " EnumerableFilterRel(condition=[OR(=($15, '1998Q1'), =($15, '1998Q2'), =($15, '1998Q3'))]): rowcount = 18262.25, cumulative cost = {91311.25 rows, 146099.0 cpu, 0.0 io}\n"
- + " EnumerableTableAccessRel(table=[[TPCDS, DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu, 0.0 io}\n"
- + " EnumerableTableAccessRel(table=[[TPCDS, CATALOG_SALES]]): rowcount = 1441548.0, cumulative cost = {1441548.0 rows, 1441549.0 cpu, 0.0 io}\n"));
- } finally {
- closeable.close();
- }
+ checkQuery(17)
+ .withHook(Hook.PROGRAM, HANDLER)
+ .explainMatches("including all attributes ",
+ OptiqAssert.checkMaskedResultContains(""
+ + "EnumerableProjectRel(I_ITEM_ID=[$0], I_ITEM_DESC=[$1], S_STATE=[$2], STORE_SALES_QUANTITYCOUNT=[$3], STORE_SALES_QUANTITYAVE=[$4], STORE_SALES_QUANTITYSTDEV=[$5], STORE_SALES_QUANTITYCOV=[/($5, $4)], AS_STORE_RETURNS_QUANTITYCOUNT=[$6], AS_STORE_RETURNS_QUANTITYAVE=[$7], AS_STORE_RETURNS_QUANTITYSTDEV=[$8], STORE_RETURNS_QUANTITYCOV=[/($8, $7)], CATALOG_SALES_QUANTITYCOUNT=[$9], CATALOG_SALES_QUANTITYAVE=[$10], CATALOG_SALES_QUANTITYSTDEV=[/($11, $10)], CATALOG_SALES_QUANTITYCOV=[/($11, $10)]): rowcount = 5.434029018852197E26, cumulative cost = {1.618185849567114E30 rows, 6.412154242245593E28 cpu, 0.0 io}\n"
+ + " EnumerableSortRel(sort0=[$0], sort1=[$1], sort2=[$2], dir0=[ASC], dir1=[ASC], dir2=[ASC]): rowcount = 5.434029018852197E26, cumulative cost = {1.6176424466652288E30 rows, 5.597049889417763E28 cpu, 0.0 io}\n"
+ + " EnumerableProjectRel(I_ITEM_ID=[$0], I_ITEM_DESC=[$1], S_STATE=[$2], STORE_SALES_QUANTITYCOUNT=[$3], STORE_SALES_QUANTITYAVE=[CAST(/($4, $5)):JavaType(class java.lang.Integer)], STORE_SALES_QUANTITYSTDEV=[CAST(POWER(/(-($6, /(*($4, $4), $5)), CASE(=($5, 1), null, -($5, 1))), 0.5)):JavaType(class java.lang.Integer)], AS_STORE_RETURNS_QUANTITYCOUNT=[$7], AS_STORE_RETURNS_QUANTITYAVE=[CAST(/($8, $7)):JavaType(class java.lang.Integer)], AS_STORE_RETURNS_QUANTITYSTDEV=[CAST(POWER(/(-($9, /(*($8, $8), $7)), CASE(=($7, 1), null, -($7, 1))), 0.5)):JavaType(class java.lang.Integer)], CATALOG_SALES_QUANTITYCOUNT=[$10], CATALOG_SALES_QUANTITYAVE=[CAST(/($11, $10)):JavaType(class java.lang.Integer)], $f11=[CAST(POWER(/(-($12, /(*($11, $11), $10)), CASE(=($10, 1), null, -($10, 1))), 0.5)):JavaType(class java.lang.Integer)]): rowcount = 5.434029018852197E26, cumulative cost = {1.1954863841615548E28 rows, 5.5427095992292415E28 cpu, 0.0 io}\n"
+ + " EnumerableAggregateRel(group=[{0, 1, 2}], STORE_SALES_QUANTITYCOUNT=[COUNT()], agg#1=[SUM($3)], agg#2=[COUNT($3)], agg#3=[SUM($6)], AS_STORE_RETURNS_QUANTITYCOUNT=[COUNT($4)], agg#5=[SUM($4)], agg#6=[SUM($7)], CATALOG_SALES_QUANTITYCOUNT=[COUNT($5)], agg#8=[SUM($5)], agg#9=[SUM($8)]): rowcount = 5.434029018852197E26, cumulative cost = {1.1411460939730328E28 rows, 4.890626116966977E28 cpu, 0.0 io}\n"
+ + " EnumerableProjectRel(I_ITEM_ID=[$58], I_ITEM_DESC=[$61], S_STATE=[$24], SS_QUANTITY=[$89], SR_RETURN_QUANTITY=[$140], CS_QUANTITY=[$196], $f6=[*($89, $89)], $f7=[*($140, $140)], $f8=[*($196, $196)]): rowcount = 5.434029018852197E27, cumulative cost = {1.0868058037845108E28 rows, 4.890626116966977E28 cpu, 0.0 io}\n"
+ + " EnumerableJoinRel(condition=[AND(=($82, $133), =($81, $132), =($88, $139))], joinType=[inner]): rowcount = 5.434029018852197E27, cumulative cost = {5.434029018992911E27 rows, 5065780.0 cpu, 0.0 io}\n"
+ + " EnumerableJoinRel(condition=[=($0, $86)], joinType=[inner]): rowcount = 2.3008402586892598E13, cumulative cost = {4.8588854672852766E13 rows, 3044518.0 cpu, 0.0 io}\n"
+ + " EnumerableTableAccessRel(table=[[TPCDS, STORE]]): rowcount = 12.0, cumulative cost = {12.0 rows, 13.0 cpu, 0.0 io}\n"
+ + " EnumerableJoinRel(condition=[=($0, $50)], joinType=[inner]): rowcount = 1.2782445881607E13, cumulative cost = {1.279800620431134E13 rows, 3044505.0 cpu, 0.0 io}\n"
+ + " EnumerableFilterRel(condition=[=(CAST($15):VARCHAR(6) CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\", '1998Q1')]): rowcount = 10957.35, cumulative cost = {84006.35 rows, 146099.0 cpu, 0.0 io}\n"
+ + " EnumerableTableAccessRel(table=[[TPCDS, DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu, 0.0 io}\n"
+ + " EnumerableJoinRel(condition=[=($0, $24)], joinType=[inner]): rowcount = 7.7770908E9, cumulative cost = {7.783045975286664E9 rows, 2898406.0 cpu, 0.0 io}\n"
+ + " EnumerableTableAccessRel(table=[[TPCDS, ITEM]]): rowcount = 18000.0, cumulative cost = {18000.0 rows, 18001.0 cpu, 0.0 io}\n"
+ + " EnumerableTableAccessRel(table=[[TPCDS, STORE_SALES]]): rowcount = 2880404.0, cumulative cost = {2880404.0 rows, 2880405.0 cpu, 0.0 io}\n"
+ + " EnumerableJoinRel(condition=[AND(=($31, $79), =($30, $91))], joinType=[inner]): rowcount = 6.9978029381741304E16, cumulative cost = {6.9978054204658736E16 rows, 2021262.0 cpu, 0.0 io}\n"
+ + " EnumerableJoinRel(condition=[=($28, $0)], joinType=[inner]): rowcount = 7.87597881975E8, cumulative cost = {7.884434222216867E8 rows, 433614.0 cpu, 0.0 io}\n"
+ + " EnumerableFilterRel(condition=[OR(=($15, '1998Q1'), =($15, '1998Q2'), =($15, '1998Q3'))]): rowcount = 18262.25, cumulative cost = {91311.25 rows, 146099.0 cpu, 0.0 io}\n"
+ + " EnumerableTableAccessRel(table=[[TPCDS, DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu, 0.0 io}\n"
+ + " EnumerableTableAccessRel(table=[[TPCDS, STORE_RETURNS]]): rowcount = 287514.0, cumulative cost = {287514.0 rows, 287515.0 cpu, 0.0 io}\n"
+ + " EnumerableJoinRel(condition=[=($28, $0)], joinType=[inner]): rowcount = 3.94888649445E9, cumulative cost = {3.9520401026966867E9 rows, 1587648.0 cpu, 0.0 io}\n"
+ + " EnumerableFilterRel(condition=[OR(=($15, '1998Q1'), =($15, '1998Q2'), =($15, '1998Q3'))]): rowcount = 18262.25, cumulative cost = {91311.25 rows, 146099.0 cpu, 0.0 io}\n"
+ + " EnumerableTableAccessRel(table=[[TPCDS, DATE_DIM]]): rowcount = 73049.0, cumulative cost = {73049.0 rows, 73050.0 cpu, 0.0 io}\n"
+ + " EnumerableTableAccessRel(table=[[TPCDS, CATALOG_SALES]]): rowcount = 1441548.0, cumulative cost = {1441548.0 rows, 1441549.0 cpu, 0.0 io}\n"));
}
@Test public void testQuery58() {
@@ -147,7 +148,9 @@ public class TpcdsTest {
@Ignore("work in progress")
@Test public void testQuery72Plan() {
- checkQuery(72).planContains("xx");
+ checkQuery(72)
+ .withHook(Hook.PROGRAM, HANDLER)
+ .planContains("xx");
}
private OptiqAssert.AssertQuery checkQuery(int i) {
[3/3] git commit: Fix build breakage on JDK 1.6 due to missing method
BitSet.previousClearBit.
Posted by jh...@apache.org.
Fix build breakage on JDK 1.6 due to missing method BitSet.previousClearBit.
Project: http://git-wip-us.apache.org/repos/asf/incubator-optiq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-optiq/commit/e4b5fe77
Tree: http://git-wip-us.apache.org/repos/asf/incubator-optiq/tree/e4b5fe77
Diff: http://git-wip-us.apache.org/repos/asf/incubator-optiq/diff/e4b5fe77
Branch: refs/heads/master
Commit: e4b5fe77abdbe637f7cd87f5c46fc571db87bdb2
Parents: 5091422
Author: Julian Hyde <jh...@apache.org>
Authored: Thu Jul 31 14:36:01 2014 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Thu Jul 31 14:36:01 2014 -0700
----------------------------------------------------------------------
.../java/net/hydromatic/optiq/util/BitSets.java | 17 ++++++++++++++
.../rel/rules/OptimizeBushyJoinRule.java | 3 ++-
.../net/hydromatic/optiq/util/BitSetsTest.java | 24 +++++++++++++++++++-
3 files changed, 42 insertions(+), 2 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/e4b5fe77/core/src/main/java/net/hydromatic/optiq/util/BitSets.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/util/BitSets.java b/core/src/main/java/net/hydromatic/optiq/util/BitSets.java
index 78394db..b9cbf11 100644
--- a/core/src/main/java/net/hydromatic/optiq/util/BitSets.java
+++ b/core/src/main/java/net/hydromatic/optiq/util/BitSets.java
@@ -229,6 +229,23 @@ public final class BitSets {
}
return s;
}
+
+ /** Returns the previous clear bit.
+ *
+ * <p>Has same behavior as {@link BitSet#previousClearBit}, but that method
+ * does not exist before 1.7. */
+ public static int previousClearBit(BitSet bitSet, int fromIndex) {
+ if (fromIndex < -1) {
+ throw new IndexOutOfBoundsException();
+ }
+ while (fromIndex >= 0) {
+ if (!bitSet.get(fromIndex)) {
+ return fromIndex;
+ }
+ --fromIndex;
+ }
+ return -1;
+ }
}
// End BitSets.java
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/e4b5fe77/core/src/main/java/org/eigenbase/rel/rules/OptimizeBushyJoinRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/rules/OptimizeBushyJoinRule.java b/core/src/main/java/org/eigenbase/rel/rules/OptimizeBushyJoinRule.java
index 135a105..586f041 100644
--- a/core/src/main/java/org/eigenbase/rel/rules/OptimizeBushyJoinRule.java
+++ b/core/src/main/java/org/eigenbase/rel/rules/OptimizeBushyJoinRule.java
@@ -124,7 +124,8 @@ public class OptimizeBushyJoinRule extends RelOptRule {
if (edgeOrdinal == -1) {
// No more edges. Are there any un-joined vertexes?
final Vertex lastVertex = Util.last(vertexes);
- final int z = lastVertex.factors.previousClearBit(lastVertex.id - 1);
+ final int z =
+ BitSets.previousClearBit(lastVertex.factors, lastVertex.id - 1);
if (z < 0) {
break;
}
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/e4b5fe77/core/src/test/java/net/hydromatic/optiq/util/BitSetsTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/net/hydromatic/optiq/util/BitSetsTest.java b/core/src/test/java/net/hydromatic/optiq/util/BitSetsTest.java
index 3c7076d..9aae64b 100644
--- a/core/src/test/java/net/hydromatic/optiq/util/BitSetsTest.java
+++ b/core/src/test/java/net/hydromatic/optiq/util/BitSetsTest.java
@@ -30,6 +30,7 @@ import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertThat;
import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
/**
* Unit test for {@link net.hydromatic.optiq.util.BitSets}.
@@ -164,7 +165,28 @@ public class BitSetsTest {
list = ImmutableIntList.of(2, 70, 5, 0);
assertThat(BitSets.of(list), equalTo(BitSets.of(0, 2, 5, 70)));
}
+
+ /**
+ * Tests the method
+ * {@link net.hydromatic.optiq.util.BitSets#previousClearBit(java.util.BitSet, int)}.
+ */
+ @Test public void testPreviousClearBit() {
+ assertThat(BitSets.previousClearBit(BitSets.of(), 10), equalTo(10));
+ assertThat(BitSets.previousClearBit(BitSets.of(), 0), equalTo(0));
+ assertThat(BitSets.previousClearBit(BitSets.of(), -1), equalTo(-1));
+ try {
+ final int actual = BitSets.previousClearBit(BitSets.of(), -2);
+ fail("expected exception, got " + actual);
+ } catch (IndexOutOfBoundsException e) {
+ // ok
+ }
+ assertThat(BitSets.previousClearBit(BitSets.of(0, 1, 3, 4), 4), equalTo(2));
+ assertThat(BitSets.previousClearBit(BitSets.of(0, 1, 3, 4), 3), equalTo(2));
+ assertThat(BitSets.previousClearBit(BitSets.of(0, 1, 3, 4), 2), equalTo(2));
+ assertThat(BitSets.previousClearBit(BitSets.of(0, 1, 3, 4), 1),
+ equalTo(-1));
+ assertThat(BitSets.previousClearBit(BitSets.of(1, 3, 4), 1), equalTo(0));
+ }
}
// End BitSetsTest.java
-
[2/3] git commit: Fix cartesian products in OptimizeBushyJoinRule.
Posted by jh...@apache.org.
Fix cartesian products in OptimizeBushyJoinRule.
Project: http://git-wip-us.apache.org/repos/asf/incubator-optiq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-optiq/commit/50914222
Tree: http://git-wip-us.apache.org/repos/asf/incubator-optiq/tree/50914222
Diff: http://git-wip-us.apache.org/repos/asf/incubator-optiq/diff/50914222
Branch: refs/heads/master
Commit: 50914222eb1c4dd210f0f96eb4876dee085bf832
Parents: 249b00c
Author: Julian Hyde <jh...@apache.org>
Authored: Wed Jul 30 17:09:21 2014 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Wed Jul 30 17:09:21 2014 -0700
----------------------------------------------------------------------
.../rel/rules/OptimizeBushyJoinRule.java | 50 +++++++++-----------
.../net/hydromatic/optiq/tools/PlannerTest.java | 33 +++++++++++++
2 files changed, 56 insertions(+), 27 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/50914222/core/src/main/java/org/eigenbase/rel/rules/OptimizeBushyJoinRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/rules/OptimizeBushyJoinRule.java b/core/src/main/java/org/eigenbase/rel/rules/OptimizeBushyJoinRule.java
index 1c6c4fa..135a105 100644
--- a/core/src/main/java/org/eigenbase/rel/rules/OptimizeBushyJoinRule.java
+++ b/core/src/main/java/org/eigenbase/rel/rules/OptimizeBushyJoinRule.java
@@ -115,22 +115,31 @@ public class OptimizeBushyJoinRule extends RelOptRule {
};
final List<LoptMultiJoin.Edge> usedEdges = Lists.newArrayList();
- while (!unusedEdges.isEmpty()) {
+ for (;;) {
final int edgeOrdinal = chooseBestEdge(unusedEdges, edgeComparator);
if (pw != null) {
trace(vertexes, unusedEdges, usedEdges, edgeOrdinal, pw);
}
- final LoptMultiJoin.Edge bestEdge = remove(unusedEdges, edgeOrdinal);
- usedEdges.add(bestEdge);
-
- // For now, assume that the edge is between precisely two factors.
- // 1-factor conditions have probably been pushed down,
- // and 3-or-more-factor conditions are advanced. (TODO:)
- // Therefore, for now, the factors that are merged are exactly the factors
- // on this edge.
- BitSet merged = bestEdge.factors;
- assert merged.cardinality() == 2;
- final int[] factors = BitSets.toArray(merged);
+ final int[] factors;
+ if (edgeOrdinal == -1) {
+ // No more edges. Are there any un-joined vertexes?
+ final Vertex lastVertex = Util.last(vertexes);
+ final int z = lastVertex.factors.previousClearBit(lastVertex.id - 1);
+ if (z < 0) {
+ break;
+ }
+ factors = new int[] {z, lastVertex.id};
+ } else {
+ final LoptMultiJoin.Edge bestEdge = unusedEdges.get(edgeOrdinal);
+
+ // For now, assume that the edge is between precisely two factors.
+ // 1-factor conditions have probably been pushed down,
+ // and 3-or-more-factor conditions are advanced. (TODO:)
+ // Therefore, for now, the factors that are merged are exactly the
+ // factors on this edge.
+ assert bestEdge.factors.cardinality() == 2;
+ factors = BitSets.toArray(bestEdge.factors);
+ }
// Determine which factor is to be on the LHS of the join.
final int majorFactor;
@@ -149,7 +158,7 @@ public class OptimizeBushyJoinRule extends RelOptRule {
// the join can now be used.
final BitSet newFactors =
BitSets.union(majorVertex.factors, minorVertex.factors);
- final List<RexNode> conditions = Lists.newArrayList(bestEdge.condition);
+ final List<RexNode> conditions = Lists.newArrayList();
final Iterator<LoptMultiJoin.Edge> edgeIterator = unusedEdges.iterator();
while (edgeIterator.hasNext()) {
LoptMultiJoin.Edge edge = edgeIterator.next();
@@ -179,6 +188,7 @@ public class OptimizeBushyJoinRule extends RelOptRule {
// This vertex has fewer rows (1k rows) -- a fact that is critical to
// decisions made later. (Hence "greedy" algorithm not "simple".)
// The adjacent edges are modified.
+ final BitSet merged = BitSets.of(minorFactor, majorFactor);
for (int i = 0; i < unusedEdges.size(); i++) {
final LoptMultiJoin.Edge edge = unusedEdges.get(i);
if (edge.factors.intersects(merged)) {
@@ -270,20 +280,6 @@ public class OptimizeBushyJoinRule extends RelOptRule {
pw.flush();
}
- /** Removes the element of a list at a given ordinal, moving the last element
- * into its place. This is an efficient means of removing an element from an
- * array list if you do not mind the order of the list changing. */
- private static <E> E remove(List<E> list, int ordinal) {
- final int lastOrdinal = list.size() - 1;
- final E last = list.remove(lastOrdinal);
- if (ordinal == lastOrdinal) {
- return last;
- }
- final E e = list.get(ordinal);
- list.set(ordinal, last);
- return e;
- }
-
int chooseBestEdge(List<LoptMultiJoin.Edge> edges,
Comparator<LoptMultiJoin.Edge> comparator) {
return minPos(edges, comparator);
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/50914222/core/src/test/java/net/hydromatic/optiq/tools/PlannerTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/net/hydromatic/optiq/tools/PlannerTest.java b/core/src/test/java/net/hydromatic/optiq/tools/PlannerTest.java
index d544858..e96bf84 100644
--- a/core/src/test/java/net/hydromatic/optiq/tools/PlannerTest.java
+++ b/core/src/test/java/net/hydromatic/optiq/tools/PlannerTest.java
@@ -526,6 +526,39 @@ public class PlannerTest {
+ " EnumerableTableAccessRel(table=[[foodmart2, sales_fact_1997]])\n");
}
+ /** Tests the bushy join algorithm where one table does not join to
+ * anything. */
+ @Test public void testBushyCrossJoin() throws Exception {
+ checkBushy("select * from \"sales_fact_1997\"\n"
+ + "join \"customer\" using (\"customer_id\")\n"
+ + "cross join \"department\"",
+ "EnumerableProjectRel(product_id=[$0], time_id=[$1], customer_id=[$2], promotion_id=[$3], store_id=[$4], store_sales=[$5], store_cost=[$6], unit_sales=[$7], customer_id0=[$8], account_num=[$9], lname=[$10], fname=[$11], mi=[$12], address1=[$13], address2=[$14], address3=[$15], address4=[$16], city=[$17], state_province=[$18], postal_code=[$19], country=[$20], customer_region_id=[$21], phone1=[$22], phone2=[$23], birthdate=[$24], marital_status=[$25], yearly_income=[$26], gender=[$27], total_children=[$28], num_children_at_home=[$29], education=[$30], date_accnt_opened=[$31], member_card=[$32], occupation=[$33], houseowner=[$34], num_cars_owned=[$35], fullname=[$36], department_id=[$37], department_description=[$38])\n"
+ + " EnumerableProjectRel($f0=[$31], $f1=[$32], $f2=[$33], $f3=[$34], $f4=[$35], $f5=[$36], $f6=[$37], $f7=[$38], $f8=[$2], $f9=[$3], $f10=[$4], $f11=[$5], $f12=[$6], $f13=[$7], $f14=[$8], $f15=[$9], $f16=[$10], $f17=[$11], $f18=[$12], $f19=[$13], $f20=[$14], $f21=[$15], $f22=[$16], $f23=[$17], $f24=[$18], $f25=[$19], $f26=[$20], $f27=[$21], $f28=[$22], $f29=[$23], $f30=[$24], $f31=[$25], $f32=[$26], $f33=[$27], $f34=[$28], $f35=[$29], $f36=[$30], $f37=[$0], $f38=[$1])\n"
+ + " EnumerableJoinRel(condition=[true], joinType=[inner])\n"
+ + " EnumerableTableAccessRel(table=[[foodmart2, department]])\n"
+ + " EnumerableJoinRel(condition=[=($31, $0)], joinType=[inner])\n"
+ + " EnumerableTableAccessRel(table=[[foodmart2, customer]])\n"
+ + " EnumerableTableAccessRel(table=[[foodmart2, sales_fact_1997]])");
+ }
+
+ /** Tests the bushy join algorithm against a query where not all tables have a
+ * join condition to the others. */
+ @Test public void testBushyCrossJoin2() throws Exception {
+ checkBushy("select * from \"sales_fact_1997\"\n"
+ + "join \"customer\" using (\"customer_id\")\n"
+ + "cross join \"department\"\n"
+ + "join \"employee\" using (\"department_id\")",
+ "EnumerableProjectRel(product_id=[$0], time_id=[$1], customer_id=[$2], promotion_id=[$3], store_id=[$4], store_sales=[$5], store_cost=[$6], unit_sales=[$7], customer_id0=[$8], account_num=[$9], lname=[$10], fname=[$11], mi=[$12], address1=[$13], address2=[$14], address3=[$15], address4=[$16], city=[$17], state_province=[$18], postal_code=[$19], country=[$20], customer_region_id=[$21], phone1=[$22], phone2=[$23], birthdate=[$24], marital_status=[$25], yearly_income=[$26], gender=[$27], total_children=[$28], num_children_at_home=[$29], education=[$30], date_accnt_opened=[$31], member_card=[$32], occupation=[$33], houseowner=[$34], num_cars_owned=[$35], fullname=[$36], department_id=[$37], department_description=[$38], employee_id=[$39], full_name=[$40], first_name=[$41], last_name=[$42], position_id=[$43], position_title=[$44], store_id0=[$45], department_id0=[$46], birth_date=[$47], hire_date=[$48], end_date=[$49], salary=[$50], supervisor_id=[$51], education_level=[$52], mar
ital_status0=[$53], gender0=[$54], management_role=[$55])\n"
+ + " EnumerableProjectRel($f0=[$48], $f1=[$49], $f2=[$50], $f3=[$51], $f4=[$52], $f5=[$53], $f6=[$54], $f7=[$55], $f8=[$19], $f9=[$20], $f10=[$21], $f11=[$22], $f12=[$23], $f13=[$24], $f14=[$25], $f15=[$26], $f16=[$27], $f17=[$28], $f18=[$29], $f19=[$30], $f20=[$31], $f21=[$32], $f22=[$33], $f23=[$34], $f24=[$35], $f25=[$36], $f26=[$37], $f27=[$38], $f28=[$39], $f29=[$40], $f30=[$41], $f31=[$42], $f32=[$43], $f33=[$44], $f34=[$45], $f35=[$46], $f36=[$47], $f37=[$0], $f38=[$1], $f39=[$2], $f40=[$3], $f41=[$4], $f42=[$5], $f43=[$6], $f44=[$7], $f45=[$8], $f46=[$9], $f47=[$10], $f48=[$11], $f49=[$12], $f50=[$13], $f51=[$14], $f52=[$15], $f53=[$16], $f54=[$17], $f55=[$18])\n"
+ + " EnumerableJoinRel(condition=[true], joinType=[inner])\n"
+ + " EnumerableJoinRel(condition=[=($0, $9)], joinType=[inner])\n"
+ + " EnumerableTableAccessRel(table=[[foodmart2, department]])\n"
+ + " EnumerableTableAccessRel(table=[[foodmart2, employee]])\n"
+ + " EnumerableJoinRel(condition=[=($31, $0)], joinType=[inner])\n"
+ + " EnumerableTableAccessRel(table=[[foodmart2, customer]])\n"
+ + " EnumerableTableAccessRel(table=[[foodmart2, sales_fact_1997]])\n");
+ }
+
/** Checks that a query returns a particular plan, using a planner with
* OptimizeBushyJoinRule enabled. */
private void checkBushy(String sql, String expected) throws Exception {