Repository: squashql/squashql
Pull request #245 (2024-01-19 11:33:48)

[Experimental] JOIN of two SELECT statement results can guess the join condition
If no condition is passed, the engine tries to detect the join condition based on the name of the columns of the two select statement. 

In addition to that, `JoinType.CROSS` has been added to handle the case where the two select statements have no column in common. 


Patch:
diff --git a/core/src/main/java/io/squashql/query/ExperimentalQueryMergeExecutor.java b/core/src/main/java/io/squashql/query/ExperimentalQueryMergeExecutor.java
index e4b0abd..a459194 100644
--- a/core/src/main/java/io/squashql/query/ExperimentalQueryMergeExecutor.java
+++ b/core/src/main/java/io/squashql/query/ExperimentalQueryMergeExecutor.java
@@ -7,9 +7,11 @@ import io.squashql.query.database.QueryEngine;
 import io.squashql.query.database.QueryRewriter;
 import io.squashql.query.database.SQLTranslator;
 import io.squashql.query.dto.*;
+import io.squashql.store.UnknownType;
 import io.squashql.table.ColumnarTable;
 import io.squashql.table.Table;
 import io.squashql.type.AliasedTypedField;
+import io.squashql.type.TableTypedField;
 import io.squashql.type.TypedField;
 import lombok.AllArgsConstructor;
 import lombok.extern.slf4j.Slf4j;
@@ -125,13 +127,42 @@ public class ExperimentalQueryMergeExecutor {
   }
 
   private CompiledCriteria compiledCriteria(JoinType joinType, CriteriaDto joinCondition, Holder left, Holder right) {
-    QueryDto leftCteQuery = new QueryDto().table(left.cteTableName);
-    QueryDto rightCteQuery = new QueryDto().table(right.cteTableName);
-    CriteriaDto joinConditionCopy = JacksonUtil.deserialize(JacksonUtil.serialize(joinCondition), CriteriaDto.class);
-    CriteriaDto rewrittenJoinCondition = rewriteJoinCondition(joinConditionCopy);
-    leftCteQuery.table.join(new TableDto(rightCteQuery.table.name), joinType, rewrittenJoinCondition);
-    QueryResolver queryResolver = new QueryResolver(leftCteQuery, this.queryEngine.datastore().storesByName());
-    return queryResolver.compileCriteria(rewrittenJoinCondition);
+    if (joinCondition != null) {
+      QueryDto leftCteQuery = new QueryDto().table(left.cteTableName);
+      QueryDto rightCteQuery = new QueryDto().table(right.cteTableName);
+      CriteriaDto joinConditionCopy = JacksonUtil.deserialize(JacksonUtil.serialize(joinCondition), CriteriaDto.class);
+      CriteriaDto rewrittenJoinCondition = rewriteJoinCondition(joinConditionCopy);
+      leftCteQuery.table.join(new TableDto(rightCteQuery.table.name), joinType, rewrittenJoinCondition);
+      QueryResolver queryResolver = new QueryResolver(leftCteQuery, this.queryEngine.datastore().storesByName());
+      return queryResolver.compileCriteria(rewrittenJoinCondition);
+    } else {
+      // Try to guess the condition
+      List<TypedField> leftColumns = new ArrayList<>();
+      List<TypedField> rightColumns = new ArrayList<>();
+      for (Field field : left.query.columns) {
+        TypedField typedField = left.queryResolver.resolveField(field);
+        leftColumns.add(typedField.alias() != null ? new AliasedTypedField(typedField.alias()) : typedField); // we have to use the aliased field in the select
+      }
+      for (Field field : right.query.columns) {
+        TypedField typedField = right.queryResolver.resolveField(field);
+        rightColumns.add(typedField.alias() != null ? new AliasedTypedField(typedField.alias()) : typedField); // we have to use the aliased field in the select
+      }
+      leftColumns.retainAll(rightColumns);
+      if (!leftColumns.isEmpty()) {
+        List<CompiledCriteria> children = new ArrayList<>(leftColumns.size());
+        for (TypedField leftColumn : leftColumns) {
+          TableTypedField l = new TableTypedField(left.originalTableName, leftColumn.name(), UnknownType.class, leftColumn.alias());
+          TableTypedField r = new TableTypedField(right.originalTableName, leftColumn.name(), UnknownType.class, leftColumn.alias());
+          children.add(new CompiledCriteria(null, ConditionType.EQ, l, r, null, null));
+
+        }
+        return children.size() > 1
+                ? new CompiledCriteria(null, ConditionType.AND, null, null, null, children)
+                : children.get(0);
+      } else {
+        return null; // no condition
+      }
+    }
   }
 
   private static CriteriaDto rewriteJoinCondition(CriteriaDto joinCondition) {
@@ -165,9 +196,9 @@ public class ExperimentalQueryMergeExecutor {
         TypedField typedField = left.queryResolver.getTypedFieldOrNull(key);
         if (typedField == null) {
           typedField = (holder = right).queryResolver.getTypedFieldOrNull(key);
-        }
-        if (typedField == null) {
-          throw new RuntimeException("Cannot resolve " + e.getKey());
+          if (typedField == null) {
+            throw new RuntimeException("Cannot resolve " + e.getKey());
+          }
         }
         String orderByField = holder.queryRewriter.aliasOrFullExpression(typedField);
         orderByField = replaceTableNameByCteNameIfNotNull(holder, orderByField);
@@ -187,8 +218,6 @@ public class ExperimentalQueryMergeExecutor {
    * @return a list of select elements
    */
   private static Twin<List<TypedField>> getSelectElements(CompiledTable joinTable, Holder left, Holder right) {
-    // Try to guess from the conditions which field to keep.
-    CompiledJoin join = joinTable.joins().get(0);
     List<TypedField> leftColumns = new ArrayList<>();
     List<TypedField> rightColumns = new ArrayList<>();
     for (Field field : left.query.columns) {
@@ -196,16 +225,31 @@ public class ExperimentalQueryMergeExecutor {
       leftColumns.add(typedField.alias() != null ? new AliasedTypedField(typedField.alias()) : typedField); // we have to use the aliased field in the select
     }
 
-    Set<TypedField> joinFields = collectJoinFields(join.joinCriteria());
+    // Try to guess from the conditions which field to keep.
+    CompiledCriteria jc = joinTable.joins().get(0).joinCriteria();
+    Set<TypedField> joinFields = jc != null ? collectJoinFields(jc) : null;
     for (Field field : right.query.columns) {
       TypedField typedField = right.queryResolver.resolveField(field);
       TypedField tf = typedField.alias() != null ? new AliasedTypedField(typedField.alias()) : typedField;
       // Do not add if it is in the join. We keep only the other field from the left query.
-      if (!joinFields.contains(tf)) {
+      if (joinFields == null || !joinFields.contains(tf)) {
         rightColumns.add(tf); // we have to use the aliased field in the select
       }
     }
-    return Tuples.twin(leftColumns, rightColumns);
+
+    // Remove any ambiguity and keep left columns if right columns are contained in the left columns
+    Set<TypedField> inter = new HashSet<>(leftColumns);
+    inter.retainAll(rightColumns);
+    rightColumns.removeAll(leftColumns);
+    List<TypedField> newLeft = new ArrayList<>();
+    for (TypedField leftColumn : leftColumns) {
+      if (inter.contains(leftColumn)) {
+        newLeft.add(new TableTypedField(left.originalTableName, leftColumn.name(), UnknownType.class, leftColumn.alias()));
+      } else {
+        newLeft.add(leftColumn);
+      }
+    }
+    return Tuples.twin(newLeft, rightColumns);
   }
 
   private static Set<TypedField> collectJoinFields(CompiledCriteria joinCriteria) {
diff --git a/core/src/main/java/io/squashql/query/compiled/CompiledJoin.java b/core/src/main/java/io/squashql/query/compiled/CompiledJoin.java
index 316dd80..8fb0f12 100644
--- a/core/src/main/java/io/squashql/query/compiled/CompiledJoin.java
+++ b/core/src/main/java/io/squashql/query/compiled/CompiledJoin.java
@@ -6,13 +6,16 @@ import io.squashql.query.dto.JoinType;
 public record CompiledJoin(NamedTable table, JoinType type, CompiledCriteria joinCriteria) {
 
   public String sqlExpression(QueryRewriter queryRewriter) {
-    StringBuilder statement = new StringBuilder();
-    statement.append(" ")
+    StringBuilder statement = new StringBuilder()
+            .append(" ")
             .append(this.type.name().toLowerCase())
             .append(" join ")
-            .append(this.table.sqlExpressionTableName(queryRewriter))
-            .append(" on ");
-    statement.append(joinCriteria().sqlExpression(queryRewriter));
+            .append(this.table.sqlExpressionTableName(queryRewriter));
+    if (joinCriteria() != null) {
+      statement
+              .append(" on ")
+              .append(joinCriteria().sqlExpression(queryRewriter));
+    }
 
     if (!this.table.joins().isEmpty()) {
       this.table.joins().forEach(j -> statement.append(j.sqlExpression(queryRewriter)));
diff --git a/core/src/main/java/io/squashql/query/dto/JoinType.java b/core/src/main/java/io/squashql/query/dto/JoinType.java
index 7df9bbf..b50d4d5 100644
--- a/core/src/main/java/io/squashql/query/dto/JoinType.java
+++ b/core/src/main/java/io/squashql/query/dto/JoinType.java
@@ -4,4 +4,5 @@ public enum JoinType {
   INNER,
   LEFT,
   FULL,
+  CROSS,
 }
diff --git a/js/typescript-library/src/query.ts b/js/typescript-library/src/query.ts
index 96c4810..d9dc2e5 100644
--- a/js/typescript-library/src/query.ts
+++ b/js/typescript-library/src/query.ts
@@ -142,6 +142,7 @@ export enum JoinType {
   INNER = "INNER",
   LEFT = "LEFT",
   FULL = "FULL",
+  CROSS = "CROSS",
 }
 
 class Join {


Test patch:
diff --git a/core/src/test/java/io/squashql/query/ATestExperimentalQueryResultMerge.java b/core/src/test/java/io/squashql/query/ATestExperimentalQueryResultMerge.java
index 919f75f..5286eb3 100644
--- a/core/src/test/java/io/squashql/query/ATestExperimentalQueryResultMerge.java
+++ b/core/src/test/java/io/squashql/query/ATestExperimentalQueryResultMerge.java
@@ -14,8 +14,7 @@ import java.util.LinkedHashMap;
 import java.util.List;
 import java.util.Map;
 
-import static io.squashql.query.Functions.criterion;
-import static io.squashql.query.Functions.sum;
+import static io.squashql.query.Functions.*;
 
 @TestClass
 @TestInstance(TestInstance.Lifecycle.PER_CLASS)
@@ -201,7 +200,7 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
     SimpleOrderDto asc = new SimpleOrderDto(OrderKeywordDto.ASC);
     Table result = this.executor.executeExperimentalQueryMerge(
             queryL, queryR, JoinType.LEFT,
-            Functions.all(criterion(this.idB, this.idA, ConditionType.EQ), criterion(this.idStoreB, this.idStoreA, ConditionType.EQ)),
+            all(criterion(this.idB, this.idA, ConditionType.EQ), criterion(this.idStoreB, this.idStoreA, ConditionType.EQ)),
             Map.of(this.idA, asc),
             -1);
     Assertions.assertThat(result.headers().stream().map(Header::name).toList())
@@ -212,6 +211,59 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
             Arrays.asList("3", "3", 4d, getDoubleNullJoinValue()));
   }
 
+  @Test
+  void testLeftJoinWithoutCriteriaWithColumnsInCommon() {
+    String firstKey = "first_key";
+    String secondKey = "second_key";
+    Field idA = this.idA.as(firstKey);
+    Field idStoreA = this.idStoreA.as(secondKey);
+    QueryDto queryL = Query
+            .from(this.storeA)
+            .select(List.of(idA, idStoreA), List.of(this.priceASum))
+            .build();
+
+    QueryDto queryR = Query
+            .from(this.storeB)
+            .select(List.of(this.idB.as(firstKey), this.idStoreB.as(secondKey)), List.of(this.priceBSum))
+            .build();
+
+    SimpleOrderDto asc = new SimpleOrderDto(OrderKeywordDto.ASC);
+    Table result = this.executor.executeExperimentalQueryMerge(
+            queryL, queryR, JoinType.LEFT,
+            null,
+            Map.of(idA, asc),
+            -1);
+    Assertions.assertThat(result.headers().stream().map(Header::name).toList())
+            .containsExactly(firstKey, secondKey, "priceA", "priceB");
+    Assertions.assertThat(result).containsExactly(
+            List.of("0", "0", 4d, 10d),
+            List.of("1", "1", 2d, 20d),
+            Arrays.asList("3", "3", 4d, getDoubleNullJoinValue()));
+  }
+
+  @Test
+  void testLeftJoinWithoutCriteriaAndNoColumnInCommon() {
+    QueryDto queryL = Query
+            .from(this.storeA)
+            .select(List.of(), List.of(this.priceASum))
+            .build();
+
+    QueryDto queryR = Query
+            .from(this.storeB)
+            .select(List.of(), List.of(this.priceBSum))
+            .build();
+
+    SimpleOrderDto asc = new SimpleOrderDto(OrderKeywordDto.ASC);
+    Table result = this.executor.executeExperimentalQueryMerge(
+            queryL, queryR, JoinType.CROSS,
+            null,
+            null,
+            -1);
+    Assertions.assertThat(result.headers().stream().map(Header::name).toList())
+            .containsExactly("priceA", "priceB");
+    Assertions.assertThat(result).containsExactly(List.of(10d, 60d));
+  }
+
   @Test
   void testWithSubQueries() {
     Field idAliasedA = this.idStoreA.as("id_aliased_a");