Skip to content

[SQL] Implement UNNEST for MAP values #4198

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
mihaibudiu merged 1 commit into from
Jun 19, 2025
Merged

[SQL] Implement UNNEST for MAP values #4198

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
2 changes: 1 addition & 1 deletion docs.feldera.com/docs/sql/array.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -49,7 +49,7 @@ 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 array.
`UNNEST` applied to a `NULL` value returns an empty table.

Note that applying `UNNEST` to an `ARRAY` of structure-typed objects
will produce a collection whose columns are the fields of the
Expand Down
2 changes: 1 addition & 1 deletion docs.feldera.com/docs/sql/function-index.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -222,7 +222,7 @@
* `UNION`: [grammar](grammar.md#setop)
* `UNION ALL`: [grammar](grammar.md#setop)
* `UNIQUE`: [comparisons](comparisons.md#unique)
* `UNNEST`: [array](array.md#the-unnest-sql-operator)
* `UNNEST`: [array](array.md#the-unnest-sql-operator), [map](map.md#the-unnest-operator)
* `UPPER`: [string](string.md#upper)
* `VALUES`: [grammar](grammar.md#values)
* `WEEK`: [datetime](datetime.md#week)
Expand Down
18 changes: 18 additions & 0 deletions docs.feldera.com/docs/sql/map.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -49,3 +49,21 @@ Comparison operations (`=`, `<>`, `!=`, `>`, `<`, `>=`, `<=`) can be applied to
| <a id="cardinality"></a>`CARDINALITY`(map) | Returns the number of key-value pairs in the map. | `CARDINALITY(MAP['x', 4])` => 1 |
| <a id="map_contains_key"></a>`MAP_CONTAINS_KEY`(map, key) | Returns true when the map has an item with the specified key; `NULL` if any argument is `NULL`. | `MAP_CONTAINS_KEY(MAP['x', 4], 'x')` => `true` |

## The `UNNEST` Operator

The `UNNEST` operator takes a `MAP` and returns a table with a row for
each key-value pair in the `MAP`. If the input is a
map with 5 key-value pairs, the output is a table with 5 rows, each
row holding one key-value pair of the map.

When `UNNEST` operator is used in self-joins as follows, an alias needs
to be used to name the key and value fields (`zips(city, zip)` in the example):

```sql
CREATE TABLE data(zipcodes MAP<VARCHAR, INT>, COUNTRY VARCHAR);

CREATE VIEW V AS SELECT data.country, city, zip
FROM data CROSS JOIN UNNEST(data.zipcodes) AS zips(city, zip);
```

`UNNEST` applied to a `NULL` value returns an empty table.
19 changes: 11 additions & 8 deletions ...-compiler/src/main/java/org/dbsp/sqlCompiler/compiler/frontend/CalciteToDBSPCompiler.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -188,6 +188,7 @@
import org.dbsp.sqlCompiler.ir.statement.DBSPStructItem;
import org.dbsp.sqlCompiler.ir.type.DBSPType;
import org.dbsp.sqlCompiler.ir.type.DBSPTypeCode;
import org.dbsp.sqlCompiler.ir.type.ICollectionType;
import org.dbsp.sqlCompiler.ir.type.derived.DBSPTypeFunction;
import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeAny;
import org.dbsp.sqlCompiler.ir.type.derived.DBSPTypeRawTuple;
Expand Down Expand Up @@ -501,8 +502,8 @@ void visitCorrelate(LogicalCorrelate correlate) {
ExpressionCompiler eComp = new ExpressionCompiler(correlate, dataVar, this.compiler);
DBSPClosureExpression arrayExpression = eComp.compile(projection).closure(dataVar);
DBSPTypeTuple uncollectElementType = this.convertType(uncollect.getRowType(), false).to(DBSPTypeTuple.class);
DBSPType arrayElementType = arrayExpression.getResultType().to(DBSPTypeArray.class).getElementType();
if (arrayElementType.mayBeNull)
DBSPType collectionElementType = arrayExpression.getResultType().to(ICollectionType.class).getElementType();
if (collectionElementType.mayBeNull)
// This seems to be a bug in Calcite, we should not need to do this adjustment
uncollectElementType = uncollectElementType.withMayBeNull(true).to(DBSPTypeTuple.class);

Expand All @@ -529,8 +530,8 @@ void visitCorrelate(LogicalCorrelate correlate) {
else
shuffleSize += 1;
}
if (arrayElementType.is(DBSPTypeTupleBase.class))
shuffleSize += arrayElementType.to(DBSPTypeTupleBase.class).size();
if (collectionElementType.is(DBSPTypeTupleBase.class))
shuffleSize += collectionElementType.to(DBSPTypeTupleBase.class).size();
else
shuffleSize += 1;
DBSPTypeFunction functionType = new DBSPTypeFunction(type, leftElementType.ref());
Expand Down Expand Up @@ -706,7 +707,8 @@ void visitUncollect(Uncollect uncollect) {
throw new UnimplementedException("UNNEST with multiple vectors", node);
}
DBSPVariablePath data = new DBSPVariablePath(inputRowType.ref());
DBSPType arrayElementType = data.deref().field(0).getType();
DBSPType arrayType = data.deref().field(0).getType();
DBSPType collectionElementType = arrayType.to(ICollectionType.class).getElementType();
DBSPClosureExpression getField0 = data.deref().field(0).closure(data);
DBSPTypeFunction functionType = new DBSPTypeFunction(type, inputRowType.ref());

Expand All @@ -717,10 +719,11 @@ void visitUncollect(Uncollect uncollect) {
else
shuffleSize += 1;
}
if (arrayElementType.is(DBSPTypeTupleBase.class))
shuffleSize += arrayElementType.to(DBSPTypeTupleBase.class).size();
else
if (collectionElementType.is(DBSPTypeTupleBase.class)) {
shuffleSize += collectionElementType.to(DBSPTypeTupleBase.class).size();
} else {
shuffleSize += 1;
}

DBSPFlatmap function = new DBSPFlatmap(node, functionType, inputRowType, getField0,
Linq.list(), null, indexType, new IdShuffle(shuffleSize));
Expand Down
2 changes: 1 addition & 1 deletion ...rc/main/java/org/dbsp/sqlCompiler/compiler/frontend/calciteCompiler/CalciteFunctions.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -129,7 +129,7 @@ record Func(SqlOperator function, String functionName, SqlLibrary library,
new Func(SqlStdOperatorTable.IGNORE_NULLS, "IGNORE NULLS", SqlLibrary.STANDARD, "grammar#window-aggregates", false),
new Func(SqlStdOperatorTable.RESPECT_NULLS, "RESPECT NULLS", SqlLibrary.STANDARD, "grammar#window-aggregates", false),
new Func(SqlStdOperatorTable.MINUS_DATE, "-", SqlLibrary.STANDARD, "datetime", false),
new Func(SqlStdOperatorTable.UNNEST, "UNNEST", SqlLibrary.STANDARD, "array#the-unnest-sql-operator", false),
new Func(SqlStdOperatorTable.UNNEST, "UNNEST", SqlLibrary.STANDARD, "array#the-unnest-sql-operator,map#the-unnest-operator", false),
new Func(SqlStdOperatorTable.UNNEST_WITH_ORDINALITY, "UNNEST WITH ORDINALITY", SqlLibrary.STANDARD, "", false),
new Func(SqlStdOperatorTable.LATERAL, "LATERAL", SqlLibrary.STANDARD, "grammar#lateral", false),
new Func(SqlStdOperatorTable.COLLECTION_TABLE, "TABLE", SqlLibrary.STANDARD, "grammar", false),
Expand Down
41 changes: 27 additions & 14 deletions ...piler/src/main/java/org/dbsp/sqlCompiler/compiler/visitors/outer/LowerCircuitVisitor.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -16,6 +16,7 @@
import org.dbsp.sqlCompiler.circuit.operator.DBSPStreamAggregateOperator;
import org.dbsp.sqlCompiler.circuit.OutputPort;
import org.dbsp.sqlCompiler.compiler.DBSPCompiler;
import org.dbsp.sqlCompiler.compiler.errors.InternalCompilerError;
import org.dbsp.sqlCompiler.compiler.frontend.TypeCompiler;
import org.dbsp.sqlCompiler.compiler.frontend.calciteObject.CalciteRelNode;
import org.dbsp.sqlCompiler.ir.aggregate.DBSPFold;
Expand All @@ -42,12 +43,14 @@
import org.dbsp.sqlCompiler.ir.statement.DBSPLetStatement;
import org.dbsp.sqlCompiler.ir.statement.DBSPStatement;
import org.dbsp.sqlCompiler.ir.type.DBSPType;
import org.dbsp.sqlCompiler.ir.type.DBSPTypeCode;
import org.dbsp.sqlCompiler.ir.type.derived.DBSPTypeTuple;
import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeAny;
import org.dbsp.sqlCompiler.ir.type.derived.DBSPTypeRawTuple;
import org.dbsp.sqlCompiler.ir.type.derived.DBSPTypeTupleBase;
import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeUSize;
import org.dbsp.sqlCompiler.ir.type.user.DBSPComparatorType;
import org.dbsp.sqlCompiler.ir.type.user.DBSPTypeMap;
import org.dbsp.sqlCompiler.ir.type.user.DBSPTypeVec;
import org.dbsp.sqlCompiler.ir.type.user.DBSPTypeWithCustomOrd;
import org.dbsp.sqlCompiler.ir.type.user.DBSPTypeZSet;
Expand All @@ -72,8 +75,8 @@ public static DBSPExpression rewriteFlatmap(
// move |x: &Tuple2<Array<i32>, Option<i32>>, | -> _ {
// let x0: Array<i32> = (*x.0).clone();
// let x1: x.1.clone();
// let array_clone = (*x).0.clone();
// array_clone.into_iter().map({
// let collection_clone = (*x).0.clone();
// collection_clone.into_iter().map({
// move |e: i32, | -> Tuple3<Vec<i32>, Option<i32>, i32> {
// Tuple3::new(x0.clone(), x1.clone(), e)
// }
Expand Down Expand Up @@ -159,30 +162,40 @@ public static DBSPExpression rewriteFlatmap(
}
resultColumns = flatmap.shuffle.shuffle(resultColumns);

// let array = if (*x).0.is_none() {
// let collection = if (*x).0.is_none() {
// vec!()
// } else {
// (*x).0.clone().unwrap()
// };
// or
// let array = (*x).0.clone();
DBSPExpression extractArray = flatmap.collectionExpression.call(rowVar).applyClone();
// let collection = (*x).0.clone();
DBSPExpression extractCollection = flatmap.collectionExpression.call(rowVar).applyClone();
if (compiler != null)
extractArray = extractArray.reduce(compiler);
DBSPLetStatement statement = new DBSPLetStatement("array", extractArray);
extractCollection = extractCollection.reduce(compiler);
DBSPLetStatement statement = new DBSPLetStatement("collection", extractCollection);
statements.add(statement);
DBSPType arrayType = extractArray.getType();
DBSPType collectionType = extractCollection.getType();

DBSPExpression arrayExpression;
if (arrayType.mayBeNull) {
DBSPExpression collectionExpression;
if (collectionType.mayBeNull) {
DBSPExpression condition = statement.getVarReference().is_null();
DBSPExpression empty = new DBSPTypeVec(collectionElementType, false).emptyVector();
DBSPExpression empty;
if (flatmap.collectionKind == DBSPTypeCode.ARRAY)
empty = new DBSPTypeVec(collectionElementType, false).emptyVector();
else if (flatmap.collectionKind == DBSPTypeCode.MAP) {
DBSPTypeTuple tup = collectionElementType.to(DBSPTypeTuple.class);
Utilities.enforce(tup.size() == 2);
empty = new DBSPTypeMap(tup.tupFields[0], tup.tupFields[1], false).defaultValue();
} else {
throw new InternalCompilerError("Unexpected collection type in flatmap " + flatmap.collectionKind);
}

DBSPExpression contents = statement.getVarReference().unwrap().deref().applyClone();
arrayExpression = new DBSPIfExpression(flatmap.getNode(), condition, empty, contents);
collectionExpression = new DBSPIfExpression(flatmap.getNode(), condition, empty, contents);
} else {
arrayExpression = statement.getVarReference().deref().applyClone();
collectionExpression = statement.getVarReference().deref().applyClone();
}
statement = new DBSPLetStatement("array_clone", arrayExpression);
statement = new DBSPLetStatement("collection_clone", collectionExpression);
statements.add(statement);

// move |e: i32, | -> Tuple3<Vec<i32>, Option<i32>, i32> {
Expand Down
15 changes: 15 additions & 0 deletions ...p-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/ir/expression/DBSPFlatmap.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2,6 +2,7 @@

import com.fasterxml.jackson.databind.JsonNode;
import org.dbsp.sqlCompiler.compiler.backend.JsonDecoder;
import org.dbsp.sqlCompiler.compiler.errors.UnimplementedException;
import org.dbsp.sqlCompiler.compiler.visitors.outer.LowerCircuitVisitor;
import org.dbsp.sqlCompiler.compiler.frontend.calciteObject.CalciteObject;
import org.dbsp.sqlCompiler.compiler.visitors.VisitDecision;
Expand All @@ -15,6 +16,7 @@
import org.dbsp.sqlCompiler.ir.type.derived.DBSPTypeTupleBase;
import org.dbsp.sqlCompiler.ir.type.ICollectionType;
import org.dbsp.sqlCompiler.ir.type.primitive.DBSPTypeBaseType;
import org.dbsp.sqlCompiler.ir.type.user.DBSPTypeArray;
import org.dbsp.util.IIndentStream;
import org.dbsp.util.Linq;
import org.dbsp.util.Shuffle;
Expand Down Expand Up @@ -55,6 +57,12 @@
* The result type is the output element type.
*/
public final class DBSPFlatmap extends DBSPExpression {
// The kind of collection that we are iterating on
enum CollectionKind {
Array,
Map,
}

/** Type of the input row. */
public final DBSPTypeTuple inputElementType;
/** A closure which, applied to 'data', produces the
Expand All @@ -77,6 +85,7 @@ public final class DBSPFlatmap extends DBSPExpression {
public final DBSPType ordinalityIndexType;
/** Shuffle to apply to elements in the produced tuple */
public final Shuffle shuffle;
public final DBSPTypeCode collectionKind;

public DBSPFlatmap(CalciteObject node,
DBSPTypeFunction resultElementType,
Expand All @@ -91,7 +100,13 @@ public DBSPFlatmap(CalciteObject node,
this.inputElementType = inputElementType;
this.rightProjections = rightProjections;
this.collectionExpression = collectionExpression;
this.collectionKind = collectionExpression.getResultType().code;
this.leftInputIndexes = leftInputIndexes;
Utilities.enforce(this.collectionKind == DBSPTypeCode.ARRAY ||
this.collectionKind == DBSPTypeCode.MAP);
if (ordinalityIndexType != null && this.collectionKind == DBSPTypeCode.MAP) {
throw new UnimplementedException("UNNEST with ORDINALITY not supported for MAP values", node);
}
Utilities.enforce(collectionExpression.parameters.length == 1);
Utilities.enforce(collectionExpression.parameters[0].type.sameType(this.inputElementType.ref()),
"Collection expression expects " + collectionExpression.parameters[0].type
Expand Down
16 changes: 14 additions & 2 deletions ...sp-compiler/SQL-compiler/src/main/java/org/dbsp/sqlCompiler/ir/type/user/DBSPTypeMap.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -7,17 +7,24 @@
import org.dbsp.sqlCompiler.ir.expression.DBSPExpression;
import org.dbsp.sqlCompiler.ir.expression.DBSPMapExpression;
import org.dbsp.sqlCompiler.ir.type.DBSPType;
import org.dbsp.sqlCompiler.ir.type.ICollectionType;
import org.dbsp.sqlCompiler.ir.type.derived.DBSPTypeRawTuple;
import org.dbsp.sqlCompiler.ir.type.derived.DBSPTypeTuple;
import org.dbsp.util.Linq;
import org.dbsp.util.Utilities;

import java.util.List;

import static org.dbsp.sqlCompiler.ir.type.DBSPTypeCode.MAP;

/** Represents the type of a Rust Map as a TypeUser. */
public class DBSPTypeMap extends DBSPTypeUser {
/** Represents the type of a Rust Map as a TypeUser.
* As collection, it contains key-value pair tuples. */
public class DBSPTypeMap extends DBSPTypeUser implements ICollectionType {
public final DBSPTypeRawTuple collectionElementType;

public DBSPTypeMap(DBSPType mapKeyType, DBSPType mapValueType, boolean mayBeNull) {
super(mapKeyType.getNode(), MAP, "Map", mayBeNull, mapKeyType, mapValueType);
this.collectionElementType = new DBSPTypeRawTuple(this.getKeyType(), this.getValueType());
}

public DBSPType getKeyType() {
Expand Down Expand Up @@ -78,4 +85,9 @@ public static DBSPTypeMap fromJson(JsonNode node, JsonDecoder decoder) {
boolean mayBeNull = fromJsonMayBeNull(node);
return new DBSPTypeMap(typeArgs.get(0), typeArgs.get(1), mayBeNull);
}

@Override
public DBSPType getElementType() {
return this.collectionElementType;
}
}
5 changes: 3 additions & 2 deletions ...ompiler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/MultiCrateTests.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -29,7 +29,7 @@ void compileToMultiCrate(String file, boolean check) throws SQLException, IOExce
messages.print();
throw new RuntimeException("Error during compilation");
}
if (check)
if (check && !BaseSQLTests.skipRust)
Utilities.compileAndCheckRust(BaseSQLTests.RUST_CRATES_DIRECTORY, true);
}

Expand Down Expand Up @@ -182,7 +182,8 @@ public void testPackagedDemos() throws SQLException, IOException, InterruptedExc
this.appendCargoDependencies(cargoContents);
}
}
Utilities.compileAndCheckRust(BaseSQLTests.RUST_CRATES_DIRECTORY, true);
if (!BaseSQLTests.skipRust)
Utilities.compileAndCheckRust(BaseSQLTests.RUST_CRATES_DIRECTORY, true);
if (udf != null) {
//noinspection ResultOfMethodCallIgnored
udf.delete();
Expand Down
16 changes: 10 additions & 6 deletions ...bsp-compiler/SQL-compiler/src/test/java/org/dbsp/sqlCompiler/compiler/sql/OtherTests.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -290,15 +290,16 @@ WITH LOW_PRICE_CTE AS (select part, MIN(price) as price from PRICE group by part
CompilerMessages messages = CompilerMain.execute("-o", BaseSQLTests.TEST_FILE_PATH, file.getPath());
if (messages.errorCount() > 0)
throw new RuntimeException(messages.toString());
Utilities.compileAndCheckRust(BaseSQLTests.RUST_DIRECTORY, true);
if (!BaseSQLTests.skipRust)
Utilities.compileAndCheckRust(BaseSQLTests.RUST_DIRECTORY, true);
}

void compileFile(String file, boolean run) throws SQLException, IOException, InterruptedException {
CompilerMessages messages = CompilerMain.execute(
"-i", "--alltables", "-q", "--ignoreOrder", "-o", BaseSQLTests.TEST_FILE_PATH, file);
messages.print();
Assert.assertEquals(0, messages.errorCount());
if (run)
if (run && !BaseSQLTests.skipRust)
Utilities.compileAndCheckRust(BaseSQLTests.RUST_DIRECTORY, true);
// cleanup after ourselves
createEmptyStubs();
Expand Down Expand Up @@ -418,7 +419,8 @@ public void testRustCompiler() throws IOException, InterruptedException, SQLExce
CompilerMessages messages = CompilerMain.execute("-q", "-o", BaseSQLTests.TEST_FILE_PATH, file.getPath());
messages.print();
Assert.assertEquals(0, messages.exitCode);
Utilities.compileAndCheckRust(BaseSQLTests.RUST_DIRECTORY, true);
if (!BaseSQLTests.skipRust)
Utilities.compileAndCheckRust(BaseSQLTests.RUST_DIRECTORY, true);
}

@Test
Expand Down Expand Up @@ -501,7 +503,8 @@ pub fn test() {
try (FileWriter fr = new FileWriter(rust, true)) { // append
fr.write(rustHandlesTest);
}
Utilities.compileAndCheckRust(BaseSQLTests.RUST_DIRECTORY, true);
if (!BaseSQLTests.skipRust)
Utilities.compileAndCheckRust(BaseSQLTests.RUST_DIRECTORY, true);

// Second test
message = CompilerMain.execute(
Expand All @@ -513,7 +516,8 @@ pub fn test() {
try (FileWriter fr = new FileWriter(rust, true)) { // append
fr.write(rustCatalogTest);
}
Utilities.compileAndCheckRust(BaseSQLTests.RUST_DIRECTORY, true);
if (!BaseSQLTests.skipRust)
Utilities.compileAndCheckRust(BaseSQLTests.RUST_DIRECTORY, true);
}

@Test
Expand Down Expand Up @@ -635,7 +639,7 @@ public void serializationTest() throws JsonProcessingException {
public void generateFunctionIndex() throws IOException {
// When invoked it generates documentation for the supported functions and operators
// in the specified file.
String file = "../../docs/sql/function-index.md";
String file = "../../docs.feldera.com/docs/sql/function-index.md";
FunctionDocumentation.generateIndex(file);
}

Expand Down
Loading
Loading