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/15 05:14:56 UTC
[1/2] [OPTIQ-371] Implement JOIN whose ON clause contains mixed equi
and theta
Repository: incubator-optiq
Updated Branches:
refs/heads/master 111947cd0 -> f25e0d837
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/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 0897aa7..0052f76 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
@@ -23,6 +23,7 @@ import net.hydromatic.optiq.tools.Program;
import net.hydromatic.optiq.tools.Programs;
import org.eigenbase.util.Bug;
+import org.eigenbase.util.Holder;
import org.eigenbase.util.Pair;
import com.google.common.base.Function;
@@ -41,15 +42,21 @@ 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
+ Function<Pair<List<Prepare.Materialization>, Holder<Program>>, Void>
+ handler(final boolean bushy) {
+ return new Function<Pair<List<Prepare.Materialization>, Holder<Program>>,
+ Void>() {
+ public Void apply(
+ Pair<List<Prepare.Materialization>, Holder<Program>> pair) {
+ pair.right.set(
+ Programs.sequence(
+ Programs.heuristicJoinOrder(Programs.RULE_SET, bushy),
+ Programs.CALC_PROGRAM));
+ return null;
+ }
+ };
+ }
private static String schema(String name, String scaleFactor) {
return " {\n"
@@ -108,30 +115,30 @@ public class TpcdsTest {
@Test public void testQuery17Plan() {
//noinspection unchecked
checkQuery(17)
- .withHook(Hook.PROGRAM, HANDLER)
+ .withHook(Hook.PROGRAM, handler(true))
.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"
+ + "EnumerableCalcRel(expr#0..11=[{inputs}], expr#12=[/($t5, $t4)], expr#13=[/($t8, $t7)], expr#14=[/($t11, $t10)], proj#0..5=[{exprs}], STORE_SALES_QUANTITYCOV=[$t12], AS_STORE_RETURNS_QUANTITYCOUNT=[$t6], AS_STORE_RETURNS_QUANTITYAVE=[$t7], AS_STORE_RETURNS_QUANTITYSTDEV=[$t8], STORE_RETURNS_QUANTITYCOV=[$t13], CATALOG_SALES_QUANTITYCOUNT=[$t9], CATALOG_SALES_QUANTITYAVE=[$t10], CATALOG_SALES_QUANTITYSTDEV=[$t14], CATALOG_SALES_QUANTITYCOV=[$t14]): rowcount = 5.434029018852197E26, cumulative cost = {1.618185849567114E30 rows, 1.2672155671963324E30 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, 1.2509134801397759E30 cpu, 0.0 io}\n"
+ + " EnumerableCalcRel(expr#0..12=[{inputs}], expr#13=[/($t4, $t5)], expr#14=[CAST($t13):JavaType(class java.lang.Integer)], expr#15=[*($t4, $t4)], expr#16=[/($t15, $t5)], expr#17=[-($t6, $t16)], expr#18=[1], expr#19=[=($t5, $t18)], expr#20=[null], expr#21=[-($t5, $t18)], expr#22=[CASE($t19, $t20, $t21)], expr#23=[/($t17, $t22)], expr#24=[0.5], expr#25=[POWER($t23, $t24)], expr#26=[CAST($t25):JavaType(class java.lang.Integer)], expr#27=[/($t8, $t7)], expr#28=[CAST($t27):JavaType(class java.lang.Integer)], expr#29=[*($t8, $t8)], expr#30=[/($t29, $t7)], expr#31=[-($t9, $t30)], expr#32=[=($t7, $t18)], expr#33=[-($t7, $t18)], expr#34=[CASE($t32, $t20, $t33)], expr#35=[/($t31, $t34)], expr#36=[POWER($t35, $t24)], expr#37=[CAST($t36):JavaType(class java.lang.Integer)], expr#38=[/($t11, $t10)], expr#39=[CAST($t38):JavaType(class java.lang.Integer)], expr#40=[*($t11, $t11)], expr#41=[/($t40, $t10)], expr#42=[-($t12, $t41)], expr#43=[=($t10, $t18)], expr#44=[-($t10, $t18)],
expr#45=[CASE($t43, $t20, $t44)], expr#46=[/($t42, $t45)], expr#47=[POWER($t46, $t24)], expr#48=[CAST($t47):JavaType(class java.lang.Integer)], proj#0..3=[{exprs}], STORE_SALES_QUANTITYAVE=[$t14], STORE_SALES_QUANTITYSTDEV=[$t26], AS_STORE_RETURNS_QUANTITYCOUNT=[$t7], AS_STORE_RETURNS_QUANTITYAVE=[$t28], AS_STORE_RETURNS_QUANTITYSTDEV=[$t37], CATALOG_SALES_QUANTITYCOUNT=[$t10], CATALOG_SALES_QUANTITYAVE=[$t39], $f11=[$t48]): rowcount = 5.434029018852197E26, cumulative cost = {1.1954863841615548E28 rows, 1.2503700772378907E30 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, 1.2172225002228922E30 cpu, 0.0 io}\n"
+ + " EnumerableCalcRel(expr#0..211=[{inputs}], expr#212=[*($t89, $t89)], expr#213=[*($t140, $t140)], expr#214=[*($t196, $t196)], I_ITEM_ID=[$t58], I_ITEM_DESC=[$t61], S_STATE=[$t24], SS_QUANTITY=[$t89], SR_RETURN_QUANTITY=[$t140], CS_QUANTITY=[$t196], $f6=[$t212], $f7=[$t213], $f8=[$t214]): rowcount = 5.434029018852197E27, cumulative cost = {1.0868058037845108E28 rows, 1.2172225002228922E30 cpu, 0.0 io}\n"
+ + " EnumerableJoinRel(condition=[AND(=($82, $133), =($81, $132), =($88, $139))], joinType=[inner]): rowcount = 5.434029018852197E27, cumulative cost = {5.434029018992911E27 rows, 1.8579845E7 cpu, 0.0 io}\n"
+ + " EnumerableJoinRel(condition=[=($0, $86)], joinType=[inner]): rowcount = 2.3008402586892598E13, cumulative cost = {4.8588854672853766E13 rows, 7354409.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"
+ + " EnumerableJoinRel(condition=[=($0, $50)], joinType=[inner]): rowcount = 1.2782445881607E13, cumulative cost = {1.279800620431234E13 rows, 7354396.0 cpu, 0.0 io}\n"
+ + " EnumerableCalcRel(expr#0..27=[{inputs}], expr#28=[CAST($t15):VARCHAR(6) CHARACTER SET \"ISO-8859-1\" COLLATE \"ISO-8859-1$en_US$primary\"], expr#29=['1998Q1'], expr#30=[=($t28, $t29)], proj#0..27=[{exprs}], $condition=[$t30]): rowcount = 10957.35, cumulative cost = {84006.35 rows, 4455990.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"
+ + " EnumerableJoinRel(condition=[AND(=($31, $79), =($30, $91))], joinType=[inner]): rowcount = 6.9978029381741304E16, cumulative cost = {6.9978054204658736E16 rows, 1.1225436E7 cpu, 0.0 io}\n"
+ + " EnumerableJoinRel(condition=[=($0, $28)], joinType=[inner]): rowcount = 7.87597881975E8, cumulative cost = {7.884434222216867E8 rows, 5035701.0 cpu, 0.0 io}\n"
+ + " EnumerableCalcRel(expr#0..27=[{inputs}], expr#28=['1998Q1'], expr#29=[=($t15, $t28)], expr#30=['1998Q2'], expr#31=[=($t15, $t30)], expr#32=['1998Q3'], expr#33=[=($t15, $t32)], expr#34=[OR($t29, $t31, $t33)], proj#0..27=[{exprs}], $condition=[$t34]): rowcount = 18262.25, cumulative cost = {91311.25 rows, 4748186.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"
+ + " EnumerableJoinRel(condition=[=($0, $28)], joinType=[inner]): rowcount = 3.94888649445E9, cumulative cost = {3.9520401026966867E9 rows, 6189735.0 cpu, 0.0 io}\n"
+ + " EnumerableCalcRel(expr#0..27=[{inputs}], expr#28=['1998Q1'], expr#29=[=($t15, $t28)], expr#30=['1998Q2'], expr#31=[=($t15, $t30)], expr#32=['1998Q3'], expr#33=[=($t15, $t32)], expr#34=[OR($t29, $t31, $t33)], proj#0..27=[{exprs}], $condition=[$t34]): rowcount = 18262.25, cumulative cost = {91311.25 rows, 4748186.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"));
}
@@ -148,10 +155,16 @@ public class TpcdsTest {
@Ignore("work in progress")
@Test public void testQuery72Plan() {
checkQuery(72)
- .withHook(Hook.PROGRAM, HANDLER)
+ .withHook(Hook.PROGRAM, handler(true))
.planContains("xx");
}
+ @Test public void testQuery95() {
+ checkQuery(95)
+ .withHook(Hook.PROGRAM, handler(false))
+ .runs();
+ }
+
private OptiqAssert.AssertQuery checkQuery(int i) {
final Query query = Query.of(i);
String sql = query.sql(-1, new Random(0));
@@ -170,6 +183,10 @@ public class TpcdsTest {
// Work around OPTIQ-304: Support '<DATE> + <INTEGER>'.
sql = sql.replace("+ 5", "+ interval '5' day");
break;
+ case 95:
+ sql = sql.replace("60 days", "interval '60' day");
+ sql = sql.replace("d_date between '", "d_date between date '");
+ break;
}
return with()
.query(sql.replaceAll("tpcds\\.", "tpcds_01."));
[2/2] git commit: [OPTIQ-371] Implement JOIN whose ON clause contains
mixed equi and theta
Posted by jh...@apache.org.
[OPTIQ-371] Implement JOIN whose ON clause contains mixed equi and theta
Add class EnumerableSemiJoin and EnumerableSemiJoinRule.
Add test cases for EXISTS and NOT EXISTS.
Add Enumerables.semiJoin, which will eventually go into linq4j.
Change the constructor of SemiJoinRel to allow it to be sub-classed.
Project: http://git-wip-us.apache.org/repos/asf/incubator-optiq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-optiq/commit/f25e0d83
Tree: http://git-wip-us.apache.org/repos/asf/incubator-optiq/tree/f25e0d83
Diff: http://git-wip-us.apache.org/repos/asf/incubator-optiq/diff/f25e0d83
Branch: refs/heads/master
Commit: f25e0d837e8305e460f62bcefd37561526d4cea2
Parents: 111947c
Author: Julian Hyde <jh...@apache.org>
Authored: Thu Aug 14 18:40:00 2014 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Thu Aug 14 18:48:24 2014 -0700
----------------------------------------------------------------------
.../net/hydromatic/optiq/BuiltinMethod.java | 2 +
.../hydromatic/optiq/impl/jdbc/JdbcRules.java | 7 +-
.../optiq/prepare/OptiqPrepareImpl.java | 1 +
.../hydromatic/optiq/rules/java/JavaRules.java | 155 +++++++++++++++++--
.../hydromatic/optiq/runtime/Enumerables.java | 122 +++++++++++++++
.../net/hydromatic/optiq/tools/Programs.java | 8 +-
.../main/java/org/eigenbase/rel/JoinInfo.java | 93 +++++++++--
.../org/eigenbase/rel/metadata/RelMdUtil.java | 3 +-
.../rel/rules/AddRedundantSemiJoinRule.java | 1 +
.../org/eigenbase/rel/rules/EquiJoinRel.java | 3 +-
.../rel/rules/LoptSemiJoinOptimizer.java | 7 +-
.../rel/rules/NestedLoopsJoinRule.java | 2 +-
.../rel/rules/PushFilterPastJoinRule.java | 9 +-
.../rel/rules/PushSemiJoinPastFilterRule.java | 1 +
.../rel/rules/PushSemiJoinPastJoinRule.java | 8 +-
.../rel/rules/PushSemiJoinPastProjectRule.java | 4 +-
.../org/eigenbase/rel/rules/SemiJoinRel.java | 27 ++--
.../org/eigenbase/rel/rules/SemiJoinRule.java | 5 +-
.../java/org/eigenbase/relopt/RelOptUtil.java | 40 +++--
.../relopt/volcano/VolcanoPlanner.java | 1 +
.../optiq/runtime/EnumerablesTest.java | 92 +++++++++++
.../test/JdbcFrontJdbcBackLinqMiddleTest.java | 23 ++-
.../net/hydromatic/optiq/test/JdbcTest.java | 51 +++++-
.../net/hydromatic/optiq/test/OptiqSuite.java | 2 +
.../net/hydromatic/optiq/tools/PlannerTest.java | 24 +--
core/src/test/resources/sql/misc.oq | 147 +++++++++++++++++-
.../hydromatic/optiq/impl/tpcds/TpcdsTest.java | 67 +++++---
27 files changed, 790 insertions(+), 115 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/net/hydromatic/optiq/BuiltinMethod.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/BuiltinMethod.java b/core/src/main/java/net/hydromatic/optiq/BuiltinMethod.java
index 0dce5af..6ad8ff6 100644
--- a/core/src/main/java/net/hydromatic/optiq/BuiltinMethod.java
+++ b/core/src/main/java/net/hydromatic/optiq/BuiltinMethod.java
@@ -66,6 +66,8 @@ public enum BuiltinMethod {
String.class, Function1.class),
JOIN(ExtendedEnumerable.class, "join", Enumerable.class, Function1.class,
Function1.class, Function2.class),
+ SEMI_JOIN(Enumerables.class, "semiJoin", Enumerable.class, Enumerable.class,
+ Function1.class, Function1.class),
SELECT(ExtendedEnumerable.class, "select", Function1.class),
SELECT2(ExtendedEnumerable.class, "select", Function2.class),
SELECT_MANY(ExtendedEnumerable.class, "selectMany", Function1.class),
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/net/hydromatic/optiq/impl/jdbc/JdbcRules.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/impl/jdbc/JdbcRules.java b/core/src/main/java/net/hydromatic/optiq/impl/jdbc/JdbcRules.java
index 76c19b1..0ef97ec 100644
--- a/core/src/main/java/net/hydromatic/optiq/impl/jdbc/JdbcRules.java
+++ b/core/src/main/java/net/hydromatic/optiq/impl/jdbc/JdbcRules.java
@@ -194,12 +194,15 @@ public class JdbcRules {
}
@Override
- public JdbcJoinRel copy(RelTraitSet traitSet, RexNode conditionExpr,
+ public JdbcJoinRel copy(RelTraitSet traitSet, RexNode condition,
RelNode left, RelNode right, JoinRelType joinType,
boolean semiJoinDone) {
+ final JoinInfo joinInfo = JoinInfo.of(left, right, condition);
+ assert joinInfo.isEqui();
try {
return new JdbcJoinRel(getCluster(), traitSet, left, right,
- conditionExpr, leftKeys, rightKeys, joinType, variablesStopped);
+ condition, joinInfo.leftKeys, joinInfo.rightKeys, joinType,
+ variablesStopped);
} catch (InvalidRelException e) {
// Semantic error not possible. Must be a bug. Convert to
// internal error.
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/net/hydromatic/optiq/prepare/OptiqPrepareImpl.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/prepare/OptiqPrepareImpl.java b/core/src/main/java/net/hydromatic/optiq/prepare/OptiqPrepareImpl.java
index 12b9a0a..f1f890b 100644
--- a/core/src/main/java/net/hydromatic/optiq/prepare/OptiqPrepareImpl.java
+++ b/core/src/main/java/net/hydromatic/optiq/prepare/OptiqPrepareImpl.java
@@ -104,6 +104,7 @@ public class OptiqPrepareImpl implements OptiqPrepare {
private static final List<RelOptRule> DEFAULT_RULES =
ImmutableList.of(
JavaRules.ENUMERABLE_JOIN_RULE,
+ JavaRules.ENUMERABLE_SEMI_JOIN_RULE,
JavaRules.ENUMERABLE_PROJECT_RULE,
JavaRules.ENUMERABLE_FILTER_RULE,
JavaRules.ENUMERABLE_AGGREGATE_RULE,
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java b/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
index 43d94a8..3ca908f 100644
--- a/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
+++ b/core/src/main/java/net/hydromatic/optiq/rules/java/JavaRules.java
@@ -34,6 +34,7 @@ import org.eigenbase.rel.convert.ConverterRule;
import org.eigenbase.rel.metadata.RelColumnMapping;
import org.eigenbase.rel.metadata.RelMetadataQuery;
import org.eigenbase.rel.rules.EquiJoinRel;
+import org.eigenbase.rel.rules.SemiJoinRel;
import org.eigenbase.relopt.*;
import org.eigenbase.reltype.*;
import org.eigenbase.rex.*;
@@ -67,6 +68,9 @@ public class JavaRules {
public static final RelOptRule ENUMERABLE_JOIN_RULE =
new EnumerableJoinRule();
+ public static final RelOptRule ENUMERABLE_SEMI_JOIN_RULE =
+ new EnumerableSemiJoinRule();
+
public static final String[] LEFT_RIGHT = new String[]{"left", "right"};
private static final boolean B = false;
@@ -97,19 +101,23 @@ public class JavaRules {
}
newInputs.add(input);
}
- final JoinInfo info =
- JoinInfo.of(newInputs.get(0), newInputs.get(1), join.getCondition());
- if (!info.isEqui()) {
- // EnumerableJoinRel only supports equi-join
+ final RelNode left = newInputs.get(0);
+ final RelNode right = newInputs.get(1);
+ final JoinInfo info = JoinInfo.of(left, right, join.getCondition());
+ if (!info.isEqui() && join.getJoinType() != JoinRelType.INNER) {
+ // EnumerableJoinRel only supports equi-join. We can put a filter on top
+ // if it is an inner join.
return null;
}
+ final RelOptCluster cluster = join.getCluster();
+ RelNode newRel;
try {
- return new EnumerableJoinRel(
- join.getCluster(),
+ newRel = new EnumerableJoinRel(
+ cluster,
join.getTraitSet().replace(EnumerableConvention.INSTANCE),
- newInputs.get(0),
- newInputs.get(1),
- join.getCondition(),
+ left,
+ right,
+ info.getEquiCondition(left, right, cluster.getRexBuilder()),
info.leftKeys,
info.rightKeys,
join.getJoinType(),
@@ -118,6 +126,11 @@ public class JavaRules {
LOGGER.fine(e.toString());
return null;
}
+ if (!info.isEqui()) {
+ newRel = new EnumerableFilterRel(cluster, newRel.getTraitSet(),
+ newRel, info.getRemaining(cluster.getRexBuilder()));
+ }
+ return newRel;
}
}
@@ -150,12 +163,15 @@ public class JavaRules {
}
@Override
- public EnumerableJoinRel copy(RelTraitSet traitSet, RexNode conditionExpr,
+ public EnumerableJoinRel copy(RelTraitSet traitSet, RexNode condition,
RelNode left, RelNode right, JoinRelType joinType,
boolean semiJoinDone) {
+ final JoinInfo joinInfo = JoinInfo.of(left, right, condition);
+ assert joinInfo.isEqui();
try {
return new EnumerableJoinRel(getCluster(), traitSet, left, right,
- conditionExpr, leftKeys, rightKeys, joinType, variablesStopped);
+ condition, joinInfo.leftKeys, joinInfo.rightKeys, joinType,
+ variablesStopped);
} catch (InvalidRelException e) {
// Semantic error not possible. Must be a bug. Convert to
// internal error.
@@ -297,6 +313,123 @@ public class JavaRules {
}
}
+ private static class EnumerableSemiJoinRule extends ConverterRule {
+ private EnumerableSemiJoinRule() {
+ super(SemiJoinRel.class,
+ Convention.NONE,
+ EnumerableConvention.INSTANCE,
+ "EnumerableSemiJoinRule");
+ }
+
+ @Override
+ public RelNode convert(RelNode rel) {
+ final SemiJoinRel semiJoin = (SemiJoinRel) rel;
+ List<RelNode> newInputs = new ArrayList<RelNode>();
+ for (RelNode input : semiJoin.getInputs()) {
+ if (!(input.getConvention() instanceof EnumerableConvention)) {
+ input =
+ convert(input,
+ input.getTraitSet().replace(EnumerableConvention.INSTANCE));
+ }
+ newInputs.add(input);
+ }
+ try {
+ return new EnumerableSemiJoinRel(
+ semiJoin.getCluster(),
+ semiJoin.getTraitSet().replace(EnumerableConvention.INSTANCE),
+ newInputs.get(0),
+ newInputs.get(1),
+ semiJoin.getCondition(),
+ semiJoin.leftKeys,
+ semiJoin.rightKeys);
+ } catch (InvalidRelException e) {
+ LOGGER.fine(e.toString());
+ return null;
+ }
+ }
+ }
+
+ /** Implementation of {@link org.eigenbase.rel.rules.SemiJoinRel} in
+ * {@link EnumerableConvention enumerable calling convention}. */
+ public static class EnumerableSemiJoinRel
+ extends SemiJoinRel
+ implements EnumerableRel {
+ protected EnumerableSemiJoinRel(
+ RelOptCluster cluster,
+ RelTraitSet traits,
+ RelNode left,
+ RelNode right,
+ RexNode condition,
+ ImmutableIntList leftKeys,
+ ImmutableIntList rightKeys)
+ throws InvalidRelException {
+ super(cluster, traits, left, right, condition, leftKeys, rightKeys);
+ }
+
+ @Override
+ public SemiJoinRel copy(RelTraitSet traitSet, RexNode condition,
+ RelNode left, RelNode right, JoinRelType joinType,
+ boolean semiJoinDone) {
+ assert joinType == JoinRelType.INNER;
+ final JoinInfo joinInfo = JoinInfo.of(left, right, condition);
+ assert joinInfo.isEqui();
+ try {
+ return new EnumerableSemiJoinRel(getCluster(), traitSet, left, right,
+ condition, joinInfo.leftKeys, joinInfo.rightKeys);
+ } catch (InvalidRelException e) {
+ // Semantic error not possible. Must be a bug. Convert to
+ // internal error.
+ throw new AssertionError(e);
+ }
+ }
+
+ @Override
+ public RelOptCost computeSelfCost(RelOptPlanner planner) {
+ double rowCount = RelMetadataQuery.getRowCount(this);
+
+ // Right-hand input is the "build", and hopefully small, input.
+ final double rightRowCount = right.getRows();
+ final double leftRowCount = left.getRows();
+ if (Double.isInfinite(leftRowCount)) {
+ rowCount = leftRowCount;
+ } else {
+ rowCount += Util.nLogN(leftRowCount);
+ }
+ if (Double.isInfinite(rightRowCount)) {
+ rowCount = rightRowCount;
+ } else {
+ rowCount += rightRowCount;
+ }
+ return planner.getCostFactory().makeCost(rowCount, 0, 0).multiplyBy(.01d);
+ }
+
+ public Result implement(EnumerableRelImplementor implementor, Prefer pref) {
+ BlockBuilder builder = new BlockBuilder();
+ final Result leftResult =
+ implementor.visitChild(this, 0, (EnumerableRel) left, pref);
+ Expression leftExpression =
+ builder.append(
+ "left", leftResult.block);
+ final Result rightResult =
+ implementor.visitChild(this, 1, (EnumerableRel) right, pref);
+ Expression rightExpression =
+ builder.append(
+ "right", rightResult.block);
+ final PhysType physType = leftResult.physType;
+ return implementor.result(
+ physType,
+ builder.append(
+ Expressions.call(
+ BuiltinMethod.SEMI_JOIN.method,
+ Expressions.list(
+ leftExpression,
+ rightExpression,
+ leftResult.physType.generateAccessor(leftKeys),
+ rightResult.physType.generateAccessor(rightKeys))))
+ .toBlock());
+ }
+ }
+
/**
* Utilities for generating programs in the Enumerable (functional)
* style.
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/net/hydromatic/optiq/runtime/Enumerables.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/runtime/Enumerables.java b/core/src/main/java/net/hydromatic/optiq/runtime/Enumerables.java
new file mode 100644
index 0000000..c804719
--- /dev/null
+++ b/core/src/main/java/net/hydromatic/optiq/runtime/Enumerables.java
@@ -0,0 +1,122 @@
+/*
+// Licensed to the Apache Software Foundation (ASF) under one or more
+// contributor license agreements. See the NOTICE file distributed with
+// this work for additional information regarding copyright ownership.
+// The ASF licenses this file to you under the Apache License, Version 2.0
+// (the "License"); you may not use this file except in compliance with
+// the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+*/
+package net.hydromatic.optiq.runtime;
+
+import net.hydromatic.linq4j.AbstractEnumerable;
+import net.hydromatic.linq4j.Enumerable;
+import net.hydromatic.linq4j.Enumerator;
+import net.hydromatic.linq4j.function.EqualityComparer;
+import net.hydromatic.linq4j.function.Function1;
+import net.hydromatic.linq4j.function.Predicate1;
+
+import org.eigenbase.util.Bug;
+
+/**
+ * Utilities for processing {@link net.hydromatic.linq4j.Enumerable}
+ * collections.
+ *
+ * <p>This class is a place to put things not yet added to linq4j.
+ * Methods are subject to removal without notice.</p>
+ */
+public class Enumerables {
+ private Enumerables() {}
+
+ /**
+ * Returns elements of {@code outer} for which there is a member of
+ * {@code inner} with a matching key.
+ */
+ public static <TSource, TInner, TKey> Enumerable<TSource> semiJoin(
+ final Enumerable<TSource> outer, final Enumerable<TInner> inner,
+ final Function1<TSource, TKey> outerKeySelector,
+ final Function1<TInner, TKey> innerKeySelector) {
+ Bug.upgrade("move into linq4j");
+ return semiJoin(outer, inner, outerKeySelector, innerKeySelector, null);
+ }
+
+ /**
+ * Returns elements of {@code outer} for which there is a member of
+ * {@code inner} with a matching key. A specified
+ * {@code EqualityComparer<TSource>} is used to compare keys.
+ */
+ public static <TSource, TInner, TKey> Enumerable<TSource> semiJoin(
+ final Enumerable<TSource> outer, final Enumerable<TInner> inner,
+ final Function1<TSource, TKey> outerKeySelector,
+ final Function1<TInner, TKey> innerKeySelector,
+ final EqualityComparer<TKey> comparer) {
+ return new AbstractEnumerable<TSource>() {
+ public Enumerator<TSource> enumerator() {
+ final Enumerable<TKey> innerLookup =
+ comparer == null
+ ? inner.select(innerKeySelector).distinct()
+ : inner.select(innerKeySelector).distinct(comparer);
+
+ return Enumerables.where(outer.enumerator(),
+ new Predicate1<TSource>() {
+ public boolean apply(TSource v0) {
+ final TKey key = outerKeySelector.apply(v0);
+ return innerLookup.contains(key);
+ }
+ });
+ }
+ };
+ }
+
+ /**
+ * Filters a sequence of values based on a
+ * predicate.
+ */
+ public static <TSource> Enumerable<TSource> where(
+ final Enumerable<TSource> source, final Predicate1<TSource> predicate) {
+ assert predicate != null;
+ return new AbstractEnumerable<TSource>() {
+ public Enumerator<TSource> enumerator() {
+ final Enumerator<TSource> enumerator = source.enumerator();
+ return Enumerables.where(enumerator, predicate);
+ }
+ };
+ }
+
+ private static <TSource> Enumerator<TSource> where(
+ final Enumerator<TSource> enumerator,
+ final Predicate1<TSource> predicate) {
+ return new Enumerator<TSource>() {
+ public TSource current() {
+ return enumerator.current();
+ }
+
+ public boolean moveNext() {
+ while (enumerator.moveNext()) {
+ if (predicate.apply(enumerator.current())) {
+ return true;
+ }
+ }
+ return false;
+ }
+
+ public void reset() {
+ enumerator.reset();
+ }
+
+ public void close() {
+ enumerator.close();
+ }
+ };
+ }
+
+}
+
+// End Enumerables.java
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/net/hydromatic/optiq/tools/Programs.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/net/hydromatic/optiq/tools/Programs.java b/core/src/main/java/net/hydromatic/optiq/tools/Programs.java
index fa22e71..4214df3 100644
--- a/core/src/main/java/net/hydromatic/optiq/tools/Programs.java
+++ b/core/src/main/java/net/hydromatic/optiq/tools/Programs.java
@@ -63,9 +63,14 @@ public class Programs {
MergeFilterOntoCalcRule.INSTANCE,
MergeProjectOntoCalcRule.INSTANCE);
+ /** Program that converts filters and projects to calcs. */
+ public static final Program CALC_PROGRAM =
+ hep(CALC_RULES, true, new DefaultRelMetadataProvider());
+
public static final ImmutableSet<RelOptRule> RULE_SET =
ImmutableSet.of(
JavaRules.ENUMERABLE_JOIN_RULE,
+ JavaRules.ENUMERABLE_SEMI_JOIN_RULE,
JavaRules.ENUMERABLE_PROJECT_RULE,
JavaRules.ENUMERABLE_FILTER_RULE,
JavaRules.ENUMERABLE_AGGREGATE_RULE,
@@ -234,8 +239,7 @@ public class Programs {
// Second planner pass to do physical "tweaks". This the first time that
// EnumerableCalcRel is introduced.
- final Program program2 =
- hep(CALC_RULES, true, new DefaultRelMetadataProvider());
+ final Program program2 = CALC_PROGRAM;
return sequence(program1, program2);
}
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/org/eigenbase/rel/JoinInfo.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/JoinInfo.java b/core/src/main/java/org/eigenbase/rel/JoinInfo.java
index 72b3c76..98eae0a 100644
--- a/core/src/main/java/org/eigenbase/rel/JoinInfo.java
+++ b/core/src/main/java/org/eigenbase/rel/JoinInfo.java
@@ -16,12 +16,17 @@
*/
package org.eigenbase.rel;
+import java.util.AbstractList;
import java.util.ArrayList;
import java.util.BitSet;
import java.util.List;
import org.eigenbase.relopt.RelOptUtil;
+import org.eigenbase.reltype.RelDataType;
+import org.eigenbase.rex.RexBuilder;
import org.eigenbase.rex.RexNode;
+import org.eigenbase.rex.RexUtil;
+import org.eigenbase.sql.fun.SqlStdOperatorTable;
import org.eigenbase.util.ImmutableIntList;
import org.eigenbase.util.mapping.IntPair;
@@ -39,17 +44,14 @@ import com.google.common.base.Preconditions;
* equi-joins and sub-class {@link org.eigenbase.rel.rules.EquiJoinRel}.</p>
*
* @see JoinRelBase#analyzeCondition() */
-public class JoinInfo {
+public abstract class JoinInfo {
public final ImmutableIntList leftKeys;
public final ImmutableIntList rightKeys;
- public final RexNode remaining;
/** Creates a JoinInfo. */
- public JoinInfo(ImmutableIntList leftKeys, ImmutableIntList rightKeys,
- RexNode remaining) {
+ protected JoinInfo(ImmutableIntList leftKeys, ImmutableIntList rightKeys) {
this.leftKeys = Preconditions.checkNotNull(leftKeys);
this.rightKeys = Preconditions.checkNotNull(rightKeys);
- this.remaining = Preconditions.checkNotNull(remaining);
assert leftKeys.size() == rightKeys.size();
}
@@ -60,15 +62,24 @@ public class JoinInfo {
RexNode remaining =
RelOptUtil.splitJoinCondition(left, right, condition, leftKeys,
rightKeys);
- return new JoinInfo(ImmutableIntList.copyOf(leftKeys),
- ImmutableIntList.copyOf(rightKeys), remaining);
+ if (remaining.isAlwaysTrue()) {
+ return new EquiJoinInfo(ImmutableIntList.copyOf(leftKeys),
+ ImmutableIntList.copyOf(rightKeys));
+ } else {
+ return new NonEquiJoinInfo(ImmutableIntList.copyOf(leftKeys),
+ ImmutableIntList.copyOf(rightKeys), remaining);
+ }
}
- /** Returns whether this is an equi-join. */
- public boolean isEqui() {
- return remaining.isAlwaysTrue();
+ /** Creates an equi-join. */
+ public static JoinInfo of(ImmutableIntList leftKeys,
+ ImmutableIntList rightKeys) {
+ return new EquiJoinInfo(leftKeys, rightKeys);
}
+ /** Returns whether this is an equi-join. */
+ public abstract boolean isEqui();
+
/** Returns a list of (left, right) key ordinals. */
public List<IntPair> pairs() {
return IntPair.zip(leftKeys, rightKeys);
@@ -81,6 +92,68 @@ public class JoinInfo {
public BitSet rightSet() {
return BitSets.of(rightKeys);
}
+
+ public abstract RexNode getRemaining(RexBuilder rexBuilder);
+
+ public RexNode getEquiCondition(final RelNode left, final RelNode right,
+ final RexBuilder rexBuilder) {
+ final List<RelDataType> leftTypes =
+ RelOptUtil.getFieldTypeList(left.getRowType());
+ final List<RelDataType> rightTypes =
+ RelOptUtil.getFieldTypeList(right.getRowType());
+ return RexUtil.composeConjunction(rexBuilder,
+ new AbstractList<RexNode>() {
+ @Override public RexNode get(int index) {
+ final int leftKey = leftKeys.get(index);
+ final int rightKey = rightKeys.get(index);
+ return rexBuilder.makeCall(SqlStdOperatorTable.EQUALS,
+ rexBuilder.makeInputRef(leftTypes.get(leftKey), leftKey),
+ rexBuilder.makeInputRef(rightTypes.get(rightKey),
+ leftTypes.size() + rightKey));
+ }
+
+ @Override public int size() {
+ return leftKeys.size();
+ }
+ },
+ false);
+ }
+
+ /** JoinInfo that represents an equi-join. */
+ private static class EquiJoinInfo extends JoinInfo {
+ protected EquiJoinInfo(ImmutableIntList leftKeys,
+ ImmutableIntList rightKeys) {
+ super(leftKeys, rightKeys);
+ }
+
+ @Override public boolean isEqui() {
+ return true;
+ }
+
+ @Override public RexNode getRemaining(RexBuilder rexBuilder) {
+ return rexBuilder.makeLiteral(true);
+ }
+ }
+
+ /** JoinInfo that represents a non equi-join. */
+ private static class NonEquiJoinInfo extends JoinInfo {
+ public final RexNode remaining;
+
+ protected NonEquiJoinInfo(ImmutableIntList leftKeys,
+ ImmutableIntList rightKeys, RexNode remaining) {
+ super(leftKeys, rightKeys);
+ this.remaining = Preconditions.checkNotNull(remaining);
+ assert !remaining.isAlwaysTrue();
+ }
+
+ @Override public boolean isEqui() {
+ return false;
+ }
+
+ @Override public RexNode getRemaining(RexBuilder rexBuilder) {
+ return remaining;
+ }
+ }
}
// End JoinInfo.java
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/org/eigenbase/rel/metadata/RelMdUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/metadata/RelMdUtil.java b/core/src/main/java/org/eigenbase/rel/metadata/RelMdUtil.java
index 3de12ec..32cf7ef 100644
--- a/core/src/main/java/org/eigenbase/rel/metadata/RelMdUtil.java
+++ b/core/src/main/java/org/eigenbase/rel/metadata/RelMdUtil.java
@@ -443,7 +443,7 @@ public class RelMdUtil {
final JoinInfo joinInfo = JoinInfo.of(leftChild, rightChild, predicate);
BitSets.populate(leftJoinCols, joinInfo.leftKeys);
BitSets.populate(rightJoinCols, joinInfo.rightKeys);
- return joinInfo.remaining;
+ return joinInfo.getRemaining(leftChild.getCluster().getRexBuilder());
}
/**
@@ -652,6 +652,7 @@ public class RelMdUtil {
joinRel,
predList,
joinType,
+ joinType == JoinRelType.INNER,
!joinType.generatesNullsOnLeft(),
!joinType.generatesNullsOnRight(),
joinFilters,
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/org/eigenbase/rel/rules/AddRedundantSemiJoinRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/rules/AddRedundantSemiJoinRule.java b/core/src/main/java/org/eigenbase/rel/rules/AddRedundantSemiJoinRule.java
index 9cdaedc..b816201 100644
--- a/core/src/main/java/org/eigenbase/rel/rules/AddRedundantSemiJoinRule.java
+++ b/core/src/main/java/org/eigenbase/rel/rules/AddRedundantSemiJoinRule.java
@@ -62,6 +62,7 @@ public class AddRedundantSemiJoinRule extends RelOptRule {
RelNode semiJoin =
new SemiJoinRel(
origJoinRel.getCluster(),
+ origJoinRel.getCluster().traitSetOf(Convention.NONE),
origJoinRel.getLeft(),
origJoinRel.getRight(),
origJoinRel.getCondition(),
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/org/eigenbase/rel/rules/EquiJoinRel.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/rules/EquiJoinRel.java b/core/src/main/java/org/eigenbase/rel/rules/EquiJoinRel.java
index 21c92ac..7698137 100644
--- a/core/src/main/java/org/eigenbase/rel/rules/EquiJoinRel.java
+++ b/core/src/main/java/org/eigenbase/rel/rules/EquiJoinRel.java
@@ -56,8 +56,7 @@ public abstract class EquiJoinRel extends JoinRelBase {
@Override
public JoinInfo analyzeCondition() {
- return new JoinInfo(leftKeys, rightKeys,
- getCluster().getRexBuilder().makeLiteral(true));
+ return JoinInfo.of(leftKeys, rightKeys);
}
}
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/org/eigenbase/rel/rules/LoptSemiJoinOptimizer.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/rules/LoptSemiJoinOptimizer.java b/core/src/main/java/org/eigenbase/rel/rules/LoptSemiJoinOptimizer.java
index 162b00f..edeee7f 100644
--- a/core/src/main/java/org/eigenbase/rel/rules/LoptSemiJoinOptimizer.java
+++ b/core/src/main/java/org/eigenbase/rel/rules/LoptSemiJoinOptimizer.java
@@ -24,6 +24,7 @@ import org.eigenbase.relopt.*;
import org.eigenbase.rex.*;
import org.eigenbase.sql.SqlKind;
import org.eigenbase.sql.fun.*;
+import org.eigenbase.util.ImmutableIntList;
import net.hydromatic.optiq.util.BitSets;
@@ -304,11 +305,12 @@ public class LoptSemiJoinOptimizer {
SemiJoinRel semiJoin =
new SemiJoinRel(
factRel.getCluster(),
+ factRel.getCluster().traitSetOf(Convention.NONE),
factRel,
dimRel,
semiJoinCondition,
- truncatedLeftKeys,
- truncatedRightKeys);
+ ImmutableIntList.copyOf(truncatedLeftKeys),
+ ImmutableIntList.copyOf(truncatedRightKeys));
return semiJoin;
}
@@ -572,6 +574,7 @@ public class LoptSemiJoinOptimizer {
SemiJoinRel chosenSemiJoin =
new SemiJoinRel(
factRel.getCluster(),
+ factRel.getCluster().traitSetOf(Convention.NONE),
factRel,
chosenSemiJoins[bestDimIdx],
semiJoin.getCondition(),
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/org/eigenbase/rel/rules/NestedLoopsJoinRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/rules/NestedLoopsJoinRule.java b/core/src/main/java/org/eigenbase/rel/rules/NestedLoopsJoinRule.java
index ff8e19d..9f1cd39 100644
--- a/core/src/main/java/org/eigenbase/rel/rules/NestedLoopsJoinRule.java
+++ b/core/src/main/java/org/eigenbase/rel/rules/NestedLoopsJoinRule.java
@@ -119,7 +119,7 @@ public class NestedLoopsJoinRule extends RelOptRule {
join.getCluster(),
left,
right,
- joinInfo.remaining,
+ joinInfo.getRemaining(join.getCluster().getRexBuilder()),
correlationList,
join.getJoinType());
call.transformTo(newRel);
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/org/eigenbase/rel/rules/PushFilterPastJoinRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/rules/PushFilterPastJoinRule.java b/core/src/main/java/org/eigenbase/rel/rules/PushFilterPastJoinRule.java
index a742643..c1a19bd 100644
--- a/core/src/main/java/org/eigenbase/rel/rules/PushFilterPastJoinRule.java
+++ b/core/src/main/java/org/eigenbase/rel/rules/PushFilterPastJoinRule.java
@@ -65,6 +65,9 @@ public abstract class PushFilterPastJoinRule extends RelOptRule {
protected void perform(RelOptRuleCall call, FilterRelBase filter,
JoinRelBase join) {
+ if (join instanceof SemiJoinRel) {
+ return;
+ }
final List<RexNode> joinFilters =
RelOptUtil.conjunctions(join.getCondition());
@@ -109,6 +112,7 @@ public abstract class PushFilterPastJoinRule extends RelOptRule {
join,
aboveFilters,
join.getJoinType(),
+ !(join instanceof EquiJoinRel),
!join.getJoinType().generatesNullsOnLeft(),
!join.getJoinType().generatesNullsOnRight(),
joinFilters,
@@ -125,14 +129,15 @@ public abstract class PushFilterPastJoinRule extends RelOptRule {
if (RelOptUtil.classifyFilters(
join,
joinFilters,
- null,
+ join.getJoinType(),
+ false,
!join.getJoinType().generatesNullsOnRight(),
!join.getJoinType().generatesNullsOnLeft(),
joinFilters,
leftFilters,
rightFilters,
joinTypeHolder,
- smart)) {
+ false)) {
filterPushed = true;
}
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/org/eigenbase/rel/rules/PushSemiJoinPastFilterRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/rules/PushSemiJoinPastFilterRule.java b/core/src/main/java/org/eigenbase/rel/rules/PushSemiJoinPastFilterRule.java
index c1ad5cc..2757373 100644
--- a/core/src/main/java/org/eigenbase/rel/rules/PushSemiJoinPastFilterRule.java
+++ b/core/src/main/java/org/eigenbase/rel/rules/PushSemiJoinPastFilterRule.java
@@ -50,6 +50,7 @@ public class PushSemiJoinPastFilterRule extends RelOptRule {
RelNode newSemiJoin =
new SemiJoinRel(
semiJoin.getCluster(),
+ semiJoin.getCluster().traitSetOf(Convention.NONE),
filter.getChild(),
semiJoin.getRight(),
semiJoin.getCondition(),
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/org/eigenbase/rel/rules/PushSemiJoinPastJoinRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/rules/PushSemiJoinPastJoinRule.java b/core/src/main/java/org/eigenbase/rel/rules/PushSemiJoinPastJoinRule.java
index f26704f..633dc94 100644
--- a/core/src/main/java/org/eigenbase/rel/rules/PushSemiJoinPastJoinRule.java
+++ b/core/src/main/java/org/eigenbase/rel/rules/PushSemiJoinPastJoinRule.java
@@ -22,6 +22,7 @@ import org.eigenbase.rel.*;
import org.eigenbase.relopt.*;
import org.eigenbase.reltype.*;
import org.eigenbase.rex.*;
+import org.eigenbase.util.ImmutableIntList;
/**
* PushSemiJoinPastJoinRule implements the rule for pushing semi-joins down in a
@@ -62,8 +63,8 @@ public class PushSemiJoinPastJoinRule extends RelOptRule {
if (join instanceof SemiJoinRel) {
return;
}
- List<Integer> leftKeys = semiJoin.getLeftKeys();
- List<Integer> rightKeys = semiJoin.getRightKeys();
+ final ImmutableIntList leftKeys = semiJoin.getLeftKeys();
+ final ImmutableIntList rightKeys = semiJoin.getRightKeys();
// X is the left child of the join below the semi-join
// Y is the right child of the join below the semi-join
@@ -152,10 +153,11 @@ public class PushSemiJoinPastJoinRule extends RelOptRule {
SemiJoinRel newSemiJoin =
new SemiJoinRel(
semiJoin.getCluster(),
+ semiJoin.getCluster().traitSetOf(Convention.NONE),
leftSemiJoinOp,
semiJoin.getRight(),
newSemiJoinFilter,
- newLeftKeys,
+ ImmutableIntList.copyOf(newLeftKeys),
rightKeys);
RelNode leftJoinRel;
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/org/eigenbase/rel/rules/PushSemiJoinPastProjectRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/rules/PushSemiJoinPastProjectRule.java b/core/src/main/java/org/eigenbase/rel/rules/PushSemiJoinPastProjectRule.java
index dd32f09..26a9e0c 100644
--- a/core/src/main/java/org/eigenbase/rel/rules/PushSemiJoinPastProjectRule.java
+++ b/core/src/main/java/org/eigenbase/rel/rules/PushSemiJoinPastProjectRule.java
@@ -22,6 +22,7 @@ import org.eigenbase.rel.*;
import org.eigenbase.relopt.*;
import org.eigenbase.reltype.*;
import org.eigenbase.rex.*;
+import org.eigenbase.util.ImmutableIntList;
import org.eigenbase.util.Pair;
/**
@@ -72,10 +73,11 @@ public class PushSemiJoinPastProjectRule extends RelOptRule {
SemiJoinRel newSemiJoin =
new SemiJoinRel(
semiJoin.getCluster(),
+ semiJoin.getCluster().traitSetOf(Convention.NONE),
project.getChild(),
semiJoin.getRight(),
newCondition,
- newLeftKeys,
+ ImmutableIntList.copyOf(newLeftKeys),
semiJoin.getRightKeys());
// Create the new projection. Note that the projection expressions
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/org/eigenbase/rel/rules/SemiJoinRel.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/rules/SemiJoinRel.java b/core/src/main/java/org/eigenbase/rel/rules/SemiJoinRel.java
index a85ad78..81d6883 100644
--- a/core/src/main/java/org/eigenbase/rel/rules/SemiJoinRel.java
+++ b/core/src/main/java/org/eigenbase/rel/rules/SemiJoinRel.java
@@ -32,13 +32,14 @@ import com.google.common.collect.ImmutableSet;
* condition, where the output only contains the columns from the left join
* input.
*/
-public final class SemiJoinRel extends EquiJoinRel {
+public class SemiJoinRel extends EquiJoinRel {
//~ Constructors -----------------------------------------------------------
/**
* Creates a SemiJoinRel.
*
* @param cluster cluster that join belongs to
+ * @param traitSet Traits
* @param left left join input
* @param right right join input
* @param condition join condition
@@ -47,19 +48,20 @@ public final class SemiJoinRel extends EquiJoinRel {
*/
public SemiJoinRel(
RelOptCluster cluster,
+ RelTraitSet traitSet,
RelNode left,
RelNode right,
RexNode condition,
- List<Integer> leftKeys,
- List<Integer> rightKeys) {
+ ImmutableIntList leftKeys,
+ ImmutableIntList rightKeys) {
super(
cluster,
- cluster.traitSetOf(Convention.NONE),
+ traitSet,
left,
right,
condition,
- ImmutableIntList.copyOf(leftKeys),
- ImmutableIntList.copyOf(rightKeys),
+ leftKeys,
+ rightKeys,
JoinRelType.INNER,
ImmutableSet.<String>of());
}
@@ -67,16 +69,13 @@ public final class SemiJoinRel extends EquiJoinRel {
//~ Methods ----------------------------------------------------------------
@Override
- public SemiJoinRel copy(RelTraitSet traitSet, RexNode conditionExpr,
+ public SemiJoinRel copy(RelTraitSet traitSet, RexNode condition,
RelNode left, RelNode right, JoinRelType joinType, boolean semiJoinDone) {
assert joinType == JoinRelType.INNER;
- return new SemiJoinRel(
- getCluster(),
- left,
- right,
- conditionExpr,
- getLeftKeys(),
- getRightKeys());
+ final JoinInfo joinInfo = JoinInfo.of(left, right, condition);
+ assert joinInfo.isEqui();
+ return new SemiJoinRel(getCluster(), traitSet, left, right, condition,
+ joinInfo.leftKeys, joinInfo.rightKeys);
}
// implement RelNode
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/org/eigenbase/rel/rules/SemiJoinRule.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/rel/rules/SemiJoinRule.java b/core/src/main/java/org/eigenbase/rel/rules/SemiJoinRule.java
index f523530..db4f970 100644
--- a/core/src/main/java/org/eigenbase/rel/rules/SemiJoinRule.java
+++ b/core/src/main/java/org/eigenbase/rel/rules/SemiJoinRule.java
@@ -24,6 +24,7 @@ import org.eigenbase.rel.JoinInfo;
import org.eigenbase.rel.JoinRelBase;
import org.eigenbase.rel.ProjectRelBase;
import org.eigenbase.rel.RelNode;
+import org.eigenbase.relopt.Convention;
import org.eigenbase.relopt.RelOptRule;
import org.eigenbase.relopt.RelOptRuleCall;
import org.eigenbase.relopt.RelOptUtil;
@@ -75,7 +76,9 @@ public class SemiJoinRule extends RelOptRule {
newRightKeys.add(aggregateKeys.get(key));
}
final SemiJoinRel semiJoin =
- new SemiJoinRel(join.getCluster(), left, aggregate.getChild(),
+ new SemiJoinRel(join.getCluster(),
+ join.getCluster().traitSetOf(Convention.NONE),
+ left, aggregate.getChild(),
join.getCondition(), joinInfo.leftKeys,
ImmutableIntList.copyOf(newRightKeys));
final ProjectRelBase newProject =
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java b/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
index 6bd4571..2593966 100644
--- a/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
+++ b/core/src/main/java/org/eigenbase/relopt/RelOptUtil.java
@@ -1846,20 +1846,22 @@ public abstract class RelOptUtil {
*
* @param joinRel join node
* @param filters filters to be classified
- * @param joinType join type; determines whether filters can be pushed
- * into the ON clause
+ * @param joinType join type
+ * @param pushInto whether filters can be pushed into the ON clause
* @param pushLeft true if filters can be pushed to the left
* @param pushRight true if filters can be pushed to the right
* @param joinFilters list of filters to push to the join
* @param leftFilters list of filters to push to the left child
* @param rightFilters list of filters to push to the right child
* @param smart Whether to try to strengthen the join type
- * @return true if at least one filter was pushed
+ * @return whether at least one filter was pushed, or join type was
+ * strengthened
*/
public static boolean classifyFilters(
RelNode joinRel,
List<RexNode> filters,
JoinRelType joinType,
+ boolean pushInto,
boolean pushLeft,
boolean pushRight,
List<RexNode> joinFilters,
@@ -1943,42 +1945,46 @@ public abstract class RelOptUtil {
}
filtersToRemove.add(filter);
- // if the filter can't be pushed to either child and the join
+ } else {
+ // If the filter can't be pushed to either child and the join
// is an inner join, push them to the join if they originated
// from above the join
- } else if (joinType == JoinRelType.INNER) {
- joinFilters.add(filter);
- filtersToRemove.add(filter);
+ if (joinType == JoinRelType.INNER && pushInto) {
+ if (!joinFilters.contains(filter)) {
+ joinFilters.add(filter);
+ }
+ filtersToRemove.add(filter);
+ }
// If the filter will only evaluate to true if fields from the left
// are not null, and the left is null-generating, then we can make the
// left. Similarly for the right.
- } else {
if (smart
- && joinType != null
&& joinType.generatesNullsOnRight()
&& Strong.is(filter, rightBitmap)) {
- filtersToRemove.add(filter);
joinType = joinType.cancelNullsOnRight();
joinTypeHolder.set(joinType);
- if (!joinFilters.contains(filter)) {
- joinFilters.add(filter);
+ if (pushInto) {
+ filtersToRemove.add(filter);
+ if (!joinFilters.contains(filter)) {
+ joinFilters.add(filter);
+ }
}
}
if (smart
- && joinType != null
&& joinType.generatesNullsOnLeft()
&& Strong.is(filter, leftBitmap)) {
filtersToRemove.add(filter);
joinType = joinType.cancelNullsOnLeft();
joinTypeHolder.set(joinType);
- if (!joinFilters.contains(filter)) {
- joinFilters.add(filter);
+ if (pushInto) {
+ filtersToRemove.add(filter);
+ if (!joinFilters.contains(filter)) {
+ joinFilters.add(filter);
+ }
}
}
}
-
- // else, leave the filter where it is
}
// Remove filters after the loop, to prevent concurrent modification.
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/main/java/org/eigenbase/relopt/volcano/VolcanoPlanner.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/eigenbase/relopt/volcano/VolcanoPlanner.java b/core/src/main/java/org/eigenbase/relopt/volcano/VolcanoPlanner.java
index 6b56724..20739ae 100644
--- a/core/src/main/java/org/eigenbase/relopt/volcano/VolcanoPlanner.java
+++ b/core/src/main/java/org/eigenbase/relopt/volcano/VolcanoPlanner.java
@@ -901,6 +901,7 @@ public class VolcanoPlanner extends AbstractRelOptPlanner {
addRule(PushFilterPastJoinRule.JOIN);
addRule(AbstractConverter.ExpandConversionRule.INSTANCE);
addRule(SwapJoinRule.INSTANCE);
+ addRule(SemiJoinRule.INSTANCE);
if (OptiqPrepareImpl.COMMUTE) {
addRule(CommutativeJoinRule.INSTANCE);
}
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/test/java/net/hydromatic/optiq/runtime/EnumerablesTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/net/hydromatic/optiq/runtime/EnumerablesTest.java b/core/src/test/java/net/hydromatic/optiq/runtime/EnumerablesTest.java
new file mode 100644
index 0000000..7aa2fcb
--- /dev/null
+++ b/core/src/test/java/net/hydromatic/optiq/runtime/EnumerablesTest.java
@@ -0,0 +1,92 @@
+/*
+// Licensed to the Apache Software Foundation (ASF) under one or more
+// contributor license agreements. See the NOTICE file distributed with
+// this work for additional information regarding copyright ownership.
+// The ASF licenses this file to you under the Apache License, Version 2.0
+// (the "License"); you may not use this file except in compliance with
+// the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+*/
+package net.hydromatic.optiq.runtime;
+
+import net.hydromatic.linq4j.Linq4j;
+import net.hydromatic.linq4j.function.Function1;
+import net.hydromatic.linq4j.function.Functions;
+
+import org.junit.Test;
+
+import java.util.Arrays;
+
+import static org.hamcrest.CoreMatchers.equalTo;
+import static org.junit.Assert.assertThat;
+
+/**
+ * Unit tests for {@link net.hydromatic.optiq.runtime.Enumerables}.
+ */
+public class EnumerablesTest {
+ @Test public void testSemiJoin() {
+ assertThat(
+ Enumerables.semiJoin(
+ Linq4j.asEnumerable(
+ Arrays.asList(
+ new Emp(10, "Fred"),
+ new Emp(20, "Theodore"),
+ new Emp(20, "Sebastian"),
+ new Emp(30, "Joe"))),
+ Linq4j.asEnumerable(
+ Arrays.asList(
+ new Dept(20, "Sales"),
+ new Dept(15, "Marketing"))),
+ new Function1<Emp, Integer>() {
+ public Integer apply(Emp a0) {
+ return a0.deptno;
+ }
+ },
+ new Function1<Dept, Integer>() {
+ public Integer apply(Dept a0) {
+ return a0.deptno;
+ }
+ },
+ Functions.<Integer>identityComparer()).toList().toString(),
+ equalTo("[Emp(20, Theodore), Emp(20, Sebastian)]"));
+ }
+
+ /** Employee record. */
+ private static class Emp {
+ final int deptno;
+ final String name;
+
+ Emp(int deptno, String name) {
+ this.deptno = deptno;
+ this.name = name;
+ }
+
+ @Override public String toString() {
+ return "Emp(" + deptno + ", " + name + ")";
+ }
+ }
+
+ /** Department record. */
+ private static class Dept {
+ final int deptno;
+ final String name;
+
+ Dept(int deptno, String name) {
+ this.deptno = deptno;
+ this.name = name;
+ }
+
+ @Override public String toString() {
+ return "Dept(" + deptno + ", " + name + ")";
+ }
+ }
+}
+
+// End EnumerablesTest.java
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/test/java/net/hydromatic/optiq/test/JdbcFrontJdbcBackLinqMiddleTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/net/hydromatic/optiq/test/JdbcFrontJdbcBackLinqMiddleTest.java b/core/src/test/java/net/hydromatic/optiq/test/JdbcFrontJdbcBackLinqMiddleTest.java
index b95d17b..577b7ab 100644
--- a/core/src/test/java/net/hydromatic/optiq/test/JdbcFrontJdbcBackLinqMiddleTest.java
+++ b/core/src/test/java/net/hydromatic/optiq/test/JdbcFrontJdbcBackLinqMiddleTest.java
@@ -113,20 +113,31 @@ public class JdbcFrontJdbcBackLinqMiddleTest {
.returns("C=7\n");
}
- /** Tests that a theta join (a join whose condition cannot be decomposed
- * into input0.x = input1.x and ... input0.z = input1.z) throws a reasonably
- * civilized "cannot be implemented" exception. Of course, we'd like to be
- * able to implement it one day. */
+ /** Tests a theta join: a join whose condition cannot be decomposed
+ * into input0.x = input1.x and ... input0.z = input1.z.
+ *
+ * <p>Currently, the query can be planned, but the plan is not efficient (uses
+ * cartesian product).</p>
+ */
@Test public void testJoinTheta() {
that()
- .with(OptiqAssert.Config.JDBC_FOODMART)
+ .with(OptiqAssert.Config.FOODMART_CLONE)
.query(
"select count(*) from (\n"
+ " select *\n"
+ " from \"foodmart\".\"sales_fact_1997\" as s\n"
+ " join \"foodmart\".\"customer\" as c\n"
+ " on s.\"customer_id\" - c.\"customer_id\" = 0)")
- .throws_(" could not be implemented");
+ .explainContains(
+ "EnumerableAggregateRel(group=[{}], EXPR$0=[COUNT()])\n"
+ + " EnumerableCalcRel(expr#0..1=[{inputs}], expr#2=[0], expr#3=[-($t0, $t1)], expr#4=[=($t3, $t2)], DUMMY=[$t2], $condition=[$t4])\n"
+ + " EnumerableJoinRel(condition=[true], joinType=[inner])\n"
+ + " JdbcToEnumerableConverter\n"
+ + " JdbcProjectRel(customer_id=[$2])\n"
+ + " JdbcTableScan(table=[[foodmart, sales_fact_1997]])\n"
+ + " JdbcToEnumerableConverter\n"
+ + " JdbcProjectRel(customer_id=[$0])\n"
+ + " JdbcTableScan(table=[[foodmart, customer]])");
}
@Test public void testJoinGroupByEmpty() {
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/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 c6c98ee..169f76f 100644
--- a/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
+++ b/core/src/test/java/net/hydromatic/optiq/test/JdbcTest.java
@@ -1481,6 +1481,26 @@ public class JdbcTest {
+ "full_name=Terry Anderson\n");
}
+ /** A join that has both equi and non-equi conditions.
+ *
+ * <p>Test case for
+ * <a href="https://issues.apache.org/jira/browse/OPTIQ-371">OPTIQ-371</a>,
+ * "Cannot implement JOIN whose ON clause contains mixed equi and theta". */
+ @Test public void testEquiThetaJoin() {
+ OptiqAssert.that()
+ .with(OptiqAssert.Config.REGULAR)
+ .query(
+ "select e.\"empid\", d.\"name\", e.\"name\"\n"
+ + "from \"hr\".\"emps\" as e\n"
+ + "join \"hr\".\"depts\" as d\n"
+ + "on e.\"deptno\" = d.\"deptno\"\n"
+ + "and e.\"name\" <> d.\"name\"\n")
+ .returns(
+ "empid=100; name=Sales; name=Bill\n"
+ + "empid=150; name=Sales; name=Sebastian\n"
+ + "empid=110; name=Sales; name=Theodore\n");
+ }
+
/** Test case for
* <a href="https://issues.apache.org/jira/browse/OPTIQ-35">OPTIQ-35</a>,
* "Support parenthesized sub-clause in JOIN". */
@@ -2438,7 +2458,7 @@ public class JdbcTest {
"EnumerableAggregateRel(group=[{0}], m0=[COUNT($1)])\n"
+ " EnumerableAggregateRel(group=[{0, 1}])\n"
+ " EnumerableCalcRel(expr#0..3=[{inputs}], c0=[$t1], unit_sales=[$t3])\n"
- + " EnumerableJoinRel(condition=[=($2, $0)], joinType=[inner])\n"
+ + " EnumerableJoinRel(condition=[=($0, $2)], joinType=[inner])\n"
+ " EnumerableCalcRel(expr#0..9=[{inputs}], expr#10=[CAST($t4):INTEGER], expr#11=[1997], expr#12=[=($t10, $t11)], time_id=[$t0], the_year=[$t4], $condition=[$t12])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, time_by_day]])\n"
+ " EnumerableCalcRel(expr#0..7=[{inputs}], time_id=[$t1], unit_sales=[$t7])\n"
@@ -2462,6 +2482,35 @@ public class JdbcTest {
.returns("c0=1997; m0=85452\n");
}
+ /** Tests a simple IN query implemented as a semi-join. */
+ @Test public void testSimpleIn() {
+ OptiqAssert.that()
+ .with(OptiqAssert.Config.REGULAR)
+ .query(
+ "select * from \"hr\".\"depts\" where \"deptno\" in (\n"
+ + " select \"deptno\" from \"hr\".\"emps\"\n"
+ + " where \"empid\" < 150)")
+ .convertContains(
+ "ProjectRel(deptno=[$0], name=[$1], employees=[$2])\n"
+ + " JoinRel(condition=[=($3, $4)], joinType=[inner])\n"
+ + " ProjectRel($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$0])\n"
+ + " EnumerableTableAccessRel(table=[[hr, depts]])\n"
+ + " AggregateRel(group=[{0}])\n"
+ + " ProjectRel(deptno=[$1])\n"
+ + " FilterRel(condition=[<($0, 150)])\n"
+ + " ProjectRel(empid=[$0], deptno=[$1])\n"
+ + " EnumerableTableAccessRel(table=[[hr, emps]])")
+ .explainContains(
+ "EnumerableCalcRel(expr#0..3=[{inputs}], proj#0..2=[{exprs}])\n"
+ + " EnumerableSemiJoinRel(condition=[=($3, $4)], joinType=[inner])\n"
+ + " EnumerableCalcRel(expr#0..2=[{inputs}], proj#0..2=[{exprs}], $f3=[$t0])\n"
+ + " EnumerableTableAccessRel(table=[[hr, depts]])\n"
+ + " EnumerableCalcRel(expr#0..4=[{inputs}], expr#5=[150], expr#6=[<($t0, $t5)], deptno=[$t1], $condition=[$t6])\n"
+ + " EnumerableTableAccessRel(table=[[hr, emps]])")
+ .returnsUnordered(
+ "deptno=10; name=Sales; employees=[Employee [empid: 100, deptno: 10, name: Bill], Employee [empid: 150, deptno: 10, name: Sebastian]]");
+ }
+
/** A difficult query: an IN list so large that the planner promotes it
* to a semi-join against a VALUES relation. */
@Ignore
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/test/java/net/hydromatic/optiq/test/OptiqSuite.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/net/hydromatic/optiq/test/OptiqSuite.java b/core/src/test/java/net/hydromatic/optiq/test/OptiqSuite.java
index 369cb93..e083b55 100644
--- a/core/src/test/java/net/hydromatic/optiq/test/OptiqSuite.java
+++ b/core/src/test/java/net/hydromatic/optiq/test/OptiqSuite.java
@@ -18,6 +18,7 @@ package net.hydromatic.optiq.test;
import net.hydromatic.optiq.impl.clone.ArrayTableTest;
import net.hydromatic.optiq.runtime.BinarySearchTest;
+import net.hydromatic.optiq.runtime.EnumerablesTest;
import net.hydromatic.optiq.tools.FrameworksTest;
import net.hydromatic.optiq.tools.PlannerTest;
import net.hydromatic.optiq.tools.SqlRunTest;
@@ -72,6 +73,7 @@ import org.junit.runners.Suite;
RelWriterTest.class,
RexProgramTest.class,
BinarySearchTest.class,
+ EnumerablesTest.class,
// medium tests (above 0.1s)
SqlParserTest.class,
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/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 21503f2..5d8411f 100644
--- a/core/src/test/java/net/hydromatic/optiq/tools/PlannerTest.java
+++ b/core/src/test/java/net/hydromatic/optiq/tools/PlannerTest.java
@@ -450,7 +450,7 @@ public class PlannerTest {
.replace(EnumerableConvention.INSTANCE);
RelNode transform = planner.transform(0, traitSet, convert);
assertThat(toString(transform), containsString(
- "EnumerableJoinRel(condition=[=($3, $0)], joinType=[inner])"));
+ "EnumerableJoinRel(condition=[=($0, $3)], joinType=[inner])"));
}
/** Plans a 3-table join query on the FoodMart schema. The ideal plan is not
@@ -464,10 +464,10 @@ public class PlannerTest {
+ "and p.\"brand_name\" = 'Washington'",
"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], product_class_id=[$37], product_id0=[$38], brand_name=[$39], product_name=[$40], SKU=[$41], SRP=[$42], gross_weight=[$43], net_weight=[$44], recyclable_package=[$45], low_fat=[$46], units_per_case=[$47], cases_per_pallet=[$48], shelf_width=[$49], shelf_height=[$50], shelf_depth=[$51])\n"
+ " EnumerableProjectRel($f0=[$44], $f1=[$45], $f2=[$46], $f3=[$47], $f4=[$48], $f5=[$49], $f6=[$50], $f7=[$51], $f8=[$15], $f9=[$16], $f10=[$17], $f11=[$18], $f12=[$19], $f13=[$20], $f14=[$21], $f15=[$22], $f16=[$23], $f17=[$24], $f18=[$25], $f19=[$26], $f20=[$27], $f21=[$28], $f22=[$29], $f23=[$30], $f24=[$31], $f25=[$32], $f26=[$33], $f27=[$34], $f28=[$35], $f29=[$36], $f30=[$37], $f31=[$38], $f32=[$39], $f33=[$40], $f34=[$41], $f35=[$42], $f36=[$43], $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])\n"
- + " EnumerableJoinRel(condition=[=($44, $1)], joinType=[inner])\n"
+ + " EnumerableJoinRel(condition=[=($1, $44)], joinType=[inner])\n"
+ " EnumerableFilterRel(condition=[=($2, 'Washington')])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, product]])\n"
- + " EnumerableJoinRel(condition=[=($31, $0)], joinType=[inner])\n"
+ + " EnumerableJoinRel(condition=[=($0, $31)], joinType=[inner])\n"
+ " EnumerableFilterRel(condition=[=($9, 'San Francisco')])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, customer]])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, sales_fact_1997]])\n");
@@ -490,11 +490,11 @@ public class PlannerTest {
+ "and p.\"brand_name\" = 'Washington'",
"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], product_class_id=[$37], product_id0=[$38], brand_name=[$39], product_name=[$40], SKU=[$41], SRP=[$42], gross_weight=[$43], net_weight=[$44], recyclable_package=[$45], low_fat=[$46], units_per_case=[$47], cases_per_pallet=[$48], shelf_width=[$49], shelf_height=[$50], shelf_depth=[$51], product_class_id0=[$52], pro
duct_subcategory=[$53], product_category=[$54], product_department=[$55], product_family=[$56])\n"
+ " EnumerableProjectRel($f0=[$49], $f1=[$50], $f2=[$51], $f3=[$52], $f4=[$53], $f5=[$54], $f6=[$55], $f7=[$56], $f8=[$0], $f9=[$1], $f10=[$2], $f11=[$3], $f12=[$4], $f13=[$5], $f14=[$6], $f15=[$7], $f16=[$8], $f17=[$9], $f18=[$10], $f19=[$11], $f20=[$12], $f21=[$13], $f22=[$14], $f23=[$15], $f24=[$16], $f25=[$17], $f26=[$18], $f27=[$19], $f28=[$20], $f29=[$21], $f30=[$22], $f31=[$23], $f32=[$24], $f33=[$25], $f34=[$26], $f35=[$27], $f36=[$28], $f37=[$34], $f38=[$35], $f39=[$36], $f40=[$37], $f41=[$38], $f42=[$39], $f43=[$40], $f44=[$41], $f45=[$42], $f46=[$43], $f47=[$44], $f48=[$45], $f49=[$46], $f50=[$47], $f51=[$48], $f52=[$29], $f53=[$30], $f54=[$31], $f55=[$32], $f56=[$33])\n"
- + " EnumerableJoinRel(condition=[=($51, $0)], joinType=[inner])\n"
+ + " EnumerableJoinRel(condition=[=($0, $51)], joinType=[inner])\n"
+ " EnumerableFilterRel(condition=[=($9, 'San Francisco')])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, customer]])\n"
- + " EnumerableJoinRel(condition=[=($20, $6)], joinType=[inner])\n"
- + " EnumerableJoinRel(condition=[=($5, $0)], joinType=[inner])\n"
+ + " EnumerableJoinRel(condition=[=($6, $20)], joinType=[inner])\n"
+ + " EnumerableJoinRel(condition=[=($0, $5)], joinType=[inner])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, product_class]])\n"
+ " EnumerableFilterRel(condition=[=($2, 'Washington')])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, product]])\n"
@@ -513,13 +513,13 @@ public class PlannerTest {
+ "where c.\"city\" = 'San Francisco'\n",
"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], product_class_id=[$37], product_id0=[$38], brand_name=[$39], product_name=[$40], SKU=[$41], SRP=[$42], gross_weight=[$43], net_weight=[$44], recyclable_package=[$45], low_fat=[$46], units_per_case=[$47], cases_per_pallet=[$48], shelf_width=[$49], shelf_height=[$50], shelf_depth=[$51], product_class_id0=[$52], pro
duct_subcategory=[$53], product_category=[$54], product_department=[$55], product_family=[$56], store_id0=[$57], store_type=[$58], region_id=[$59], store_name=[$60], store_number=[$61], store_street_address=[$62], store_city=[$63], store_state=[$64], store_postal_code=[$65], store_country=[$66], store_manager=[$67], store_phone=[$68], store_fax=[$69], first_opened_date=[$70], last_remodel_date=[$71], store_sqft=[$72], grocery_sqft=[$73], frozen_sqft=[$74], meat_sqft=[$75], coffee_bar=[$76], video_store=[$77], salad_bar=[$78], prepared_food=[$79], florist=[$80])\n"
+ " EnumerableProjectRel($f0=[$73], $f1=[$74], $f2=[$75], $f3=[$76], $f4=[$77], $f5=[$78], $f6=[$79], $f7=[$80], $f8=[$24], $f9=[$25], $f10=[$26], $f11=[$27], $f12=[$28], $f13=[$29], $f14=[$30], $f15=[$31], $f16=[$32], $f17=[$33], $f18=[$34], $f19=[$35], $f20=[$36], $f21=[$37], $f22=[$38], $f23=[$39], $f24=[$40], $f25=[$41], $f26=[$42], $f27=[$43], $f28=[$44], $f29=[$45], $f30=[$46], $f31=[$47], $f32=[$48], $f33=[$49], $f34=[$50], $f35=[$51], $f36=[$52], $f37=[$58], $f38=[$59], $f39=[$60], $f40=[$61], $f41=[$62], $f42=[$63], $f43=[$64], $f44=[$65], $f45=[$66], $f46=[$67], $f47=[$68], $f48=[$69], $f49=[$70], $f50=[$71], $f51=[$72], $f52=[$53], $f53=[$54], $f54=[$55], $f55=[$56], $f56=[$57], $f57=[$0], $f58=[$1], $f59=[$2], $f60=[$3], $f61=[$4], $f62=[$5], $f63=[$6], $f64=[$7], $f65=[$8], $f66=[$9], $f67=[$10], $f68=[$11], $f69=[$12], $f70=[$13], $f71=[$14], $f72=[$15], $f73=[$16], $f74=[$17], $f75=[$18], $f76=[$19], $f77=[$20], $f78=[$21], $f79=[$22], $f80=[$23])\n"
- + " EnumerableJoinRel(condition=[=($77, $0)], joinType=[inner])\n"
+ + " EnumerableJoinRel(condition=[=($0, $77)], joinType=[inner])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, store]])\n"
- + " EnumerableJoinRel(condition=[=($51, $0)], joinType=[inner])\n"
+ + " EnumerableJoinRel(condition=[=($0, $51)], joinType=[inner])\n"
+ " EnumerableFilterRel(condition=[=($9, 'San Francisco')])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, customer]])\n"
- + " EnumerableJoinRel(condition=[=($20, $6)], joinType=[inner])\n"
- + " EnumerableJoinRel(condition=[=($5, $0)], joinType=[inner])\n"
+ + " EnumerableJoinRel(condition=[=($6, $20)], joinType=[inner])\n"
+ + " EnumerableJoinRel(condition=[=($0, $5)], joinType=[inner])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, product_class]])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, product]])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, sales_fact_1997]])\n");
@@ -535,7 +535,7 @@ public class PlannerTest {
+ " 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"
+ + " EnumerableJoinRel(condition=[=($0, $31)], joinType=[inner])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, customer]])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, sales_fact_1997]])");
}
@@ -553,7 +553,7 @@ public class PlannerTest {
+ " EnumerableJoinRel(condition=[=($0, $9)], joinType=[inner])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, department]])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, employee]])\n"
- + " EnumerableJoinRel(condition=[=($31, $0)], joinType=[inner])\n"
+ + " EnumerableJoinRel(condition=[=($0, $31)], joinType=[inner])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, customer]])\n"
+ " EnumerableTableAccessRel(table=[[foodmart2, sales_fact_1997]])\n");
}
http://git-wip-us.apache.org/repos/asf/incubator-optiq/blob/f25e0d83/core/src/test/resources/sql/misc.oq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/misc.oq b/core/src/test/resources/sql/misc.oq
index 2bbb121..554343b 100644
--- a/core/src/test/resources/sql/misc.oq
+++ b/core/src/test/resources/sql/misc.oq
@@ -151,6 +151,149 @@ on ( d."deptno" + 2 - 2 = e."deptno" + 1 - 1 and e."deptno" + 10 - 10 = d."dept
!ok
+# [OPTIQ-377] Mixed equi and non-equi join
+select e."empid", d."name", e."name"
+from "hr"."emps" as e
+join "hr"."depts" as d
+on e."deptno" = d."deptno"
+and e."name" <> d."name";
++-------+-------+-----------+
+| empid | name | name |
++-------+-------+-----------+
+| 100 | Sales | Bill |
+| 110 | Sales | Theodore |
+| 150 | Sales | Sebastian |
++-------+-------+-----------+
+(3 rows)
+
+!ok
+EnumerableCalcRel(expr#0..4=[{inputs}], expr#5=[CAST($t2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], expr#6=[CAST($t4):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], expr#7=[<>($t5, $t6)], empid=[$t0], name=[$t4], name0=[$t2], $condition=[$t7])
+ EnumerableJoinRel(condition=[=($1, $3)], joinType=[inner])
+ EnumerableCalcRel(expr#0..4=[{inputs}], proj#0..2=[{exprs}])
+ EnumerableTableAccessRel(table=[[hr, emps]])
+ EnumerableCalcRel(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
+ EnumerableTableAccessRel(table=[[hr, depts]])
+!plan
+
+# Same query, expressed using WHERE.
+select e."empid", d."name", e."name"
+from "hr"."emps" as e,
+ "hr"."depts" as d
+where e."deptno" = d."deptno"
+and e."name" <> d."name";
++-------+-------+-----------+
+| empid | name | name |
++-------+-------+-----------+
+| 100 | Sales | Bill |
+| 110 | Sales | Theodore |
+| 150 | Sales | Sebastian |
++-------+-------+-----------+
+(3 rows)
+
+!ok
+EnumerableCalcRel(expr#0..4=[{inputs}], expr#5=[CAST($t2):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], expr#6=[CAST($t4):VARCHAR(1) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], expr#7=[<>($t5, $t6)], empid=[$t0], name=[$t4], name0=[$t2], $condition=[$t7])
+ EnumerableJoinRel(condition=[=($1, $3)], joinType=[inner])
+ EnumerableCalcRel(expr#0..4=[{inputs}], proj#0..2=[{exprs}])
+ EnumerableTableAccessRel(table=[[hr, emps]])
+ EnumerableCalcRel(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
+ EnumerableTableAccessRel(table=[[hr, depts]])
+!plan
+
+# EXISTS
+select * from "hr"."emps"
+where exists (
+ select 1 from "hr"."depts" where "depts"."deptno" = "emps"."deptno");
++-------+--------+-----------+---------+------------+
+| empid | deptno | name | salary | commission |
++-------+--------+-----------+---------+------------+
+| 100 | 10 | Bill | 10000.0 | 1000 |
+| 110 | 10 | Theodore | 11500.0 | 250 |
+| 150 | 10 | Sebastian | 7000.0 | |
++-------+--------+-----------+---------+------------+
+(3 rows)
+
+!ok
+EnumerableSemiJoinRel(condition=[=($1, $5)], joinType=[inner])
+ EnumerableTableAccessRel(table=[[hr, emps]])
+ EnumerableCalcRel(expr#0..3=[{inputs}], expr#4=[true], $f01=[$t0], $f0=[$t4])
+ EnumerableJoinRel(condition=[=($0, $1)], joinType=[inner])
+ EnumerableAggregateRel(group=[{0}])
+ EnumerableCalcRel(expr#0..4=[{inputs}], $f0=[$t1])
+ EnumerableTableAccessRel(table=[[hr, emps]])
+ EnumerableTableAccessRel(table=[[hr, depts]])
+!plan
+
+# NOT EXISTS
+# Right results, but it would be better if the plan used EnumerableSemiJoinRel; see [OPTIQ-374]
+select * from "hr"."emps"
+where not exists (
+ select 1 from "hr"."depts" where "depts"."deptno" = "emps"."deptno");
++-------+--------+------+--------+------------+
+| empid | deptno | name | salary | commission |
++-------+--------+------+--------+------------+
+| 200 | 20 | Eric | 8000.0 | 500 |
++-------+--------+------+--------+------------+
+(1 row)
+
+!ok
+EnumerableCalcRel(expr#0..6=[{inputs}], expr#7=[IS NOT NULL($t6)], expr#8=[NOT($t7)], proj#0..4=[{exprs}], $condition=[$t8])
+ EnumerableJoinRel(condition=[=($1, $5)], joinType=[left])
+ EnumerableTableAccessRel(table=[[hr, emps]])
+ EnumerableAggregateRel(group=[{0}], agg#0=[MIN($1)])
+ EnumerableCalcRel(expr#0..3=[{inputs}], expr#4=[true], $f01=[$t0], $f0=[$t4])
+ EnumerableJoinRel(condition=[=($0, $1)], joinType=[inner])
+ EnumerableAggregateRel(group=[{0}])
+ EnumerableCalcRel(expr#0..4=[{inputs}], $f0=[$t1])
+ EnumerableTableAccessRel(table=[[hr, emps]])
+ EnumerableTableAccessRel(table=[[hr, depts]])
+!plan
+
+# NOT EXISTS .. OR NOT EXISTS
+# Right results, but it would be better if the plan used EnumerableSemiJoinRel; see [OPTIQ-374]
+select * from "hr"."emps"
+where not exists (
+ select 1 from "hr"."depts" where "depts"."deptno" = "emps"."deptno")
+or not exists (
+ select 1 from "hr"."depts" where "depts"."deptno" + 90 = "emps"."empid");
+
++-------+--------+-----------+---------+------------+
+| empid | deptno | name | salary | commission |
++-------+--------+-----------+---------+------------+
+| 110 | 10 | Theodore | 11500.0 | 250 |
+| 150 | 10 | Sebastian | 7000.0 | |
+| 200 | 20 | Eric | 8000.0 | 500 |
++-------+--------+-----------+---------+------------+
+(3 rows)
+
+!ok
+EnumerableCalcRel(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t5)], expr#9=[NOT($t8)], expr#10=[IS NOT NULL($t7)], expr#11=[NOT($t10)], expr#12=[OR($t9, $t11)], proj#0..4=[{exprs}], $condition=[$t12])
+ EnumerableJoinRel(condition=[=($0, $6)], joinType=[left])
+ EnumerableCalcRel(expr#0..6=[{inputs}], proj#0..4=[{exprs}], $f0=[$t6])
+ EnumerableJoinRel(condition=[=($1, $5)], joinType=[left])
+ EnumerableTableAccessRel(table=[[hr, emps]])
+ EnumerableAggregateRel(group=[{0}], agg#0=[MIN($1)])
+ EnumerableCalcRel(expr#0..3=[{inputs}], expr#4=[true], $f01=[$t0], $f0=[$t4])
+ EnumerableJoinRel(condition=[=($0, $1)], joinType=[inner])
+ EnumerableAggregateRel(group=[{0}])
+ EnumerableCalcRel(expr#0..4=[{inputs}], $f0=[$t1])
+ EnumerableTableAccessRel(table=[[hr, emps]])
+ EnumerableTableAccessRel(table=[[hr, depts]])
+ EnumerableAggregateRel(group=[{0}], agg#0=[MIN($1)])
+ EnumerableCalcRel(expr#0..3=[{inputs}], expr#4=[true], expr#5=[90], expr#6=[+($t1, $t5)], expr#7=[CAST($t0):INTEGER NOT NULL], expr#8=[=($t6, $t7)], $f01=[$t0], $f0=[$t4], $condition=[$t8])
+ EnumerableJoinRel(condition=[true], joinType=[inner])
+ EnumerableAggregateRel(group=[{0}])
+ EnumerableCalcRel(expr#0..4=[{inputs}], $f0=[$t0])
+ EnumerableSemiJoinRel(condition=[=($1, $5)], joinType=[inner])
+ EnumerableTableAccessRel(table=[[hr, emps]])
+ EnumerableCalcRel(expr#0..3=[{inputs}], expr#4=[true], $f01=[$t0], $f0=[$t4])
+ EnumerableJoinRel(condition=[=($0, $1)], joinType=[inner])
+ EnumerableAggregateRel(group=[{0}])
+ EnumerableCalcRel(expr#0..4=[{inputs}], $f0=[$t1])
+ EnumerableTableAccessRel(table=[[hr, emps]])
+ EnumerableTableAccessRel(table=[[hr, depts]])
+ EnumerableTableAccessRel(table=[[hr, depts]])
+!plan
+
# [OPTIQ-345] AssertionError in RexToLixTranslator comparing to date literal
!use catchall
select count(*) as c from "everyTypes" where "sqlDate" = DATE '1970-01-01';
@@ -242,8 +385,8 @@ from "sales_fact_1997" as s
where c."city" = 'San Francisco'
and pc."product_department" = 'Snacks';
EnumerableCalcRel(expr#0..56=[{inputs}], $f0=[$t20], $f1=[$t21], $f2=[$t22], $f3=[$t23], $f4=[$t24], $f5=[$t25], $f6=[$t26], $f7=[$t27], $f8=[$t28], $f9=[$t29], $f10=[$t30], $f11=[$t31], $f12=[$t32], $f13=[$t33], $f14=[$t34], $f15=[$t35], $f16=[$t36], $f17=[$t37], $f18=[$t38], $f19=[$t39], $f20=[$t40], $f21=[$t41], $f22=[$t42], $f23=[$t43], $f24=[$t44], $f25=[$t45], $f26=[$t46], $f27=[$t47], $f28=[$t48], $f29=[$t49], $f30=[$t50], $f31=[$t51], $f32=[$t52], $f33=[$t53], $f34=[$t54], $f35=[$t55], $f36=[$t56], $f37=[$t5], $f38=[$t6], $f39=[$t7], $f40=[$t8], $f41=[$t9], $f42=[$t10], $f43=[$t11], $f44=[$t12], $f45=[$t13], $f46=[$t14], $f47=[$t15], $f48=[$t16], $f49=[$t17], $f50=[$t18], $f51=[$t19], $f52=[$t0], $f53=[$t1], $f54=[$t2], $f55=[$t3], $f56=[$t4])
- EnumerableJoinRel(condition=[=($20, $6)], joinType=[inner])
- EnumerableJoinRel(condition=[=($5, $0)], joinType=[inner])
+ EnumerableJoinRel(condition=[=($6, $20)], joinType=[inner])
+ EnumerableJoinRel(condition=[=($0, $5)], joinType=[inner])
EnumerableCalcRel(expr#0..4=[{inputs}], expr#5=['Snacks'], expr#6=[=($t3, $t5)], proj#0..4=[{exprs}], $condition=[$t6])
EnumerableTableAccessRel(table=[[foodmart2, product_class]])
EnumerableTableAccessRel(table=[[foodmart2, product]])