>From Shahrzad Shirazi <[email protected]>: Shahrzad Shirazi has submitted this change. ( https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/20003 )
Change subject: [ASTERIXDB-3625][COMP] Correcting the result of LEFT OUTER JOIN using index-nested-loop join ...................................................................... [ASTERIXDB-3625][COMP] Correcting the result of LEFT OUTER JOIN using index-nested-loop join - user model changes: no - storage format changes: no - interface changes: no Details: When an index-nested-loop join finds no matching entries, it may discard all tuples from the left table, resulting in incorrect results. This fix ensures that tuples from the left table are preserved even when no matching entries exist for the join. Ext-ref: MB-67347 Change-Id: Ica97998d7fb006230977034152a35e1d6386ef21 Reviewed-on: https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/20003 Tested-by: Ali Alsuliman <[email protected]> Reviewed-by: Ali Alsuliman <[email protected]> --- M hyracks-fullstack/hyracks/hyracks-storage-am-lsm-btree/src/main/java/org/apache/hyracks/storage/am/lsm/btree/dataflow/LSMBTreeBatchPointSearchOperatorNodePushable.java A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.5.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/results/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.2.adm M asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.1.ddl.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.4.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.3.query.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/results/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.3.adm A asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.2.update.sqlpp A asterixdb/asterix-app/src/test/resources/runtimets/results/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.1.adm 10 files changed, 171 insertions(+), 0 deletions(-) Approvals: Ali Alsuliman: Looks good to me, approved; Verified Objections: Anon. E. Moose #1000171: Violations found diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.1.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.1.ddl.sqlpp new file mode 100644 index 0000000..8fe4194 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.1.ddl.sqlpp @@ -0,0 +1,36 @@ +/* + * 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 TestDataverse IF EXISTS; +CREATE DATAVERSE TestDataverse; +USE TestDataverse; + +CREATE TYPE col1Type AS { +id:int +}; + +CREATE TYPE col2Type AS { +id:int, +f:int +}; + +CREATE DATASET col1(col1Type) PRIMARY KEY id; + +CREATE DATASET col2(col2Type) PRIMARY KEY id; + +CREATE INDEX idx_id ON col2(f); \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.2.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.2.update.sqlpp new file mode 100644 index 0000000..90a6b2a --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.2.update.sqlpp @@ -0,0 +1,25 @@ +/* + * 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 TestDataverse; + +UPSERT INTO col1 {"id": 1, "f": 8}; +UPSERT INTO col1 {"id": 2, "f": 100}; +UPSERT INTO col2 {"id": 1, "f": 8}; +UPSERT INTO col2 {"id": 2, "f": 33}; + diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.3.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.3.query.sqlpp new file mode 100644 index 0000000..b746539 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.3.query.sqlpp @@ -0,0 +1,23 @@ +/* + * 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 TestDataverse; + +FROM col1 LEFT JOIN col2 ON col1.f /*+ indexnl */ = col2.f +SELECT col1, col2 +ORDER BY col1.id; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.4.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.4.query.sqlpp new file mode 100644 index 0000000..8df0469 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.4.query.sqlpp @@ -0,0 +1,23 @@ +/* + * 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 TestDataverse; + +FROM col1 LEFT JOIN col2 ON col1.f /*+ indexnl */ > col2.f +SELECT col1, col2 +ORDER BY col1.id,col2.id; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.5.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.5.query.sqlpp new file mode 100644 index 0000000..c574b0f --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.5.query.sqlpp @@ -0,0 +1,23 @@ +/* + * 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 TestDataverse; + +FROM col1 LEFT JOIN col2 ON col1.f /*+ indexnl */< col2.f +SELECT col1, col2 +ORDER BY col1.id,col2.id; \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.1.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.1.adm new file mode 100644 index 0000000..cb3a9d2 --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.1.adm @@ -0,0 +1,2 @@ +{ "col1": { "id": 1, "f": 8 }, "col2": { "id": 1, "f": 8 } } +{ "col1": { "id": 2, "f": 100 } } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.2.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.2.adm new file mode 100644 index 0000000..8e7d14d --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.2.adm @@ -0,0 +1,3 @@ +{ "col1": { "id": 1, "f": 8 } } +{ "col1": { "id": 2, "f": 100 }, "col2": { "id": 1, "f": 8 } } +{ "col1": { "id": 2, "f": 100 }, "col2": { "id": 2, "f": 33 } } \ No newline at end of file diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.3.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.3.adm new file mode 100644 index 0000000..ece0d0d --- /dev/null +++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/index-leftouterjoin/index-leftouterjoin-example/index-leftouterjoin-example.3.adm @@ -0,0 +1,2 @@ +{ "col1": { "id": 1, "f": 8 }, "col2": { "id": 2, "f": 33 } } +{ "col1": { "id": 2, "f": 100 } } \ 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 50431e1..42aac70 100644 --- a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml +++ b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml @@ -14379,6 +14379,11 @@ </test-group> <test-group name="index-leftouterjoin"> <test-case FilePath="index-leftouterjoin"> + <compilation-unit name="index-leftouterjoin-example"> + <output-dir compare="Text">index-leftouterjoin-example</output-dir> + </compilation-unit> + </test-case> + <test-case FilePath="index-leftouterjoin"> <compilation-unit name="probe-pidx-with-join-btree-sidx1"> <output-dir compare="Text">probe-pidx-with-join-btree-sidx1</output-dir> </compilation-unit> diff --git a/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-btree/src/main/java/org/apache/hyracks/storage/am/lsm/btree/dataflow/LSMBTreeBatchPointSearchOperatorNodePushable.java b/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-btree/src/main/java/org/apache/hyracks/storage/am/lsm/btree/dataflow/LSMBTreeBatchPointSearchOperatorNodePushable.java index f6f97b7..d2b2c1b 100644 --- a/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-btree/src/main/java/org/apache/hyracks/storage/am/lsm/btree/dataflow/LSMBTreeBatchPointSearchOperatorNodePushable.java +++ b/hyracks-fullstack/hyracks/hyracks-storage-am-lsm-btree/src/main/java/org/apache/hyracks/storage/am/lsm/btree/dataflow/LSMBTreeBatchPointSearchOperatorNodePushable.java @@ -126,6 +126,11 @@ break; } } + + if (matchingTupleCount == 0 && retainInput && retainMissing) { + int end = accessor.getTupleCount(); + appendMissingTuple(0, end); + } stats.getInputTupleCounter().update(matchingTupleCount); } -- To view, visit https://asterix-gerrit.ics.uci.edu/c/asterixdb/+/20003 To unsubscribe, or for help writing mail filters, visit https://asterix-gerrit.ics.uci.edu/settings Gerrit-Project: asterixdb Gerrit-Branch: trinity Gerrit-Change-Id: Ica97998d7fb006230977034152a35e1d6386ef21 Gerrit-Change-Number: 20003 Gerrit-PatchSet: 23 Gerrit-Owner: Shahrzad Shirazi <[email protected]> Gerrit-Reviewer: Ali Alsuliman <[email protected]> Gerrit-Reviewer: Anon. E. Moose #1000171 Gerrit-Reviewer: Jenkins <[email protected]> Gerrit-Reviewer: Shahrzad Shirazi <[email protected]> Gerrit-MessageType: merged
