This is an automated email from the ASF dual-hosted git repository. mhubail pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/asterixdb.git
commit 59ace80151c0c8039645cf191af060163aea13d2 Author: Ali Alsuliman <[email protected]> AuthorDate: Tue Mar 4 21:52:16 2025 -0800 [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 <[email protected]> Tested-by: Ali Alsuliman <[email protected]> Reviewed-by: Ali Alsuliman <[email protected]> Reviewed-by: Peeyush Gupta <[email protected]> --- .../ASTERIXDB-3572-1-equi-non-equi-condition.sqlpp | 30 ++++++++++++++ .../ASTERIXDB-3572-2-equi-non-equi-condition.sqlpp | 29 ++++++++++++++ .../optimizerts/results/ASTERIXDB-2402.plan | 12 +++--- .../resources/optimizerts/results/ch2/ch2_q21.plan | 6 +-- .../ASTERIXDB-3572-1-equi-non-equi-condition.plan | 46 ++++++++++++++++++++++ .../ASTERIXDB-3572-2-equi-non-equi-condition.plan | 40 +++++++++++++++++++ .../optimizerts/results_cbo/ASTERIXDB-2402.plan | 12 +++--- .../optimizerts/results_cbo/ch2/ch2_q21.plan | 6 +-- ...RIXDB-3572-equi-non-equi-condition.01.ddl.sqlpp | 26 ++++++++++++ ...DB-3572-equi-non-equi-condition.02.update.sqlpp | 32 +++++++++++++++ ...XDB-3572-equi-non-equi-condition.03.query.sqlpp | 32 +++++++++++++++ ...XDB-3572-equi-non-equi-condition.04.query.sqlpp | 32 +++++++++++++++ .../ASTERIXDB-3572-equi-non-equi-condition.03.adm | 3 ++ .../ASTERIXDB-3572-equi-non-equi-condition.04.adm | 2 + .../src/test/resources/runtimets/sqlpp_queries.xml | 5 +++ .../algebricks/rewriter/util/JoinUtils.java | 42 ++++++++++++++------ 16 files changed, 326 insertions(+), 29 deletions(-) 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 0000000000..cf7278f7fd --- /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 0000000000..30ead99d35 --- /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 1dff738207..0171a23faa 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 @@ distribute result [$$227] 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 @@ distribute result [$$227] 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 @@ distribute result [$$227] 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 @@ distribute result [$$227] 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 bea8419d80..8edc41580d 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 @@ distribute result [$$339] 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 @@ distribute result [$$339] 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 0000000000..09d216445b --- /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 0000000000..dff8650d21 --- /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 c7d8549eb9..6c51839cfb 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 @@ distribute result [$$227] 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 @@ distribute result [$$227] 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 @@ distribute result [$$227] 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 @@ distribute result [$$227] 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 09cb112a0a..2a00f54ffd 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 @@ distribute result [$$339] [cardinality: 9.223372036854776E16, op-cost: 0.0, tota 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 @@ distribute result [$$339] [cardinality: 9.223372036854776E16, op-cost: 0.0, tota 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 0000000000..50adf76865 --- /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 0000000000..1b28b4fa0b --- /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 0000000000..3aac3bf457 --- /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 0000000000..134c9d2509 --- /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 0000000000..a3f88e6b6d --- /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 0000000000..fa537237a0 --- /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 9480064490..801e9314a1 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml +++ b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml @@ -6877,6 +6877,11 @@ <output-dir compare="Text">ASTERIXDB-3502</output-dir> </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> 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 49ff483de7..085580f08e 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 @@ public class JoinUtils { } 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 @@ public class JoinUtils { } 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; } }
