feldera · mihaibudiu · Jul 7, 2026 · Jul 6, 2026
diff --git a/docs.feldera.com/docs/sql/array.md b/docs.feldera.com/docs/sql/array.md
@@ -49,7 +49,27 @@ CREATE VIEW V AS SELECT city, data.country
 FROM data CROSS JOIN UNNEST(data.cities) AS city;
 ```
 
-`UNNEST` applied to a `NULL` value returns an empty table.
+`CROSS APPLY` is another spelling for the same query:
+
+```sql
+CREATE VIEW V AS SELECT city, data.country
+FROM data CROSS APPLY UNNEST(data.cities) AS city;
+```
+
+`UNNEST` applied to a `NULL` value returns an empty table.  As a
+consequence, the queries above drop the rows of `data` where `cities`
+is `NULL` or empty.  The join forms that instead keep such rows,
+`LEFT JOIN UNNEST(...) ON TRUE` and `OUTER APPLY UNNEST(...)`, are not
+yet supported; see [unsupported
+operations](unsupported-operations.md#left-join-unnest).  To keep the
+rows where the array is `NULL`, substitute a one-element array in the
+`UNNEST` argument:
+
+```sql
+-- A row with a NULL cities array produces one row with a NULL city
+CREATE VIEW V AS SELECT city, country
+FROM data, UNNEST(COALESCE(cities, ARRAY[NULL])) AS t (city);
+```
 
 Note that applying `UNNEST` to an `ARRAY` of structure-typed objects
 will produce a collection whose columns are the fields of the

diff --git a/docs.feldera.com/docs/sql/unsupported-operations.md b/docs.feldera.com/docs/sql/unsupported-operations.md
@@ -73,6 +73,17 @@ subqueries. In these cases, we recommend users refactor the query.
 
 See [#2555](https://github.com/feldera/feldera/issues/2555).
 
+## `LEFT JOIN UNNEST`
+
+`LEFT JOIN UNNEST(...)` and `OUTER APPLY (...)`, are not yet supported:
+
+```sql
+-- NOT supported: LEFT JOIN UNNEST
+SELECT s.id, mention_id
+FROM spreadsheet s
+LEFT JOIN UNNEST(s.mentions) AS m(mention_id) ON TRUE;
+```
+
 ## Map functions
 
 Several `MAP` functions are not yet implemented:

diff --git a/...-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/frontend/CalciteToDBSPCompiler.java b/...-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/frontend/CalciteToDBSPCompiler.java
@@ -412,12 +412,9 @@ void visitCorrelate(LogicalCorrelate correlate) {
         CalciteObject node = CalciteObject.create(correlate);
         DBSPTypeTuple type = this.convertType(
                 node.getPositionRange(), correlate.getRowType(), false).to(DBSPTypeTuple.class);
-        /*
-        The join type does not influence the results of the decorrelate!
-        A Decorrelate is a cross join, and an outer cross join is equivalent to an inner cross join.
-        if (correlate.getJoinType().isOuterJoin())
-            throw this.decorrelateError(node);
-         */
+
+        if (correlate.getJoinType() != JoinRelType.INNER)
+            throw new UnimplementedException("LEFT JOIN UNNEST");
         this.visit(correlate.getLeft(), 0, correlate);
         DBSPSimpleOperator left = this.getInputAs(correlate.getLeft(), true);
         DBSPTypeTuple leftElementType = left.getOutputZSetElementType().to(DBSPTypeTuple.class);
@@ -692,7 +689,7 @@ void visitUncollect(Uncollect uncollect) {
             indexType = tuple.getFieldType(tuple.size() - 1);
         }
         if (inputRowType.size() > 1) {
-            throw new UnimplementedException("UNNEST with multiple vectors", node);
+            throw new UnimplementedException("UNNEST of multiple collections", node);
         }
         DBSPVariablePath data = new DBSPVariablePath(inputRowType.ref());
         DBSPType arrayType = data.deref().field(0).getType();

diff --git a/...ler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/ir/expression/DBSPZSetExpression.java b/...ler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/ir/expression/DBSPZSetExpression.java
@@ -11,6 +11,8 @@
 import org.dbsp.sqlCompiler.ir.DBSPNode;
 import org.dbsp.sqlCompiler.ir.IDBSPInnerNode;
 import org.dbsp.sqlCompiler.ir.ISameValue;
+import org.dbsp.sqlCompiler.ir.expression.literal.DBSPLiteral;
+import org.dbsp.sqlCompiler.ir.expression.literal.DBSPNullLiteral;
 import org.dbsp.sqlCompiler.ir.type.DBSPType;
 import org.dbsp.sqlCompiler.ir.type.derived.DBSPTypeTupleBase;
 import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeBaseType;
@@ -162,6 +164,15 @@ public DBSPZSetExpression addUsingCast(DBSPZSetExpression other) {
     }
 
     public DBSPExpression castRecursive(DBSPExpression expression, DBSPType type) {
+        if (expression.is(DBSPNullLiteral.class)) {
+            return DBSPLiteral.none(type);
+        }
+        if (expression.is(DBSPSomeExpression.class)) {
+            return this.castRecursive(expression.to(DBSPSomeExpression.class).expression, type);
+        }
+        if (!type.is(DBSPTypeBaseType.class) && expression.is(DBSPCastExpression.class)) {
+            return this.castRecursive(expression.to(DBSPCastExpression.class).source, type);
+        }
         if (type.is(DBSPTypeBaseType.class)) {
             return expression.cast(expression.getNode(), type, DBSPCastExpression.CastType.SqlUnsafe);
         } else if (type.is(DBSPTypeArray.class)) {

diff --git a/...er/src/test/java/org/dbsp/sqlCompiler/compiler/sql/simple/IncrementalRegressionTests.java b/...er/src/test/java/org/dbsp/sqlCompiler/compiler/sql/simple/IncrementalRegressionTests.java
@@ -1389,8 +1389,7 @@ CREATE VIEW v AS (
                     FROM T
                     CROSS JOIN UNNEST(split(T.s, '/')) WITH ORDINALITY AS R(sid, o)
                     WHERE sid <> ''
-                );
-                """);
+                );""");
         // Validated on postgres by replacing 'split' with 'string_to_array'
         ccs.step("INSERT INTO T VALUES(0, 'a/b/c'), (1, '/a//b'), (2, 'd');", """
                  id | sid | o | weight

diff --git a/.../SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/simple/RegressionTests.java b/.../SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/simple/RegressionTests.java
@@ -1977,7 +1977,7 @@ public void issue3094() {
     public void issue3547() {
         this.queryFailingInCompilation(
                 "SELECT * FROM UNNEST(ARRAY [1, 2, 3, 4, 5], ARRAY[3, 2, 1]);",
-                "UNNEST with multiple vectors");
+                "UNNEST of multiple collections");
     }
 
     @Test
@@ -1994,7 +1994,7 @@ create table latest_cells(
                     m.mentioned_id
                 from
                     latest_cells s
-                left join unnest(s.mentioned_cell_ids) as m(mentioned_id) on true;
+                join unnest(s.mentioned_cell_ids) as m(mentioned_id) on true;
 
                 create local view l1 as
                 select
@@ -2011,6 +2011,34 @@ create table latest_cells(
                  -------------------""");
     }
 
+    @Test
+    public void issue2736b() {
+        this.statementsFailingInCompilation("""
+                create table latest_cells(
+                  id integer,
+                  mentioned_cell_ids integer array
+                );
+
+                create local view l as
+                select
+                    s.id,
+                    m.mentioned_id
+                from
+                    latest_cells s
+                left join unnest(s.mentioned_cell_ids) as m(mentioned_id) on true;
+
+                create local view l1 as
+                select
+                    s.id,
+                    m.mentioned_id
+                from
+                    latest_cells s
+                join unnest(s.mentioned_cell_ids) as m(mentioned_id) on true;
+
+                create view e as (SELECT * FROM l) EXCEPT (SELECT * FROM l1);""",
+                "Not yet implemented: LEFT JOIN UNNEST");
+    }
+
     @Test
     public void testChain() {
         var ccs = this.getCCS("""

diff --git a/...iler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/tools/BaseSQLTests.java b/...iler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/tools/BaseSQLTests.java
@@ -32,6 +32,7 @@
 import org.dbsp.sqlCompiler.compiler.backend.rust.multi.MultiCrates;
 import org.dbsp.sqlCompiler.compiler.backend.rust.multi.MultiCratesWriter;
 import org.dbsp.sqlCompiler.compiler.frontend.calciteCompiler.SqlToRelCompiler;
+import org.dbsp.sqlCompiler.compiler.frontend.calciteCompiler.optimizer.CalciteOptimizer;
 import org.dbsp.sqlCompiler.compiler.sql.MultiCrateTests;
 import org.dbsp.sqlCompiler.compiler.visitors.outer.CircuitPostfix;
 import org.dbsp.sqlCompiler.compiler.visitors.outer.LowerCircuitVisitor;
@@ -125,6 +126,11 @@ public static void showPlan() {
         Logger.INSTANCE.setLoggingLevel(SqlToRelCompiler.class, 2);
     }
 
+    @SuppressWarnings("unused")
+    public static void showCalciteOptimizer() {
+        Logger.INSTANCE.setLoggingLevel(CalciteOptimizer.class, 2);
+    }
+
     @SuppressWarnings("unused")
     public static void instrumentApply() {
         Logger.INSTANCE.setLoggingLevel(LowerCircuitVisitor.class, 2);