diff --git a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java
index 1c3ef03..3cea23f 100644
--- a/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java
+++ b/asterixdb/asterix-algebra/src/main/java/org/apache/asterix/optimizer/base/RuleCollections.java
@@ -112,6 +112,7 @@
 import org.apache.hyracks.algebricks.rewriter.rules.BreakSelectIntoConjunctsRule;
 import org.apache.hyracks.algebricks.rewriter.rules.ComplexUnnestToProductRule;
 import org.apache.hyracks.algebricks.rewriter.rules.ConsolidateAssignsRule;
+import org.apache.hyracks.algebricks.rewriter.rules.ConsolidateLeftOuterJoinSelectsRule;
 import org.apache.hyracks.algebricks.rewriter.rules.ConsolidateSelectsRule;
 import org.apache.hyracks.algebricks.rewriter.rules.CopyLimitDownRule;
 import org.apache.hyracks.algebricks.rewriter.rules.EliminateGroupByEmptyKeyRule;
@@ -339,6 +340,8 @@
         planCleanupRules.add(new RemoveRedundantVariablesInUnionRule()); // relies on RemoveUnusedAssignAndAggregateRule
         planCleanupRules.add(new PushProjectDownRule());
         planCleanupRules.add(new PushSelectDownRule());
+        // Must run after PushSelectDownRule
+        planCleanupRules.add(new ConsolidateLeftOuterJoinSelectsRule());
         planCleanupRules.add(new SetClosedRecordConstructorsRule());
         planCleanupRules.add(new IntroduceDynamicTypeCastRule());
         planCleanupRules.add(new IntroduceDynamicTypeCastForExternalFunctionRule());
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.00.ddl.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.00.ddl.sqlpp
new file mode 100644
index 0000000..a4d3984
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.00.ddl.sqlpp
@@ -0,0 +1,57 @@
+/*
+ * 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 tpch IF EXISTS;
+CREATE DATAVERSE tpch;
+
+USE tpch;
+
+CREATE TYPE CustomerType AS CLOSED {
+  c_custkey : bigint,
+  c_name : string,
+  c_address : string,
+  c_nationkey : bigint,
+  c_phone : string,
+  c_acctbal : double,
+  c_mktsegment : string,
+  c_comment : string
+};
+
+CREATE TYPE OrderType AS CLOSED {
+  o_orderkey : bigint,
+  o_custkey : bigint,
+  o_orderstatus : string,
+  o_totalprice : double,
+  o_orderdate : string,
+  o_orderpriority : string,
+  o_clerk : string,
+  o_shippriority : bigint,
+  o_comment : string
+};
+
+CREATE DATASET Customer(CustomerType)
+PRIMARY KEY c_custkey;
+
+CREATE DATASET Orders(OrderType)
+PRIMARY KEY o_orderkey;
+
+
+CREATE INDEX customer_fk_nation ON Customer (c_nationkey);
+CREATE INDEX orders_fk_customer ON Orders (o_custkey);
+CREATE INDEX orders_orderdateIx ON Orders (o_orderdate);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.01.update.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.01.update.sqlpp
new file mode 100644
index 0000000..3336fd7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.01.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 tpch;
+
+LOAD DATASET Orders USING localfs (
+   (`path`=`asterix_nc1://data/tpch0.001/orders.tbl`),
+   (`format`=`delimited-text`),
+   (`delimiter`=`|`)
+);
+
+LOAD DATASET Customer using localfs (
+   (`path`=`asterix_nc1://data/tpch0.001/customer.tbl`),
+   (`format`=`delimited-text`),
+   (`delimiter`=`|`)
+);
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.10.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.10.query.sqlpp
new file mode 100644
index 0000000..45fa904
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.10.query.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.
+ */
+
+USE tpch;
+
+SELECT VALUE COUNT(*)
+FROM            Customer c
+LEFT OUTER JOIN Orders o
+ON  c.c_custkey  = o.o_custkey
+AND o.o_comment NOT LIKE '%special%requests%'
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.11.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.11.query.sqlpp
new file mode 100644
index 0000000..0d270a7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.11.query.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.
+ */
+
+USE tpch;
+
+SELECT VALUE COUNT(*)
+FROM            Customer c 
+LEFT OUTER JOIN Orders o 
+ON  c.c_custkey /*+indexnl*/ = o.o_custkey
+AND o.o_comment NOT LIKE '%special%requests%'
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.20.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.20.query.sqlpp
new file mode 100644
index 0000000..5f5562b
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.20.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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 tpch;
+
+SELECT VALUE COUNT(*)
+FROM            Customer c
+LEFT OUTER JOIN Orders o
+ON  c.c_custkey  = o.o_custkey
+AND o.o_comment NOT LIKE '%special%requests%'
+AND o.o_totalprice > 100000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.21.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.21.query.sqlpp
new file mode 100644
index 0000000..3057cd5
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.21.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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 tpch;
+
+SELECT VALUE COUNT(*)
+FROM            Customer c
+LEFT OUTER JOIN Orders o
+ON  c.c_custkey /*+indexnl*/ = o.o_custkey
+AND o.o_comment NOT LIKE '%special%requests%'
+AND o.o_totalprice > 100000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.30.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.30.query.sqlpp
new file mode 100644
index 0000000..717da7f
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.30.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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 tpch;
+
+SELECT VALUE COUNT(*)
+FROM            Customer c
+LEFT OUTER JOIN Orders o
+ON  c.c_custkey  = o.o_custkey
+AND o.o_comment NOT LIKE '%special%requests%'
+AND (CASE WHEN o.o_totalprice > 100000.0 THEN TRUE ELSE FALSE END)
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.31.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.31.query.sqlpp
new file mode 100644
index 0000000..5ba532e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.31.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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 tpch;
+
+SELECT VALUE COUNT(*)
+FROM            Customer c
+LEFT OUTER JOIN Orders o
+ON  c.c_custkey /*+indexnl*/ = o.o_custkey
+AND o.o_comment NOT LIKE '%special%requests%'
+AND (CASE WHEN o.o_totalprice > 100000.0 THEN TRUE ELSE FALSE END)
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.40.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.40.query.sqlpp
new file mode 100644
index 0000000..10475eb
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.40.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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 tpch;
+
+SELECT VALUE COUNT(*)
+FROM            Customer c
+LEFT OUTER JOIN Orders o
+ON  c.c_custkey  = o.o_custkey
+AND o.o_comment NOT LIKE '%special%requests%'
+AND (SELECT VALUE SUM(o.o_orderkey) FROM range(1, 10) x)[0] > 30000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.41.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.41.query.sqlpp
new file mode 100644
index 0000000..d8b1908
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.41.query.sqlpp
@@ -0,0 +1,27 @@
+/*
+ * 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 tpch;
+
+SELECT VALUE COUNT(*)
+FROM            Customer c
+LEFT OUTER JOIN Orders o
+ON  c.c_custkey /*+indexnl*/ = o.o_custkey
+AND o.o_comment NOT LIKE '%special%requests%'
+AND (SELECT VALUE SUM(o.o_orderkey) FROM range(1, 10) x)[0] > 30000
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.50.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.50.query.sqlpp
new file mode 100644
index 0000000..39f82b7
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.50.query.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.
+ */
+
+USE tpch;
+
+SELECT VALUE COUNT(*)
+FROM            Customer c
+LEFT OUTER JOIN Orders o
+ON  c.c_custkey  = o.o_custkey
+WHERE o.o_comment NOT LIKE '%special%requests%'
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.51.query.sqlpp b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.51.query.sqlpp
new file mode 100644
index 0000000..e15d56e
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/queries_sqlpp/join/nlj-outer/nlj-outer.51.query.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.
+ */
+
+USE tpch;
+
+SELECT VALUE COUNT(*)
+FROM            Customer c
+LEFT OUTER JOIN Orders o
+ON  c.c_custkey /*+indexnl*/ = o.o_custkey
+WHERE o.o_comment NOT LIKE '%special%requests%'
\ No newline at end of file
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.10.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.10.adm
new file mode 100644
index 0000000..f768f58
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.10.adm
@@ -0,0 +1 @@
+1535
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.11.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.11.adm
new file mode 100644
index 0000000..f768f58
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.11.adm
@@ -0,0 +1 @@
+1535
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.20.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.20.adm
new file mode 100644
index 0000000..6bf42b9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.20.adm
@@ -0,0 +1 @@
+757
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.21.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.21.adm
new file mode 100644
index 0000000..6bf42b9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.21.adm
@@ -0,0 +1 @@
+757
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.30.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.30.adm
new file mode 100644
index 0000000..6bf42b9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.30.adm
@@ -0,0 +1 @@
+757
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.31.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.31.adm
new file mode 100644
index 0000000..6bf42b9
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.31.adm
@@ -0,0 +1 @@
+757
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.40.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.40.adm
new file mode 100644
index 0000000..6822652
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.40.adm
@@ -0,0 +1 @@
+793
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.41.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.41.adm
new file mode 100644
index 0000000..6822652
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.41.adm
@@ -0,0 +1 @@
+793
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.50.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.50.adm
new file mode 100644
index 0000000..000b173
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.50.adm
@@ -0,0 +1 @@
+1485
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.51.adm b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.51.adm
new file mode 100644
index 0000000..000b173
--- /dev/null
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/results/join/nlj-outer/nlj-outer.51.adm
@@ -0,0 +1 @@
+1485
diff --git a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
index 910aff3..42f7921 100644
--- a/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
+++ b/asterixdb/asterix-app/src/test/resources/runtimets/testsuite_sqlpp.xml
@@ -6700,6 +6700,11 @@
       </compilation-unit>
     </test-case>
     <test-case FilePath="join">
+      <compilation-unit name="nlj-outer">
+        <output-dir compare="Text">nlj-outer</output-dir>
+      </compilation-unit>
+    </test-case>
+    <test-case FilePath="join">
       <compilation-unit name="nlj_partitioning_property">
         <output-dir compare="Text">nlj_partitioning_property</output-dir>
       </compilation-unit>
diff --git a/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/ConsolidateLeftOuterJoinSelectsRule.java b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/ConsolidateLeftOuterJoinSelectsRule.java
new file mode 100644
index 0000000..3114524
--- /dev/null
+++ b/hyracks-fullstack/algebricks/algebricks-rewriter/src/main/java/org/apache/hyracks/algebricks/rewriter/rules/ConsolidateLeftOuterJoinSelectsRule.java
@@ -0,0 +1,112 @@
+/*
+ * 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.
+ */
+package org.apache.hyracks.algebricks.rewriter.rules;
+
+import java.util.ArrayList;
+import java.util.List;
+
+import org.apache.commons.lang3.mutable.Mutable;
+import org.apache.commons.lang3.mutable.MutableObject;
+import org.apache.hyracks.algebricks.common.exceptions.AlgebricksException;
+import org.apache.hyracks.algebricks.core.algebra.base.ILogicalExpression;
+import org.apache.hyracks.algebricks.core.algebra.base.ILogicalOperator;
+import org.apache.hyracks.algebricks.core.algebra.base.IOptimizationContext;
+import org.apache.hyracks.algebricks.core.algebra.base.LogicalOperatorTag;
+import org.apache.hyracks.algebricks.core.algebra.base.LogicalVariable;
+import org.apache.hyracks.algebricks.core.algebra.expressions.IAlgebricksConstantValue;
+import org.apache.hyracks.algebricks.core.algebra.expressions.ScalarFunctionCallExpression;
+import org.apache.hyracks.algebricks.core.algebra.functions.AlgebricksBuiltinFunctions;
+import org.apache.hyracks.algebricks.core.algebra.functions.IFunctionInfo;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.LeftOuterJoinOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.LeftOuterUnnestMapOperator;
+import org.apache.hyracks.algebricks.core.algebra.operators.logical.SelectOperator;
+import org.apache.hyracks.algebricks.core.rewriter.base.IAlgebraicRewriteRule;
+
+/**
+ * In case {@link LeftOuterJoinOperator} gets transformed into {@link LeftOuterUnnestMapOperator}, the plan could
+ * end up with multiple SELECT operators (broken up by {@link BreakSelectIntoConjunctsRule}).
+ * This rule consolidate those SELECT operators back again.
+ * <p>
+ * Example:
+ * select (and([JOIN_CONDITION], eq($$o.getField("x"), 1)))
+ * -- unnest $$o <- dataset(...)
+ * <p>
+ * After {@link BreakSelectIntoConjunctsRule}
+ * select ([JOIN_CONDITION])
+ * -- select (eq($$o.getField("x"), 1))
+ * -- -- unnest $$o <- dataset(...)
+ * <p>
+ * Before accessMethod rewrite:
+ * left outer join ([JOIN_CONDITION])
+ * -- data-scan []<-[$$56, $$c] <- ...
+ * -- select (eq($$o.getField("x"), 1))
+ * -- -- data-scan []<-[$$57, $$o] <- ...
+ * <p>
+ * After accessMethod rewrite:
+ * select ([JOIN_CONDITION]) retain-untrue (... <- missing)
+ * -- select (eq($$o.getField("x"), 1))
+ * -- -- left-outer-unnest-map ...
+ * -- -- -- ...
+ * <p>
+ * After this rule:
+ * select (and([JOIN_CONDITION], eq($$o.getField("x"), 1))) retain-untrue (... <- missing)
+ * -- left-outer-unnest-map ...
+ */
+public class ConsolidateLeftOuterJoinSelectsRule implements IAlgebraicRewriteRule {
+    private final List<Mutable<ILogicalExpression>> conditions = new ArrayList<>();
+
+    @Override
+    public boolean rewritePre(Mutable<ILogicalOperator> opRef, IOptimizationContext context)
+            throws AlgebricksException {
+        ILogicalOperator op = opRef.getValue();
+        if (op.getOperatorTag() != LogicalOperatorTag.SELECT) {
+            return false;
+        }
+
+        conditions.clear();
+        IAlgebricksConstantValue missingValue = null;
+        LogicalVariable missingVariable = null;
+        ILogicalOperator nextOp = op;
+        do {
+            SelectOperator selectOp = (SelectOperator) nextOp;
+            if (missingValue == null) {
+                // Capture the SELECT that contains the retain missing value and variable placeholder
+                missingValue = selectOp.getRetainMissingAsValue();
+                missingVariable = selectOp.getMissingPlaceholderVariable();
+            }
+            conditions.add(new MutableObject<>(selectOp.getCondition().getValue()));
+            nextOp = nextOp.getInputs().get(0).getValue();
+        } while (nextOp.getOperatorTag() == LogicalOperatorTag.SELECT);
+
+        if (conditions.size() < 2 || missingValue == null) {
+            return false;
+        }
+
+        SelectOperator newSelect = new SelectOperator(createAndCondition(context), missingValue, missingVariable);
+        newSelect.getInputs().add(new MutableObject<>(nextOp));
+        opRef.setValue(newSelect);
+        context.computeAndSetTypeEnvironmentForOperator(newSelect);
+        return true;
+    }
+
+    private Mutable<ILogicalExpression> createAndCondition(IOptimizationContext context) {
+        IFunctionInfo fInfo = context.getMetadataProvider().lookupFunction(AlgebricksBuiltinFunctions.AND);
+        return new MutableObject<>(new ScalarFunctionCallExpression(fInfo, new ArrayList<>(conditions)));
+    }
+}