Repository: squashql/squashql
Pull request #312 (2024-04-17 08:05:48)

Refactor exp query join
It adds support of `.orderByFirstElements()` (use of `new ExplicitOrder(["aa", "bb"])`) and add more flexibility in the orderBy.


Patch:
diff --git a/core/src/main/java/io/squashql/query/QueryExecutor.java b/core/src/main/java/io/squashql/query/QueryExecutor.java
index a59cda3..0590bcc 100644
--- a/core/src/main/java/io/squashql/query/QueryExecutor.java
+++ b/core/src/main/java/io/squashql/query/QueryExecutor.java
@@ -6,7 +6,7 @@ import io.squashql.query.database.QueryEngine;
 import io.squashql.query.database.QueryScope;
 import io.squashql.query.database.SqlUtils;
 import io.squashql.query.dto.*;
-import io.squashql.query.join.ExperimentalQueryMergeExecutor;
+import io.squashql.query.join.ExperimentalQueryJoinExecutor;
 import io.squashql.query.parameter.QueryCacheParameter;
 import io.squashql.table.*;
 import io.squashql.table.PivotTableContext;
@@ -294,7 +294,7 @@ public class QueryExecutor {
   }
 
   public Table executeExperimentalQueryMerge(QueryJoinDto queryJoin) {
-    return new ExperimentalQueryMergeExecutor(this.queryEngine).execute(queryJoin);
+    return new ExperimentalQueryJoinExecutor(this.queryEngine).execute(queryJoin);
   }
 
   /**
diff --git a/core/src/main/java/io/squashql/query/database/SQLTranslator.java b/core/src/main/java/io/squashql/query/database/SQLTranslator.java
index e0ef545..d2e310b 100644
--- a/core/src/main/java/io/squashql/query/database/SQLTranslator.java
+++ b/core/src/main/java/io/squashql/query/database/SQLTranslator.java
@@ -50,7 +50,7 @@ public class SQLTranslator {
     }
     addHavingConditions(statement, query.havingCriteria(), queryRewriter);
     addOrderBy(statement, query.orderBy(), queryRewriter);
-    addLimit(query.limit(), statement);
+    addLimit(statement, query.limit());
     return statement.toString();
   }
 
@@ -62,14 +62,14 @@ public class SQLTranslator {
     statement.append(String.join(", ", cteRecordTables.stream().map(t -> t.sqlExpression(qr)).toList())).append(" ");
   }
 
-  private static void addOrderBy(StringBuilder statement, List<CompiledOrderBy> orderBy, QueryRewriter qr) {
+  public static void addOrderBy(StringBuilder statement, List<CompiledOrderBy> orderBy, QueryRewriter qr) {
     if (!orderBy.isEmpty()) {
-      statement.append(" order by ");
-      statement.append(String.join(", ", orderBy.stream().map(t -> t.sqlExpression(qr)).toList())).append(" ");
+      statement.append(" order by ")
+              .append(String.join(", ", orderBy.stream().map(t -> t.sqlExpression(qr)).toList())).append(" ");
     }
   }
 
-  public static void addLimit(int limit, StringBuilder statement) {
+  public static void addLimit(StringBuilder statement, int limit) {
     if (limit > 0) {
       statement.append(" limit ").append(limit);
     }
diff --git a/core/src/main/java/io/squashql/query/join/ExperimentalQueryMergeExecutor.java b/core/src/main/java/io/squashql/query/join/ExperimentalQueryJoinExecutor.java
similarity index 79%
rename from core/src/main/java/io/squashql/query/join/ExperimentalQueryMergeExecutor.java
rename to core/src/main/java/io/squashql/query/join/ExperimentalQueryJoinExecutor.java
index e58ae4d..af2c30a 100644
--- a/core/src/main/java/io/squashql/query/join/ExperimentalQueryMergeExecutor.java
+++ b/core/src/main/java/io/squashql/query/join/ExperimentalQueryJoinExecutor.java
@@ -2,6 +2,7 @@ package io.squashql.query.join;
 
 import io.squashql.query.*;
 import io.squashql.query.compiled.CompiledMeasure;
+import io.squashql.query.compiled.CompiledOrderBy;
 import io.squashql.query.database.*;
 import io.squashql.query.dto.*;
 import io.squashql.table.ColumnarTable;
@@ -13,7 +14,6 @@ import lombok.AllArgsConstructor;
 import lombok.extern.slf4j.Slf4j;
 import org.eclipse.collections.api.tuple.Pair;
 import org.eclipse.collections.api.tuple.Triple;
-import org.eclipse.collections.api.tuple.Twin;
 import org.eclipse.collections.impl.tuple.Tuples;
 
 import java.util.*;
@@ -25,7 +25,7 @@ import static io.squashql.query.database.SQLTranslator.addLimit;
 
 @Slf4j
 @AllArgsConstructor
-public class ExperimentalQueryMergeExecutor {
+public class ExperimentalQueryJoinExecutor {
 
   private final QueryEngine<?> queryEngine;
 
@@ -41,9 +41,9 @@ public class ExperimentalQueryMergeExecutor {
     Holder(String cteTableName, QueryDto query) {
       this.query = query;
       this.cteTableName = cteTableName;
-      this.queryResolver = new QueryResolver(query, ExperimentalQueryMergeExecutor.this.queryEngine.datastore().storeByName());
+      this.queryResolver = new QueryResolver(query, ExperimentalQueryJoinExecutor.this.queryEngine.datastore().storeByName());
       this.dbQuery = new DatabaseQuery(this.queryResolver.getScope().copyWithNewLimit(-1), new ArrayList<>(this.queryResolver.getMeasures().values()));
-      this.queryRewriter = ExperimentalQueryMergeExecutor.this.queryEngine.queryRewriter(this.dbQuery);
+      this.queryRewriter = ExperimentalQueryJoinExecutor.this.queryEngine.queryRewriter(this.dbQuery);
       this.originalTableName = query.table != null ? query.table.name : null;
       this.sql = SQLTranslator.translate(this.dbQuery, this.queryRewriter);
     }
@@ -62,89 +62,31 @@ public class ExperimentalQueryMergeExecutor {
     }
 
     // Start by setting all CTEs
-    StringBuilder sb = new StringBuilder("with ");
-    List<List<Twin<String>>> columnNamesAndNamesOrAliases = new ArrayList<>();
     Map<String, TypedField> typedFieldByFullName = new HashMap<>();
-    for (int i = 0; i < holders.size(); i++) {
-      Holder holder = holders.get(i);
-      sb.append(holder.queryRewriter.cteName(holder.cteTableName)).append(" as (").append(holder.sql);
-      sb.append(i < holders.size() - 1 ? "), " : ") ");
-
-      columnNamesAndNamesOrAliases.add(
-              holder.queryResolver.getColumns().stream().map(f -> {
-                String fieldFullName = SqlUtils.getFieldFullName(holder.queryRewriter.cteName(holder.cteTableName), holder.queryRewriter.fieldName(getFieldName(f)));
-                typedFieldByFullName.put(fieldFullName, f);
-                return Tuples.twin(fieldFullName, f.alias() != null ? holder.queryRewriter.escapeAlias(f.alias()) : fieldFullName);
-              }).toList());
-    }
-
     // Order is important here (hence LinkedHashMap). The columns from first queries will take precedence over the last queries
-    Map<String, String> fullNameByAlias = new LinkedHashMap<>();
-    for (List<Twin<String>> columnNamesOrAliases : columnNamesAndNamesOrAliases) {
-      for (Twin<String> nameOrAlias : columnNamesOrAliases) {
-        fullNameByAlias.putIfAbsent(nameOrAlias.getTwo(), nameOrAlias.getOne());
-      }
-    }
-
-    List<JoinDto> newJoins = new ArrayList<>();
-    // Iterate over the joins to rewrite the condition when necessary (to use aliases)
-    Map<Integer, Map<String, Field>> fieldBySquashQLExpression = new TreeMap<>();
-    for (Field field : queryJoin.queries.get(0).columns) {
-      TypedField typedField = holders.get(0).queryResolver.resolveField(field);
-      fieldBySquashQLExpression.computeIfAbsent(0, k -> new HashMap<>()).put(SqlUtils.squashqlExpression(typedField), field);
+    Map<String, String> fullNameByAliasOrFullName = new LinkedHashMap<>();
+    StringJoiner joiner = new StringJoiner(", ", "with ", "");
+    for (Holder holder : holders) {
+      joiner.add(holder.queryRewriter.cteName(holder.cteTableName) + " as (" + holder.sql + ")");
+
+      holder.queryResolver.getColumns().forEach(f -> {
+        String fieldFullName = SqlUtils.getFieldFullName(holder.queryRewriter.cteName(holder.cteTableName), holder.queryRewriter.fieldName(getFieldName(f)));
+        typedFieldByFullName.put(fieldFullName, f);
+        fullNameByAliasOrFullName.putIfAbsent(
+                f.alias() != null ? holder.queryRewriter.escapeAlias(f.alias()) : fieldFullName,
+                fieldFullName);
+      });
     }
 
-    int index = 1;
-    for (JoinDto join : queryJoin.table.joins) {
-      CriteriaDto joinConditionCopy = join.clone().joinCriteria;
-      if (joinConditionCopy == null) {
-        // Guess the condition
-        for (Field field : holders.get(index).query.columns) {
-          TypedField typedField = holders.get(index).queryResolver.resolveField(field);
-          fieldBySquashQLExpression.computeIfAbsent(index, k -> new HashMap<>()).put(SqlUtils.squashqlExpression(typedField), field);
-        }
-
-        List<Pair<Pair<Field, Integer>, Pair<Field, Integer>>> common = new ArrayList<>();
-        for (Map.Entry<Integer, Map<String, Field>> entry : fieldBySquashQLExpression.entrySet()) {
-          if (entry.getKey() == index) {
-            break;
-          }
-
-          for (Map.Entry<String, Field> fieldEntry : entry.getValue().entrySet()) {
-            if (fieldBySquashQLExpression.get(index).containsKey(fieldEntry.getKey())) {
-              common.add(Tuples.pair(Tuples.pair(fieldEntry.getValue(), entry.getKey()), Tuples.pair(fieldBySquashQLExpression.get(index).get(fieldEntry.getKey()), index)));
-            }
-          }
-        }
-
-        if (!common.isEmpty()) {
-          List<CriteriaDto> children = new ArrayList<>(common.size());
-          for (Pair<Pair<Field, Integer>, Pair<Field, Integer>> c : common) {
-            Field l = new TableField(holders.get(c.getOne().getTwo()).cteTableName, getFieldName(c.getOne().getOne()));
-            Field r = new TableField(holders.get(c.getTwo().getTwo()).cteTableName, getFieldName(c.getTwo().getOne()));
-            children.add(new CriteriaDto(l, r, null, null, ConditionType.EQ, Collections.emptyList()));
-          }
-          CriteriaDto criteriaDto = children.size() > 1
-                  ? new CriteriaDto(null, null, null, null, ConditionType.AND, children)
-                  : children.get(0);
-          newJoins.add(new JoinDto(join.table, join.type, criteriaDto));
-        } else {
-          newJoins.add(new JoinDto(join.table, JoinType.CROSS, null));
-        }
-      } else {
-        CriteriaDto rewrittenJoinCondition = rewriteJoinCondition(joinConditionCopy, holders);
-        newJoins.add(new JoinDto(join.table, join.type, rewrittenJoinCondition));
-      }
-      index++;
-    }
+    List<JoinDto> joinDtos = extractJoinDtos(queryJoin, holders);
 
     QueryRewriter queryRewriter = holders.get(0).queryRewriter;
     StringBuilder joinSb = new StringBuilder();
     joinSb.append(" from ").append(queryRewriter.cteName(holders.get(0).cteTableName));
     Set<String> toRemoveFromSelectSet = new HashSet<>();
-    for (int i = 0; i < newJoins.size(); i++) {
+    for (int i = 0; i < joinDtos.size(); i++) {
       Holder holder = holders.get(i + 1);
-      JoinDto jc = newJoins.get(i);
+      JoinDto jc = joinDtos.get(i);
       joinSb
               .append(" ")
               .append(jc.type.name().toLowerCase())
@@ -157,11 +99,9 @@ public class ExperimentalQueryMergeExecutor {
       }
     }
 
-    sb.append("select ");
-
     List<TypedField> selectedColumns = new ArrayList<>();
     List<String> selectSt = new ArrayList<>();
-    for (Map.Entry<String, String> e : fullNameByAlias.entrySet()) {
+    for (Map.Entry<String, String> e : fullNameByAliasOrFullName.entrySet()) {
       String alias = e.getKey();
       String fullName = e.getValue();
       if (!toRemoveFromSelectSet.contains(fullName)) {
@@ -175,34 +115,32 @@ public class ExperimentalQueryMergeExecutor {
     }
 
     // The measures
-    for (Holder holder : holders) {
-      holder.query.measures.forEach(m -> selectSt.add(holder.queryRewriter.escapeAlias(m.alias())));
-    }
-
-    sb.append(String.join(", ", selectSt));
-
-    sb.append(joinSb);
-
     List<CompiledMeasure> measures = new ArrayList<>();
     Set<String> measureAliases = new HashSet<>();
     for (Holder holder : holders) {
-      holder.query.measures.forEach(measure -> {
-        measures.add(holder.queryResolver.getMeasures().get(measure));
-        measureAliases.add(measure.alias());
+      holder.query.measures.forEach(m -> {
+        selectSt.add(holder.queryRewriter.escapeAlias(m.alias()));
+        measures.add(holder.queryResolver.getMeasures().get(m));
+        measureAliases.add(m.alias());
       });
     }
 
-    addOrderBy(queryJoin.orders, sb, queryRewriter, selectedColumns, measureAliases, holders);
-    addLimit(queryLimit, sb);
+    StringBuilder sb = new StringBuilder(joiner.toString());
+    sb
+            .append(" select ")
+            .append(String.join(", ", selectSt))
+            .append(joinSb);
+
+    addOrderBy(sb, queryJoin.orders, queryRewriter, selectedColumns, measureAliases, holders);
+    addLimit(sb, queryLimit);
 
     return Tuples.triple(sb.toString(), selectedColumns, measures);
   }
 
   public Table execute(QueryJoinDto queryJoin) {
     Triple<String, List<TypedField>, List<CompiledMeasure>> sqlGenerationResult = generateSql(queryJoin);
-    log.info("sql=" + sqlGenerationResult.getOne());
+    log.info("sql={}", sqlGenerationResult.getOne());
     Table result = this.queryEngine.executeRawSql(sqlGenerationResult.getOne());
-
     List<? extends Class<?>> columnTypes = result.headers().stream().map(Header::type).toList();
     Pair<List<Header>, List<List<Object>>> transform = transformToColumnFormat(
             sqlGenerationResult.getTwo(),
@@ -217,6 +155,61 @@ public class ExperimentalQueryMergeExecutor {
             transform.getTwo());
   }
 
+  private static List<JoinDto> extractJoinDtos(QueryJoinDto queryJoin, List<Holder> holders) {
+    List<JoinDto> newJoins = new ArrayList<>();
+    // Iterate over the joins to rewrite the condition when necessary (to use aliases)
+    Map<Integer, Map<String, Field>> fieldBySquashQLExpression = new TreeMap<>();
+    for (Field field : queryJoin.queries.get(0).columns) {
+      TypedField typedField = holders.get(0).queryResolver.resolveField(field);
+      fieldBySquashQLExpression.computeIfAbsent(0, __ -> new HashMap<>()).put(SqlUtils.squashqlExpression(typedField), field);
+    }
+
+    int index = 1;
+    for (JoinDto join : queryJoin.table.joins) {
+      CriteriaDto joinConditionCopy = join.clone().joinCriteria;
+      if (joinConditionCopy == null) {
+        // Guess the condition
+        for (Field field : holders.get(index).query.columns) {
+          TypedField typedField = holders.get(index).queryResolver.resolveField(field);
+          fieldBySquashQLExpression.computeIfAbsent(index, k -> new HashMap<>()).put(SqlUtils.squashqlExpression(typedField), field);
+        }
+
+        List<Pair<Pair<Field, Integer>, Pair<Field, Integer>>> common = new ArrayList<>();
+        for (Map.Entry<Integer, Map<String, Field>> entry : fieldBySquashQLExpression.entrySet()) {
+          if (entry.getKey() == index) {
+            break;
+          }
+
+          for (Map.Entry<String, Field> fieldEntry : entry.getValue().entrySet()) {
+            if (fieldBySquashQLExpression.get(index).containsKey(fieldEntry.getKey())) {
+              common.add(Tuples.pair(Tuples.pair(fieldEntry.getValue(), entry.getKey()), Tuples.pair(fieldBySquashQLExpression.get(index).get(fieldEntry.getKey()), index)));
+            }
+          }
+        }
+
+        if (!common.isEmpty()) {
+          List<CriteriaDto> children = new ArrayList<>(common.size());
+          for (Pair<Pair<Field, Integer>, Pair<Field, Integer>> c : common) {
+            Field l = new TableField(holders.get(c.getOne().getTwo()).cteTableName, getFieldName(c.getOne().getOne()));
+            Field r = new TableField(holders.get(c.getTwo().getTwo()).cteTableName, getFieldName(c.getTwo().getOne()));
+            children.add(new CriteriaDto(l, r, null, null, ConditionType.EQ, Collections.emptyList()));
+          }
+          CriteriaDto criteriaDto = children.size() > 1
+                  ? new CriteriaDto(null, null, null, null, ConditionType.AND, children)
+                  : children.get(0);
+          newJoins.add(new JoinDto(join.table, join.type, criteriaDto));
+        } else {
+          newJoins.add(new JoinDto(join.table, JoinType.CROSS, null));
+        }
+      } else {
+        CriteriaDto rewrittenJoinCondition = rewriteJoinCondition(joinConditionCopy, holders);
+        newJoins.add(new JoinDto(join.table, join.type, rewrittenJoinCondition));
+      }
+      index++;
+    }
+    return newJoins;
+  }
+
   /**
    * Generates the sql expression corresponding to the {@link CriteriaDto}. In addition, the set is here to collect the
    * columns from the {@link CriteriaDto} that should be removed from the select: e.g A.id = B.id, the set should contain
@@ -279,15 +272,17 @@ public class ExperimentalQueryMergeExecutor {
     }
   }
 
-  public static void addOrderBy(Map<Field, OrderDto> orders, StringBuilder sb, QueryRewriter queryRewriter,
-                                List<TypedField> selectedColumns, Set<String> measureAliases, List<Holder> holders) {
+  private static void addOrderBy(StringBuilder sb,
+                                 Map<Field, OrderDto> orders,
+                                 QueryRewriter queryRewriter,
+                                 List<TypedField> selectedColumns,
+                                 Set<String> measureAliases,
+                                 List<Holder> holders) {
+    List<CompiledOrderBy> orderBy = new ArrayList<>();
     if (orders != null && !orders.isEmpty()) {
-      sb.append(" order by ");
-      List<String> orderList = new ArrayList<>();
       for (Map.Entry<Field, OrderDto> e : orders.entrySet()) {
         Field key = e.getKey();
         TypedField typedField = null;
-        Holder h = null;
         String alias = key.alias();
         if (alias != null) {
           if (measureAliases.contains(alias)) {
@@ -296,7 +291,15 @@ public class ExperimentalQueryMergeExecutor {
             // Rely on the alias
             for (TypedField selectedColumn : selectedColumns) {
               if (alias.equals(selectedColumn.alias())) {
-                typedField = selectedColumn;
+                typedField = new AliasedTypedField(alias);
+                break;
+              }
+            }
+
+            // If not found, try to find a column with that name in all selectedColumns...
+            for (TypedField selectedColumn : selectedColumns) {
+              if (alias.equals(getFieldName(selectedColumn))) {
+                typedField = new AliasedTypedField(alias);
                 break;
               }
             }
@@ -307,17 +310,17 @@ public class ExperimentalQueryMergeExecutor {
           if (tableName != null) {
             for (Holder holder : holders) {
               if (holder.originalTableName.equals(tableName)) {
-                typedField = holder.queryResolver.resolveField(key);
-                h = holder;
+                TypedField rf = holder.queryResolver.resolveField(key);
+                typedField = new TableTypedField(holder.cteTableName, getFieldName(rf), rf.type(), true);
                 break;
               }
             }
           } else {
             // Take the first one that matches
             for (Holder holder : holders) {
-              if (holder.query.columns.stream().map(ExperimentalQueryMergeExecutor::getFieldName).collect(Collectors.toSet()).contains(getFieldName(key))) {
-                typedField = holder.queryResolver.resolveField(key);
-                h = holder;
+              if (holder.query.columns.stream().map(ExperimentalQueryJoinExecutor::getFieldName).collect(Collectors.toSet()).contains(getFieldName(key))) {
+                TypedField rf = holder.queryResolver.resolveField(key);
+                typedField = new TableTypedField(holder.cteTableName, getFieldName(rf), rf.type(), true);
                 break;
               }
             }
@@ -328,21 +331,13 @@ public class ExperimentalQueryMergeExecutor {
           throw new RuntimeException("Cannot resolve " + e.getKey());
         }
 
-
-        String orderByField = h == null ? queryRewriter.aliasOrFullExpression(typedField)
-                : SqlUtils.getFieldFullName(h.queryRewriter.cteName(h.cteTableName), h.queryRewriter.fieldName(getFieldName(typedField)));
-        OrderDto orderDto = e.getValue();
-        if (orderDto instanceof SimpleOrderDto sod) {
-          orderList.add(orderByField + " " + sod.order.name() + " nulls last");
-        } else {
-          throw new IllegalArgumentException("only ASC or DESC ordering is supporting");
-        }
+        orderBy.add(new CompiledOrderBy(typedField, e.getValue()));
       }
-      sb.append(String.join(", ", orderList));
     }
+    SQLTranslator.addOrderBy(sb, orderBy, queryRewriter);
   }
 
-  static CriteriaDto rewriteJoinCondition(CriteriaDto joinCondition, List<Holder> holders) {
+  private static CriteriaDto rewriteJoinCondition(CriteriaDto joinCondition, List<Holder> holders) {
     Map<String, String> cteByOriginalTableName = new HashMap<>();
     for (Holder holder : holders) {
       cteByOriginalTableName.put(holder.originalTableName, holder.cteTableName);
@@ -392,7 +387,7 @@ public class ExperimentalQueryMergeExecutor {
     return null;
   }
 
-  static String getFieldName(Field field) {
+  private static String getFieldName(Field field) {
     String alias = field.alias();
     if (alias != null) {
       return alias;
@@ -403,7 +398,7 @@ public class ExperimentalQueryMergeExecutor {
     }
   }
 
-  static String getFieldName(TypedField field) {
+  private static String getFieldName(TypedField field) {
     String alias = field.alias();
     if (alias != null) {
       return alias;
diff --git a/js/typescript-library/src/generate-json-query-join.ts b/js/typescript-library/src/generate-json-query-join.ts
index 73bfe09..136fd04 100644
--- a/js/typescript-library/src/generate-json-query-join.ts
+++ b/js/typescript-library/src/generate-json-query-join.ts
@@ -1,9 +1,9 @@
 import * as fs from "fs"
-import {TableField} from "./field"
+import {Field, TableField} from "./field"
 import {avg, sum} from "./measure"
 import {JoinType} from "./query"
 import {from} from "./queryBuilder"
-import {OrderKeyword, SimpleOrder} from "./order"
+import {ExplicitOrder, Order, OrderKeyword, SimpleOrder} from "./order"
 import {QueryJoin} from "./queryJoin"
 import {all, ConditionType, criterion_} from "./condition"
 
@@ -26,7 +26,10 @@ export function generateFromQueryJoin() {
           .select([c3], [], [avg("max", new TableField("f3"))])
           .build()
 
-  const orders = new Map([[a, new SimpleOrder(OrderKeyword.ASC)], [c3, new SimpleOrder(OrderKeyword.DESC)]])
+  const orders: Map<Field, Order> = new Map()
+  orders.set(a, new SimpleOrder(OrderKeyword.ASC))
+  orders.set(c3, new SimpleOrder(OrderKeyword.DESC))
+  orders.set(b2, new ExplicitOrder(["aa", "bb"]))
   const q = new QueryJoin(query1)
           .join(query2, JoinType.LEFT,
                   all([


Test patch:
diff --git a/bigquery/src/test/java/io/squashql/query/TestBigQueryExperimentalQueryResultMerge.java b/bigquery/src/test/java/io/squashql/query/TestBigQueryExperimentalQueryJoinExecutor.java
similarity index 94%
rename from bigquery/src/test/java/io/squashql/query/TestBigQueryExperimentalQueryResultMerge.java
rename to bigquery/src/test/java/io/squashql/query/TestBigQueryExperimentalQueryJoinExecutor.java
index 0dc98c2..ec2176e 100644
--- a/bigquery/src/test/java/io/squashql/query/TestBigQueryExperimentalQueryResultMerge.java
+++ b/bigquery/src/test/java/io/squashql/query/TestBigQueryExperimentalQueryJoinExecutor.java
@@ -15,7 +15,7 @@ import static io.squashql.query.BigQueryTestUtil.PROJECT_ID;
 /**
  * Do not edit this class, it has been generated automatically by {@link io.squashql.template.BigQueryClassTemplateGenerator}.
  */
-public class TestBigQueryExperimentalQueryResultMerge extends ATestExperimentalQueryResultMerge {
+public class TestBigQueryExperimentalQueryJoinExecutor extends ATestExperimentalQueryJoinExecutor {
 
   @AfterAll
   void tearDown() {
diff --git a/clickhouse/src/test/java/io/squashql/query/TestClickHouseExperimentalQueryResultMerge.java b/clickhouse/src/test/java/io/squashql/query/TestClickHouseExperimentalQueryJoinExecutor.java
similarity index 94%
rename from clickhouse/src/test/java/io/squashql/query/TestClickHouseExperimentalQueryResultMerge.java
rename to clickhouse/src/test/java/io/squashql/query/TestClickHouseExperimentalQueryJoinExecutor.java
index 0c9df91..62a4dd9 100644
--- a/clickhouse/src/test/java/io/squashql/query/TestClickHouseExperimentalQueryResultMerge.java
+++ b/clickhouse/src/test/java/io/squashql/query/TestClickHouseExperimentalQueryJoinExecutor.java
@@ -13,7 +13,7 @@ import org.junit.jupiter.api.BeforeAll;
 /**
  * Do not edit this class, it has been generated automatically by {@link ClickHouseClassTemplateGenerator}.
  */
-public class TestClickHouseExperimentalQueryResultMerge extends ATestExperimentalQueryResultMerge {
+public class TestClickHouseExperimentalQueryJoinExecutor extends ATestExperimentalQueryJoinExecutor {
 
   public org.testcontainers.containers.GenericContainer container = ClickHouseTestUtil.createClickHouseContainer();
 
diff --git a/core/src/test/java/io/squashql/join/TestExperimentalQueryMergeExecutor.java b/core/src/test/java/io/squashql/join/TestSqlExperimentalQueryJoinExecutor.java
similarity index 95%
rename from core/src/test/java/io/squashql/join/TestExperimentalQueryMergeExecutor.java
rename to core/src/test/java/io/squashql/join/TestSqlExperimentalQueryJoinExecutor.java
index 0d17acd..fd88235 100644
--- a/core/src/test/java/io/squashql/join/TestExperimentalQueryMergeExecutor.java
+++ b/core/src/test/java/io/squashql/join/TestSqlExperimentalQueryJoinExecutor.java
@@ -1,5 +1,6 @@
 package io.squashql.join;
 
+import io.squashql.query.ATestExperimentalQueryJoinExecutor;
 import io.squashql.query.TableField;
 import io.squashql.query.builder.Query;
 import io.squashql.query.compiled.CompiledMeasure;
@@ -7,7 +8,7 @@ import io.squashql.query.database.*;
 import io.squashql.query.dto.JoinType;
 import io.squashql.query.dto.QueryDto;
 import io.squashql.query.dto.QueryJoinDto;
-import io.squashql.query.join.ExperimentalQueryMergeExecutor;
+import io.squashql.query.join.ExperimentalQueryJoinExecutor;
 import io.squashql.store.Datastore;
 import io.squashql.store.Store;
 import io.squashql.table.Table;
@@ -29,9 +30,9 @@ import static io.squashql.query.Functions.criterion;
 import static io.squashql.query.dto.ConditionType.EQ;
 
 /**
- * Test sql generation, not the execution. See {@link io.squashql.query.ATestExperimentalQueryResultMerge}.
+ * Test sql generation, not the execution. See {@link ATestExperimentalQueryJoinExecutor}.
  */
-public class TestExperimentalQueryMergeExecutor {
+public class TestSqlExperimentalQueryJoinExecutor {
 
   static final String storeA = "A";
   static final String storeB = "B";
@@ -51,7 +52,7 @@ public class TestExperimentalQueryMergeExecutor {
   static final TableField C_d = new TableField(storeC, "d");
   static final TableField C_g = new TableField(storeC, "g");
 
-  static final ExperimentalQueryMergeExecutor ex = new ExperimentalQueryMergeExecutor(new FakeQueryEngine());
+  static final ExperimentalQueryJoinExecutor ex = new ExperimentalQueryJoinExecutor(new FakeQueryEngine());
 
   static class FakeQueryEngine implements QueryEngine<Datastore> {
 
@@ -70,7 +71,7 @@ public class TestExperimentalQueryMergeExecutor {
       return () -> {
         Map<String, Store> map = new HashMap<>();
         try {
-          java.lang.reflect.Field[] fields = TestExperimentalQueryMergeExecutor.class.getDeclaredFields();
+          java.lang.reflect.Field[] fields = TestSqlExperimentalQueryJoinExecutor.class.getDeclaredFields();
           for (java.lang.reflect.Field field : fields) {
             Class<?> type = field.getType();
             if (type.equals(TableField.class)) {
diff --git a/core/src/test/java/io/squashql/query/ATestExperimentalQueryResultMerge.java b/core/src/test/java/io/squashql/query/ATestExperimentalQueryJoinExecutor.java
similarity index 88%
rename from core/src/test/java/io/squashql/query/ATestExperimentalQueryResultMerge.java
rename to core/src/test/java/io/squashql/query/ATestExperimentalQueryJoinExecutor.java
index 8ee0321..1d682b3 100644
--- a/core/src/test/java/io/squashql/query/ATestExperimentalQueryResultMerge.java
+++ b/core/src/test/java/io/squashql/query/ATestExperimentalQueryJoinExecutor.java
@@ -3,7 +3,6 @@ package io.squashql.query;
 import io.squashql.TestClass;
 import io.squashql.query.builder.Query;
 import io.squashql.query.dto.*;
-import io.squashql.query.dto.QueryJoinDto;
 import io.squashql.table.Table;
 import io.squashql.type.TableTypedField;
 import org.assertj.core.api.Assertions;
@@ -20,7 +19,7 @@ import static io.squashql.query.dto.OrderKeywordDto.DESC;
 
 @TestClass
 @TestInstance(TestInstance.Lifecycle.PER_CLASS)
-public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
+public abstract class ATestExperimentalQueryJoinExecutor extends ABaseTestQuery {
 
   String storeA = "StoreA" + getClass().getSimpleName().toLowerCase();
   String storeB = "StoreB" + getClass().getSimpleName().toLowerCase();
@@ -394,6 +393,59 @@ public abstract class ATestExperimentalQueryResultMerge extends ABaseTestQuery {
             Arrays.asList("3", 4d, getDoubleNullJoinValue()));
   }
 
+  @Test
+  void testOrderByFirstElements() {
+    QueryDto queryL = Query
+            .from(this.storeA)
+            .select(List.of(this.idA), List.of(this.priceASum))
+            .build();
+
+    QueryDto queryR = Query
+            .from(this.storeB)
+            .select(List.of(this.idB), List.of(this.priceBSum))
+            .build();
+
+    Map<Field, OrderDto> orders = new LinkedHashMap<>();
+    orders.put(this.idA, new ExplicitOrderDto(List.of("3", "0", "1")));
+    QueryJoinDto jq = QueryJoinDto.from(queryL).join(queryR, JoinType.LEFT, criterion(this.idB, this.idA, ConditionType.EQ))
+            .orderBy(orders)
+            .limit(-1);
+    Table result = this.executor.executeExperimentalQueryMerge(jq);
+    Assertions.assertThat(result.headers().stream().map(Header::name).toList())
+            .containsExactly(this.storeA + ".idA", "priceA", "priceB");
+    Assertions.assertThat(result).containsExactly(
+            Arrays.asList("3", 4d, getDoubleNullJoinValue()),
+            List.of("0", 4d, 10d),
+            List.of("1", 2d, 20d));
+  }
+
+  @Test
+  void testOrderByAliasedField() {
+    QueryDto queryL = Query
+            .from(this.storeA)
+            .select(List.of(this.idA), List.of(this.priceASum))
+            .build();
+
+    QueryDto queryR = Query
+            .from(this.storeB)
+            .select(List.of(this.idB), List.of(this.priceBSum))
+            .build();
+
+    Map<Field, OrderDto> orders = new LinkedHashMap<>();
+    // This orderBy can work even if we do not really know where the "idA" comes from...
+    orders.put(new AliasedField(((TableField) this.idA).fieldName), new SimpleOrderDto(DESC));
+    QueryJoinDto jq = QueryJoinDto.from(queryL).join(queryR, JoinType.LEFT, criterion(this.idB, this.idA, ConditionType.EQ))
+            .orderBy(orders)
+            .limit(-1);
+    Table result = this.executor.executeExperimentalQueryMerge(jq);
+    Assertions.assertThat(result.headers().stream().map(Header::name).toList())
+            .containsExactly(this.storeA + ".idA", "priceA", "priceB");
+    Assertions.assertThat(result).containsExactly(
+            Arrays.asList("3", 4d, getDoubleNullJoinValue()),
+            List.of("1", 2d, 20d),
+            List.of("0", 4d, 10d));
+  }
+
   /**
    * Returns the value of a double when the join fails.
    */
diff --git a/duckdb/src/test/java/io/squashql/query/TestDuckDBExperimentalQueryResultMerge.java b/duckdb/src/test/java/io/squashql/query/TestDuckDBExperimentalQueryJoinExecutor.java
similarity index 90%
rename from duckdb/src/test/java/io/squashql/query/TestDuckDBExperimentalQueryResultMerge.java
rename to duckdb/src/test/java/io/squashql/query/TestDuckDBExperimentalQueryJoinExecutor.java
index caa5ff7..09dd073 100644
--- a/duckdb/src/test/java/io/squashql/query/TestDuckDBExperimentalQueryResultMerge.java
+++ b/duckdb/src/test/java/io/squashql/query/TestDuckDBExperimentalQueryJoinExecutor.java
@@ -10,7 +10,7 @@ import io.squashql.transaction.DuckDBDataLoader;
 /**
  * Do not edit this class, it has been generated automatically by {@link io.squashql.template.DuckDBClassTemplateGenerator}.
  */
-public class TestDuckDBExperimentalQueryResultMerge extends ATestExperimentalQueryResultMerge {
+public class TestDuckDBExperimentalQueryJoinExecutor extends ATestExperimentalQueryJoinExecutor {
 
   @Override
   protected QueryEngine createQueryEngine(Datastore datastore) {
diff --git a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
index 538e586..519a449 100644
--- a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
+++ b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
@@ -321,7 +321,10 @@ public class TestJavascriptLibrary {
                     all(
                             criterion(b1, b2, ConditionType.EQ),
                             criterion(c1, c2, ConditionType.EQ)))
-            .orderBy(Map.of(a, new SimpleOrderDto(OrderKeywordDto.ASC), c3, new SimpleOrderDto(OrderKeywordDto.DESC)))
+            .orderBy(Map.of(
+                    a, new SimpleOrderDto(OrderKeywordDto.ASC),
+                    c3, new SimpleOrderDto(OrderKeywordDto.DESC),
+                    b2, new ExplicitOrderDto(List.of("aa", "bb"))))
             .join(query3, JoinType.INNER)
             .limit(12);
 
diff --git a/snowflake/src/test/java/io/squashql/query/TestSnowflakeExperimentalQueryResultMerge.java b/snowflake/src/test/java/io/squashql/query/TestSnowflakeExperimentalQueryJoinExecutor.java
similarity index 93%
rename from snowflake/src/test/java/io/squashql/query/TestSnowflakeExperimentalQueryResultMerge.java
rename to snowflake/src/test/java/io/squashql/query/TestSnowflakeExperimentalQueryJoinExecutor.java
index 9cd2423..5210485 100644
--- a/snowflake/src/test/java/io/squashql/query/TestSnowflakeExperimentalQueryResultMerge.java
+++ b/snowflake/src/test/java/io/squashql/query/TestSnowflakeExperimentalQueryJoinExecutor.java
@@ -11,7 +11,7 @@ import org.junit.jupiter.api.AfterAll;
 /**
  * Do not edit this class, it has been generated automatically by {@link io.squashql.template.SnowflakeClassTemplateGenerator}.
  */
-public class TestSnowflakeExperimentalQueryResultMerge extends ATestExperimentalQueryResultMerge {
+public class TestSnowflakeExperimentalQueryJoinExecutor extends ATestExperimentalQueryJoinExecutor {
 
   @AfterAll
   void tearDown() {
diff --git a/spark/src/test/java/io/squashql/query/TestSparkExperimentalQueryResultMerge.java b/spark/src/test/java/io/squashql/query/TestSparkExperimentalQueryJoinExecutor.java
similarity index 92%
rename from spark/src/test/java/io/squashql/query/TestSparkExperimentalQueryResultMerge.java
rename to spark/src/test/java/io/squashql/query/TestSparkExperimentalQueryJoinExecutor.java
index bc0d0aa..e6846c2 100644
--- a/spark/src/test/java/io/squashql/query/TestSparkExperimentalQueryResultMerge.java
+++ b/spark/src/test/java/io/squashql/query/TestSparkExperimentalQueryJoinExecutor.java
@@ -11,7 +11,7 @@ import org.junit.jupiter.api.AfterAll;
 /**
  * Do not edit this class, it has been generated automatically by {@link io.squashql.template.SparkClassTemplateGenerator}.
  */
-public class TestSparkExperimentalQueryResultMerge extends ATestExperimentalQueryResultMerge {
+public class TestSparkExperimentalQueryJoinExecutor extends ATestExperimentalQueryJoinExecutor {
 
   @AfterAll
   void tearDown() {