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 f232e2d8ec19032df7cfa68edfdfb0587f72bd75
Author: Shahrzad Shirazi <shahrzad.shir...@couchbase.com>
AuthorDate: Wed Jul 2 13:44:40 2025 -0700
[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.solai...@gmail.com>
Reviewed-by: Ali Alsuliman <ali.al.solai...@gmail.com>
---
.../index-leftouterjoin-example.1.ddl.sqlpp | 36 ++++++++++++++++++++++
.../index-leftouterjoin-example.2.update.sqlpp | 25 +++++++++++++++
.../index-leftouterjoin-example.3.query.sqlpp | 23 ++++++++++++++
.../index-leftouterjoin-example.4.query.sqlpp | 23 ++++++++++++++
.../index-leftouterjoin-example.5.query.sqlpp | 23 ++++++++++++++
.../index-leftouterjoin-example.1.adm | 2 ++
.../index-leftouterjoin-example.2.adm | 3 ++
.../index-leftouterjoin-example.3.adm | 2 ++
.../src/test/resources/runtimets/sqlpp_queries.xml | 5 +++
...MBTreeBatchPointSearchOperatorNodePushable.java | 5 +++
10 files changed, 147 insertions(+)
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 0000000000..8fe4194116
--- /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 0000000000..90a6b2ab3c
--- /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 0000000000..b746539dac
--- /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 0000000000..8df0469055
--- /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 0000000000..c574b0f4b7
--- /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 0000000000..cb3a9d2209
--- /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 0000000000..8e7d14d277
--- /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 0000000000..ece0d0d359
--- /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 50431e1309..42aac70424 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/sqlpp_queries.xml
@@ -14378,6 +14378,11 @@
</test-case>
</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>
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 f6f97b789c..d2b2c1b073 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 @@ public class LSMBTreeBatchPointSearchOperatorNodePushable
extends BTreeSearchOpe
break;
}
}
+
+ if (matchingTupleCount == 0 && retainInput && retainMissing) {
+ int end = accessor.getTupleCount();
+ appendMissingTuple(0, end);
+ }
stats.getInputTupleCounter().update(matchingTupleCount);
}
