[ASTERIXDB-3572][COMP] Use hash-join when condition contains equi predicate
- user model changes: no
- storage format changes: no
- interface changes: no
Details:
When the join condition contains at least one equality
predicate, a hash-join can be used instead of nested loop join.
Ext-ref: MB-65612
Change-Id: I5ca76afb607c725beaf68272e917d0566000a6ac
Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/19473
Integration-Tests: Jenkins <jenkins@fulliautomatix.ics.uci.edu>
Tested-by: Ali Alsuliman <ali.al.solaiman@gmail.com>
Reviewed-by: Ali Alsuliman <ali.al.solaiman@gmail.com>
Reviewed-by: Peeyush Gupta <peeyush.gupta@couchbase.com>
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/joins/ASTERIXDB-3572-1-equi-non-equi-condition.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/joins/ASTERIXDB-3572-1-equi-non-equi-condition.sqlpp
new file mode 100644
index 0000000..cf7278f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/joins/ASTERIXDB-3572-1-equi-non-equi-condition.sqlpp
@@ -0,0 +1,30 @@
+/*
+ * 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.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+
+USE test;
+
+CREATE COLLECTION ds1 PRIMARY KEY(id: uuid) autogenerated;
+CREATE COLLECTION ds2 PRIMARY KEY(id: uuid) autogenerated;
+
+SELECT ds1.x AS ds1_x, ds1.st AS ds1_st, ds2.x AS ds2_x, ds2.st AS ds2_st, ds2.et AS ds2_et
+FROM ds1 LEFT JOIN ds2 ON ds1.st > ds2.st AND ds1.st < ds2.et AND ds1.x = ds2.x
+ORDER BY ds2.et;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/queries/joins/ASTERIXDB-3572-2-equi-non-equi-condition.sqlpp b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/joins/ASTERIXDB-3572-2-equi-non-equi-condition.sqlpp
new file mode 100644
index 0000000..30ead99
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/queries/joins/ASTERIXDB-3572-2-equi-non-equi-condition.sqlpp
@@ -0,0 +1,29 @@
+/*
+ * 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.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+
+USE test;
+
+CREATE COLLECTION ds1 PRIMARY KEY(id: uuid) autogenerated;
+CREATE COLLECTION ds2 PRIMARY KEY(id: uuid) autogenerated;
+
+SELECT ds1.x AS ds1_x, ds1.st AS ds1_st, ds2.x AS ds2_x, ds2.st AS ds2_st, ds2.et AS ds2_et
+FROM ds1 LEFT JOIN ds2 ON ds1.st = ds1.ss + ds2.tc AND ds1.x = ds2.x;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ASTERIXDB-2402.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ASTERIXDB-2402.plan
index 1dff738..0171a23 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ASTERIXDB-2402.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ASTERIXDB-2402.plan
@@ -147,9 +147,9 @@
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
left outer join (and(spatial-intersect($$243, $$244), eq($$235, $$242)))
- -- NESTED_LOOP |PARTITIONED|
+ -- HYBRID_HASH_JOIN [$$242][$$235] |PARTITIONED|
exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$242] |PARTITIONED|
left outer join (true)
-- NESTED_LOOP |PARTITIONED|
exchange
@@ -195,7 +195,7 @@
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
exchange
- -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$235] |PARTITIONED|
assign [$$265, $$244] <- [true, $$u.getField(0)]
-- ASSIGN |PARTITIONED|
exchange
@@ -233,9 +233,9 @@
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
left outer join (and(spatial-intersect($$243, $$244), eq($$235, $$242)))
- -- NESTED_LOOP |PARTITIONED|
+ -- HYBRID_HASH_JOIN [$$242][$$235] |PARTITIONED|
exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$242] |PARTITIONED|
left outer join (true)
-- NESTED_LOOP |PARTITIONED|
exchange
@@ -281,7 +281,7 @@
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
exchange
- -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$235] |PARTITIONED|
assign [$$265, $$244] <- [true, $$u.getField(0)]
-- ASSIGN |PARTITIONED|
exchange
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q21.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q21.plan
index bea8419..8edc415 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q21.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/ch2/ch2_q21.plan
@@ -61,9 +61,9 @@
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
left outer join (and(eq($$383, $$378), gt($$384, $$353), eq($$381, $$377), eq($$382, $$352)))
- -- NESTED_LOOP |PARTITIONED|
+ -- HYBRID_HASH_JOIN [$$378, $$377, $$352][$$383, $$381, $$382] |PARTITIONED|
exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$378, $$377, $$352] |PARTITIONED|
project ([$$352, $$378, $$377, $$354, $$355, $$351, $$350, $$379, $$353])
-- STREAM_PROJECT |PARTITIONED|
exchange
@@ -161,7 +161,7 @@
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
exchange
- -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$383, $$381, $$382] |PARTITIONED|
assign [$$326] <- [{"o_id": $$381, "o_w_id": $$382, "o_d_id": $$383, "ol_delivery_d": $$384}.getField("o_id")]
-- ASSIGN |PARTITIONED|
assign [$$384] <- [$$ol2.getField("ol_delivery_d")] project: [$$383, $$382, $$381, $$384]
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/joins/ASTERIXDB-3572-1-equi-non-equi-condition.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/joins/ASTERIXDB-3572-1-equi-non-equi-condition.plan
new file mode 100644
index 0000000..09d2164
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/joins/ASTERIXDB-3572-1-equi-non-equi-condition.plan
@@ -0,0 +1,46 @@
+distribute result [$$43]
+-- DISTRIBUTE_RESULT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ assign [$$43] <- [{"ds1_x": $$49, "ds1_st": $$45, "ds2_x": $$ds2.getField("x"), "ds2_st": $$ds2.getField("st"), "ds2_et": $$51}] project: [$$43]
+ -- ASSIGN |PARTITIONED|
+ exchange
+ -- SORT_MERGE_EXCHANGE [$$51(ASC) ] |PARTITIONED|
+ order (ASC, $$51)
+ -- STABLE_SORT [$$51(ASC)] |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ project ([$$49, $$45, $$51, $$ds2])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ left outer join (and(gt($$45, $$48), eq($$49, $$50), lt($$45, $$51)))
+ -- HYBRID_HASH_JOIN [$$49][$$50] |PARTITIONED|
+ exchange
+ -- HASH_PARTITION_EXCHANGE [$$49] |PARTITIONED|
+ assign [$$49, $$45] <- [$$ds1.getField("x"), $$ds1.getField("st")] project: [$$49, $$45]
+ -- ASSIGN |PARTITIONED|
+ project ([$$ds1])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$46, $$ds1] <- test.ds1
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange
+ -- HASH_PARTITION_EXCHANGE [$$50] |PARTITIONED|
+ assign [$$51, $$50, $$48] <- [$$ds2.getField("et"), $$ds2.getField("x"), $$ds2.getField("st")]
+ -- ASSIGN |PARTITIONED|
+ project ([$$ds2])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$47, $$ds2] <- test.ds2
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results/joins/ASTERIXDB-3572-2-equi-non-equi-condition.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results/joins/ASTERIXDB-3572-2-equi-non-equi-condition.plan
new file mode 100644
index 0000000..dff8650
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results/joins/ASTERIXDB-3572-2-equi-non-equi-condition.plan
@@ -0,0 +1,40 @@
+distribute result [$$42]
+-- DISTRIBUTE_RESULT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ assign [$$42] <- [{"ds1_x": $$47, "ds1_st": $$45, "ds2_x": $$48, "ds2_st": $$55, "ds2_et": $$56}] project: [$$42]
+ -- ASSIGN |PARTITIONED|
+ project ([$$47, $$45, $$48, $$55, $$56])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ left outer join (and(eq($$45, numeric-add($$50, $$51)), eq($$47, $$48)))
+ -- HYBRID_HASH_JOIN [$$47][$$48] |PARTITIONED|
+ exchange
+ -- HASH_PARTITION_EXCHANGE [$$47] |PARTITIONED|
+ assign [$$50, $$47, $$45] <- [$$ds1.getField("ss"), $$ds1.getField("x"), $$ds1.getField("st")] project: [$$47, $$45, $$50]
+ -- ASSIGN |PARTITIONED|
+ project ([$$ds1])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$43, $$ds1] <- test.ds1
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
+ exchange
+ -- HASH_PARTITION_EXCHANGE [$$48] |PARTITIONED|
+ assign [$$56, $$55, $$48, $$51] <- [$$ds2.getField("et"), $$ds2.getField("st"), $$ds2.getField("x"), $$ds2.getField("tc")] project: [$$48, $$55, $$56, $$51]
+ -- ASSIGN |PARTITIONED|
+ project ([$$ds2])
+ -- STREAM_PROJECT |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ data-scan []<-[$$44, $$ds2] <- test.ds2
+ -- DATASOURCE_SCAN |PARTITIONED|
+ exchange
+ -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ empty-tuple-source
+ -- EMPTY_TUPLE_SOURCE |PARTITIONED|
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ASTERIXDB-2402.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ASTERIXDB-2402.plan
index c7d8549..6c51839 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ASTERIXDB-2402.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ASTERIXDB-2402.plan
@@ -147,9 +147,9 @@
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
left outer join (and(spatial-intersect($$243, $$244), eq($$235, $$242)))
- -- NESTED_LOOP |PARTITIONED|
+ -- HYBRID_HASH_JOIN [$$242][$$235] |PARTITIONED|
exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$242] |PARTITIONED|
left outer join (true)
-- NESTED_LOOP |PARTITIONED|
exchange
@@ -181,7 +181,7 @@
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
exchange
- -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$235] |PARTITIONED|
assign [$$265, $$244] <- [true, $$u.getField(0)]
-- ASSIGN |PARTITIONED|
exchange
@@ -219,9 +219,9 @@
exchange
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
left outer join (and(spatial-intersect($$243, $$244), eq($$235, $$242)))
- -- NESTED_LOOP |PARTITIONED|
+ -- HYBRID_HASH_JOIN [$$242][$$235] |PARTITIONED|
exchange
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$242] |PARTITIONED|
left outer join (true)
-- NESTED_LOOP |PARTITIONED|
exchange
@@ -253,7 +253,7 @@
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
exchange
- -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$235] |PARTITIONED|
assign [$$265, $$244] <- [true, $$u.getField(0)]
-- ASSIGN |PARTITIONED|
exchange
diff --git a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q21.plan b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q21.plan
index 09cb112..2a00f54 100644
--- a/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q21.plan
+++ b/asterixdb/asterix-app/src/test/resources/optimizerts/results_cbo/ch2/ch2_q21.plan
@@ -85,9 +85,9 @@
exchange [cardinality: 1000000.0, op-cost: 500000.0, total-cost: 1500000.0]
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
left outer join (and(eq($$383, $$378), gt($$384, $$353), eq($$381, $$377), eq($$382, $$352))) [cardinality: 500000.0, op-cost: 1.0E12, total-cost: 1.000006E12]
- -- NESTED_LOOP |PARTITIONED|
+ -- HYBRID_HASH_JOIN [$$378, $$377, $$352][$$383, $$381, $$382] |PARTITIONED|
exchange [cardinality: 1000000.0, op-cost: 500000.0, total-cost: 1500000.0]
- -- ONE_TO_ONE_EXCHANGE |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$378, $$377, $$352] |PARTITIONED|
select (gt($$353, string(numeric-add(date($$349), duration: {P150D })))) project: [$$352, $$378, $$377, $$362, $$353] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0]
-- STREAM_SELECT |PARTITIONED|
assign [$$362, $$353] <- [$$ol1.getField("ol_i_id"), $$ol1.getField("ol_delivery_d")] project: [$$378, $$377, $$352, $$349, $$362, $$353] [cardinality: 1000000.0, op-cost: 0.0, total-cost: 1000000.0]
@@ -117,7 +117,7 @@
empty-tuple-source
-- EMPTY_TUPLE_SOURCE |PARTITIONED|
exchange
- -- BROADCAST_EXCHANGE |PARTITIONED|
+ -- HASH_PARTITION_EXCHANGE [$$383, $$381, $$382] |PARTITIONED|
assign [$$326] <- [{"o_id": $$381, "o_w_id": $$382, "o_d_id": $$383, "ol_delivery_d": $$384}.getField("o_id")]
-- ASSIGN |PARTITIONED|
assign [$$384] <- [$$ol2.getField("ol_delivery_d")] project: [$$383, $$382, $$381, $$384]
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.01.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.01.ddl.sqlpp
new file mode 100644
index 0000000..50adf76
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.01.ddl.sqlpp
@@ -0,0 +1,26 @@
+/*
+ * 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.
+ */
+
+DROP DATAVERSE test IF EXISTS;
+CREATE DATAVERSE test;
+
+USE test;
+
+CREATE COLLECTION ds1 PRIMARY KEY(id: uuid) autogenerated;
+CREATE COLLECTION ds2 PRIMARY KEY(id: uuid) autogenerated;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.02.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.02.update.sqlpp
new file mode 100644
index 0000000..1b28b4f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.02.update.sqlpp
@@ -0,0 +1,32 @@
+/*
+ * 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.
+ */
+
+USE test;
+
+UPSERT INTO ds1 ([
+{"x": 1, "st": 7, "ss": 2},
+{"x": 2, "st": 5, "ss": 4}
+]);
+
+UPSERT INTO ds2 ([
+{"x": 1, "st": 1, "et": 10, "tc": 2},
+{"x": 1, "st": 8, "et": 10, "tc": 5},
+{"x": 1, "st": 1, "et": 15},
+{"x": 2, "st": 8, "et": 10, "tc": 100}
+]);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.03.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.03.query.sqlpp
new file mode 100644
index 0000000..3aac3bf
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.03.query.sqlpp
@@ -0,0 +1,32 @@
+/*
+ * 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.
+ */
+/*
+ * Similar to hash-join-with-redundant-variable.04.query.sqlpp
+ * But with Index NL. The plan of this test should has three
+ * hash-partition-exchange (as opposed to test 13 & 14). Because the parallelism
+ * is set to 3, then the last join requires both sides to be hash partitioned.
+ * Customer will need to duplicate its variable to join both with Nation and Supplier.
+ * This is the effect of using Index NL with parallelism != # of partitions
+ */
+
+USE test;
+
+SELECT ds1.x AS ds1_x, ds1.st AS ds1_st, ds2.x AS ds2_x, ds2.st AS ds2_st, ds2.et AS ds2_et
+FROM ds1 LEFT JOIN ds2 ON ds1.st > ds2.st AND ds1.st < ds2.et AND ds1.x = ds2.x
+ORDER BY ds2.et;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.04.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.04.query.sqlpp
new file mode 100644
index 0000000..134c9d2
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.04.query.sqlpp
@@ -0,0 +1,32 @@
+/*
+ * 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.
+ */
+/*
+ * Similar to hash-join-with-redundant-variable.04.query.sqlpp
+ * But with Index NL. The plan of this test should has three
+ * hash-partition-exchange (as opposed to test 13 & 14). Because the parallelism
+ * is set to 3, then the last join requires both sides to be hash partitioned.
+ * Customer will need to duplicate its variable to join both with Nation and Supplier.
+ * This is the effect of using Index NL with parallelism != # of partitions
+ */
+
+USE test;
+
+SELECT ds1.x AS ds1_x, ds1.st AS ds1_st, ds1.ss AS ds1_ss, ds2.x AS ds2_x, ds2.tc AS ds2_tc
+FROM ds1 LEFT JOIN ds2 ON ds1.st = ds1.ss + ds2.tc AND ds1.x = ds2.x
+ORDER BY ds1_x;
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.03.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.03.adm
new file mode 100644
index 0000000..a3f88e6
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.03.adm
@@ -0,0 +1,3 @@
+{ "ds1_x": 2, "ds1_st": 5 }
+{ "ds1_x": 1, "ds1_st": 7, "ds2_x": 1, "ds2_st": 1, "ds2_et": 10 }
+{ "ds1_x": 1, "ds1_st": 7, "ds2_x": 1, "ds2_st": 1, "ds2_et": 15 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.04.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.04.adm
new file mode 100644
index 0000000..fa53723
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/ASTERIXDB-3572-equi-non-equi-condition/ASTERIXDB-3572-equi-non-equi-condition.04.adm
@@ -0,0 +1,2 @@
+{ "ds1_x": 1, "ds1_st": 7, "ds1_ss": 2, "ds2_x": 1, "ds2_tc": 5 }
+{ "ds1_x": 2, "ds1_st": 5, "ds1_ss": 4 }
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
index 9480064..801e931 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
@@ -6878,6 +6878,11 @@
</compilation-unit>
</test-case>
<test-case FilePath="join">
+ <compilation-unit name="ASTERIXDB-3572-equi-non-equi-condition">
+ <output-dir compare="Text">ASTERIXDB-3572-equi-non-equi-condition</output-dir>
+ </compilation-unit>
+ </test-case>
+ <test-case FilePath="join">
<compilation-unit name="hash_join_array">
<output-dir compare="Text">hash_join_array</output-dir>
</compilation-unit>
diff --git a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/util/JoinUtils.java b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/util/JoinUtils.java
index 49ff483..085580f 100644
--- a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/util/JoinUtils.java
+++ b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/util/JoinUtils.java
@@ -171,37 +171,51 @@
}
private static boolean isHashJoinCondition(ILogicalExpression e, Collection<LogicalVariable> inLeftAll,
- Collection<LogicalVariable> inRightAll, Collection<LogicalVariable> outLeftFields,
- Collection<LogicalVariable> outRightFields) {
+ Collection<LogicalVariable> inRightAll, List<LogicalVariable> outLeftFields,
+ List<LogicalVariable> outRightFields) {
+ return isValidHashJoinExpr(e, inLeftAll, inRightAll, outLeftFields, outRightFields) == Boolean.TRUE;
+ }
+
+ private static Boolean isValidHashJoinExpr(ILogicalExpression e, Collection<LogicalVariable> inLeftAll,
+ Collection<LogicalVariable> inRightAll, List<LogicalVariable> outLeftFields,
+ List<LogicalVariable> outRightFields) {
switch (e.getExpressionTag()) {
case FUNCTION_CALL: {
AbstractFunctionCallExpression fexp = (AbstractFunctionCallExpression) e;
FunctionIdentifier fi = fexp.getFunctionIdentifier();
if (fi.equals(AlgebricksBuiltinFunctions.AND)) {
+ Boolean foundValidEQ = null;
for (Mutable<ILogicalExpression> a : fexp.getArguments()) {
- if (!isHashJoinCondition(a.getValue(), inLeftAll, inRightAll, outLeftFields, outRightFields)) {
- return false;
+ Boolean validHashJoinExpr =
+ isValidHashJoinExpr(a.getValue(), inLeftAll, inRightAll, outLeftFields, outRightFields);
+ if (validHashJoinExpr == Boolean.FALSE) {
+ return Boolean.FALSE;
+ } else if (validHashJoinExpr == Boolean.TRUE) {
+ foundValidEQ = Boolean.TRUE;
}
}
- return true;
+ return foundValidEQ;
} else {
ComparisonKind ck = AlgebricksBuiltinFunctions.getComparisonType(fi);
if (ck != ComparisonKind.EQ) {
- return false;
+ return null;
}
ILogicalExpression opLeft = fexp.getArguments().get(0).getValue();
ILogicalExpression opRight = fexp.getArguments().get(1).getValue();
if (opLeft.getExpressionTag() != LogicalExpressionTag.VARIABLE
|| opRight.getExpressionTag() != LogicalExpressionTag.VARIABLE) {
- return false;
+ return null;
}
LogicalVariable var1 = ((VariableReferenceExpression) opLeft).getVariableReference();
+ boolean leftAddedVar;
if (inLeftAll.contains(var1) && !outLeftFields.contains(var1)) {
outLeftFields.add(var1);
+ leftAddedVar = true;
} else if (inRightAll.contains(var1) && !outRightFields.contains(var1)) {
outRightFields.add(var1);
+ leftAddedVar = false;
} else {
- return false;
+ return null;
}
LogicalVariable var2 = ((VariableReferenceExpression) opRight).getVariableReference();
if (inLeftAll.contains(var2) && !outLeftFields.contains(var2)) {
@@ -209,13 +223,19 @@
} else if (inRightAll.contains(var2) && !outRightFields.contains(var2)) {
outRightFields.add(var2);
} else {
- return false;
+ // ensure there is always pairs of keys between left & right by removing the added key above
+ if (leftAddedVar) {
+ outLeftFields.removeLast();
+ } else {
+ outRightFields.removeLast();
+ }
+ return null;
}
- return true;
+ return Boolean.TRUE;
}
}
default:
- return false;
+ return null;
}
}