[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 <ali.al.solaiman@gmail.com>
Reviewed-by: Ali Alsuliman <ali.al.solaiman@gmail.com>
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 4ae1ea6..3debd6f 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
@@ -14727,6 +14727,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 de4c4a9..66dbcf5 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
@@ -131,6 +131,11 @@
break;
}
}
+
+ if (matchingTupleCount == 0 && retainInput && retainMissing) {
+ int end = accessor.getTupleCount();
+ appendMissingTuple(0, end);
+ }
stats.getInputTupleCounter().update(matchingTupleCount);
}