Repository: squashql/squashql
Pull request #340 (2024-06-03 09:24:33)

#339 Remove NULLS FIRST
Closes https://github.com/squashql/squashql/issues/339

CLAassistant: [![CLA assistant check](https://cla-assistant.io/pull/badge/not_signed)](https://cla-assistant.io/squashql/squashql?pullRequest=340) <br/>Thank you for your submission! We really appreciate it. Like many open source projects, we ask that you sign our [Contributor License Agreement](https://cla-assistant.io/squashql/squashql?pullRequest=340) before we can accept your contribution.<br/><sub>You have signed the CLA already but the status is still pending? Let us [recheck](https://cla-assistant.io/check/squashql/squashql?pullRequest=340) it.</sub>
paulbares: There is another sort performed by squashql (see the use of NullAndTotalComparator or something like that). See my comment in the issue, I don't think it is the good solution 

Patch:
diff --git a/core/src/main/java/io/squashql/query/builder/CanAddOrderBy.java b/core/src/main/java/io/squashql/query/builder/CanAddOrderBy.java
index 11474aa..8851a83 100644
--- a/core/src/main/java/io/squashql/query/builder/CanAddOrderBy.java
+++ b/core/src/main/java/io/squashql/query/builder/CanAddOrderBy.java
@@ -1,6 +1,7 @@
 package io.squashql.query.builder;
 
 import io.squashql.query.Field;
+import io.squashql.query.dto.NullsOrderDto;
 import io.squashql.query.dto.OrderKeywordDto;
 import java.util.List;
 
@@ -8,5 +9,8 @@ public interface CanAddOrderBy {
 
   HasHaving orderBy(Field column, OrderKeywordDto orderKeywordDto);
 
+  HasHaving orderBy(Field column, OrderKeywordDto orderKeywordDto, NullsOrderDto nullsOrderDto);
+
   HasHaving orderBy(Field column, List<?> firstElements);
+
 }
diff --git a/core/src/main/java/io/squashql/query/builder/Query.java b/core/src/main/java/io/squashql/query/builder/Query.java
index 008aa57..1fcfbfb 100644
--- a/core/src/main/java/io/squashql/query/builder/Query.java
+++ b/core/src/main/java/io/squashql/query/builder/Query.java
@@ -84,6 +84,12 @@ public class Query implements HasCondition, HasHaving, HasJoin, HasStartedBuildi
     return this;
   }
 
+  @Override
+  public HasHaving orderBy(Field column, OrderKeywordDto orderKeywordDto, NullsOrderDto nullsOrderDto) {
+    this.queryDto.orderBy(column, orderKeywordDto, nullsOrderDto);
+    return this;
+  }
+
   @Override
   public HasHaving orderBy(Field column, List<?> firstElements) {
     this.queryDto.orderBy(column, firstElements);
diff --git a/core/src/main/java/io/squashql/query/compiled/CompiledOrderBy.java b/core/src/main/java/io/squashql/query/compiled/CompiledOrderBy.java
index 4c67deb..67feeca 100644
--- a/core/src/main/java/io/squashql/query/compiled/CompiledOrderBy.java
+++ b/core/src/main/java/io/squashql/query/compiled/CompiledOrderBy.java
@@ -11,16 +11,14 @@ import java.util.Collections;
 import java.util.StringJoiner;
 import java.util.concurrent.atomic.AtomicInteger;
 
-/**
- * NULLS FIRST always.
- */
 public record CompiledOrderBy(TypedField field, OrderDto orderDto) {
 
   public String sqlExpression(QueryRewriter queryRewriter) {
     StringJoiner joiner = new StringJoiner(" ");
     if (this.orderDto instanceof SimpleOrderDto simpleOrder) {
       String expression = this.field.sqlExpression(queryRewriter); // TODO todo-mde should be queryRewriter.orderBy(field) because some db supports using the alias, others not.
-      joiner.add(expression).add(simpleOrder.order.name().toLowerCase()).add("nulls first");
+      joiner.add(expression).add(simpleOrder.order.name().toLowerCase());
+      joiner.add("nulls").add(((SimpleOrderDto) orderDto).nullsOrder.name().toLowerCase());
       return joiner.toString();
     } else if (this.orderDto instanceof ExplicitOrderDto explicitOrder) {
       AtomicInteger i = new AtomicInteger(0);
diff --git a/core/src/main/java/io/squashql/query/dto/NullsOrderDto.java b/core/src/main/java/io/squashql/query/dto/NullsOrderDto.java
new file mode 100644
index 0000000..82ed614
--- /dev/null
+++ b/core/src/main/java/io/squashql/query/dto/NullsOrderDto.java
@@ -0,0 +1,5 @@
+package io.squashql.query.dto;
+
+public enum NullsOrderDto {
+  FIRST, LAST
+}
diff --git a/core/src/main/java/io/squashql/query/dto/QueryDto.java b/core/src/main/java/io/squashql/query/dto/QueryDto.java
index 56548e2..b8227f8 100644
--- a/core/src/main/java/io/squashql/query/dto/QueryDto.java
+++ b/core/src/main/java/io/squashql/query/dto/QueryDto.java
@@ -116,6 +116,11 @@ public class QueryDto implements Cloneable {
     return this;
   }
 
+  public QueryDto orderBy(Field column, OrderKeywordDto orderKeywordDto, NullsOrderDto nullsOrderDto) {
+    this.orders.put(column, new SimpleOrderDto(orderKeywordDto, nullsOrderDto));
+    return this;
+  }
+
   public QueryDto orderBy(Field column, List<?> firstElements) {
     this.orders.put(column, new ExplicitOrderDto(firstElements));
     return this;
diff --git a/core/src/main/java/io/squashql/query/dto/SimpleOrderDto.java b/core/src/main/java/io/squashql/query/dto/SimpleOrderDto.java
index 3184aac..e880a14 100644
--- a/core/src/main/java/io/squashql/query/dto/SimpleOrderDto.java
+++ b/core/src/main/java/io/squashql/query/dto/SimpleOrderDto.java
@@ -1,17 +1,22 @@
 package io.squashql.query.dto;
 
+import lombok.AllArgsConstructor;
 import lombok.EqualsAndHashCode;
 import lombok.NoArgsConstructor;
 import lombok.ToString;
 
 @ToString
 @EqualsAndHashCode
+@AllArgsConstructor
 @NoArgsConstructor // For Jackson
 public final class SimpleOrderDto implements OrderDto {
 
   public OrderKeywordDto order;
+  public NullsOrderDto nullsOrder;
 
   public SimpleOrderDto(OrderKeywordDto order) {
     this.order = order;
+    this.nullsOrder = NullsOrderDto.FIRST;
   }
+
 }
diff --git a/core/src/main/java/io/squashql/util/NullAndTotalComparator.java b/core/src/main/java/io/squashql/util/NullAndTotalComparator.java
index 526a2ec..51ff1c1 100644
--- a/core/src/main/java/io/squashql/util/NullAndTotalComparator.java
+++ b/core/src/main/java/io/squashql/util/NullAndTotalComparator.java
@@ -1,5 +1,6 @@
 package io.squashql.util;
 
+import io.squashql.query.dto.NullsOrderDto;
 import java.io.Serializable;
 import java.util.Comparator;
 
@@ -9,14 +10,14 @@ import static io.squashql.query.database.SqlTranslator.TOTAL_CELL;
 
 public class NullAndTotalComparator<T> implements Comparator<T>, Serializable {
 
-  private final boolean nullFirst;
+  private final boolean nullsFirst;
   private final boolean totalFirst;
   // if null, non-null Ts are considered equal
   @SuppressWarnings("serial") // Not statically typed as Serializable
   private final Comparator<T> real;
 
-  NullAndTotalComparator(boolean nullFirst, boolean totalFirst, Comparator<? super T> real) {
-    this.nullFirst = nullFirst;
+  NullAndTotalComparator(NullsOrderDto nullsOrder, boolean totalFirst, Comparator<? super T> real) {
+    this.nullsFirst = nullsOrder == NullsOrderDto.FIRST;
     this.totalFirst = totalFirst;
     this.real = (Comparator<T>) real;
   }
@@ -24,9 +25,9 @@ public class NullAndTotalComparator<T> implements Comparator<T>, Serializable {
   @Override
   public int compare(T a, T b) {
     if (a == null) {
-      return (b == null) ? 0 : (this.nullFirst ? -1 : 1);
+      return (b == null) ? 0 : (this.nullsFirst ? -1 : 1);
     } else if (b == null) {
-      return this.nullFirst ? 1 : -1;
+      return this.nullsFirst ? 1 : -1;
     } else {
       if (isTotal(a)) {
         if (isTotal(b)) {
@@ -62,6 +63,11 @@ public class NullAndTotalComparator<T> implements Comparator<T>, Serializable {
   }
 
   public static <T> NullAndTotalComparator<T> nullsLastAndTotalsFirst(Comparator<? super T> comparator) {
-    return new NullAndTotalComparator<>(false, true, comparator);
+    return new NullAndTotalComparator<>(NullsOrderDto.LAST, true, comparator);
   }
+
+  public static <T> NullAndTotalComparator<T> totalsFirst(Comparator<? super T> comparator, NullsOrderDto nullsOrderDto) {
+    return new NullAndTotalComparator<>(nullsOrderDto, true, comparator);
+  }
+
 }
diff --git a/core/src/main/java/io/squashql/util/Queries.java b/core/src/main/java/io/squashql/util/Queries.java
index a5a2250..6ab6fb3 100644
--- a/core/src/main/java/io/squashql/util/Queries.java
+++ b/core/src/main/java/io/squashql/util/Queries.java
@@ -21,7 +21,7 @@ public final class Queries {
     Map<String, Comparator<?>> res = new LinkedHashMap<>(); // order is important !
     orders.forEach((c, order) -> {
       if (order instanceof SimpleOrderDto so) {
-        res.put(SqlUtils.squashqlExpression(c), NullAndTotalComparator.nullsLastAndTotalsFirst(so.order == DESC ? Comparator.naturalOrder().reversed() : Comparator.naturalOrder()));
+        res.put(SqlUtils.squashqlExpression(c), NullAndTotalComparator.totalsFirst(so.order == DESC ? Comparator.naturalOrder().reversed() : Comparator.naturalOrder(), ((SimpleOrderDto) order).nullsOrder));
       } else if (order instanceof ExplicitOrderDto eo) {
         res.put(SqlUtils.squashqlExpression(c), NullAndTotalComparator.nullsLastAndTotalsFirst(new CustomExplicitOrdering(eo.explicit)));
       } else {
diff --git a/js/typescript-library/src/index.ts b/js/typescript-library/src/index.ts
index b959516..795fb78 100644
--- a/js/typescript-library/src/index.ts
+++ b/js/typescript-library/src/index.ts
@@ -10,7 +10,7 @@ export {
 } from './queryJoin'
 
 export {
-  Order, OrderKeyword, SimpleOrder, ExplicitOrder
+  NullsOrderKeyword, Order, OrderKeyword, SimpleOrder, ExplicitOrder
 } from './order'
 
 export {
diff --git a/js/typescript-library/src/order.ts b/js/typescript-library/src/order.ts
index 726d1de..d736a14 100644
--- a/js/typescript-library/src/order.ts
+++ b/js/typescript-library/src/order.ts
@@ -9,16 +9,22 @@ export enum OrderKeyword {
   DESC = "DESC",
 }
 
+export enum NullsOrderKeyword {
+  FIRST = "FIRST",
+  LAST = "LAST",
+}
+
 export class SimpleOrder implements Order {
   class: string = PACKAGE + "dto.SimpleOrderDto"
 
-  constructor(private order: OrderKeyword) {
+  constructor(private order: OrderKeyword, private nullsOrder: NullsOrderKeyword = NullsOrderKeyword.FIRST) {
   }
 
   toJSON() {
     return {
       "@class": this.class,
       "order": this.order,
+      "nullsOrder": this.nullsOrder,
     }
   }
 }
diff --git a/js/typescript-library/src/query.ts b/js/typescript-library/src/query.ts
index 22804b0..dac45cc 100644
--- a/js/typescript-library/src/query.ts
+++ b/js/typescript-library/src/query.ts
@@ -1,7 +1,7 @@
 import {ColumnSet, ColumnSetKey, GroupColumnSet} from "./columnset"
 import {Field} from "./field"
 import {Measure} from "./measure"
-import {ExplicitOrder, Order, OrderKeyword, SimpleOrder} from "./order"
+import {ExplicitOrder, NullsOrderKeyword, Order, OrderKeyword, SimpleOrder} from "./order"
 import {Parameter} from "./parameter"
 import {VirtualTable} from "./virtualtable"
 import {serializeMap} from "./util"
@@ -73,8 +73,8 @@ export class Query {
     return this
   }
 
-  orderBy(column: Field, order: OrderKeyword): Query {
-    this.orders.set(column, new SimpleOrder(order))
+  orderBy(column: Field, order: OrderKeyword, nullsOrder: NullsOrderKeyword = NullsOrderKeyword.FIRST): Query {
+    this.orders.set(column, new SimpleOrder(order, nullsOrder))
     return this
   }
 
diff --git a/js/typescript-library/src/queryBuilder.ts b/js/typescript-library/src/queryBuilder.ts
index 6dcefe2..a91a62f 100644
--- a/js/typescript-library/src/queryBuilder.ts
+++ b/js/typescript-library/src/queryBuilder.ts
@@ -2,13 +2,15 @@ import {Measure} from "./measure"
 import {ColumnSet} from "./columnset"
 import {JoinType, Query, Table} from "./query"
 import Criteria from "./criteria"
-import {OrderKeyword} from "./order"
+import {NullsOrderKeyword, OrderKeyword} from "./order"
 import {VirtualTable} from "./virtualtable"
 import {Field} from "./field"
 
 export interface CanAddOrderBy {
   orderBy(column: Field, order: OrderKeyword): HasHaving
 
+  orderBy(column: Field, order: OrderKeyword, nullsOrder: NullsOrderKeyword): HasHaving
+
   orderByFirstElements(column: Field, firstElements: Array<any>): HasHaving
 }
 
@@ -131,8 +133,8 @@ class QueryBuilder implements HasCondition, HasHaving, HasJoin, HasStartedBuildi
     return this
   }
 
-  orderBy(column: Field, order: OrderKeyword): HasHaving {
-    this.queryDto.orderBy(column, order)
+  orderBy(column: Field, order: OrderKeyword, nullsOrder: NullsOrderKeyword = NullsOrderKeyword.FIRST): HasHaving {
+    this.queryDto.orderBy(column, order, nullsOrder)
     return this
   }
 
diff --git a/js/typescript-library/src/util.ts b/js/typescript-library/src/util.ts
index 9178d8d..612fe22 100644
--- a/js/typescript-library/src/util.ts
+++ b/js/typescript-library/src/util.ts
@@ -124,7 +124,7 @@ function transformToObject(key: string, value: any, reviverFallback?: (key: stri
   } else if (clazz === PACKAGE + "dto.Period$Year") {
     return new Year(transformToObject("year", value["year"]))
   } else if (clazz === PACKAGE + "dto.SimpleOrderDto") {
-    return new SimpleOrder(value["order"])
+    return new SimpleOrder(value["order"], value["nullsOrder"])
   } else if (clazz === PACKAGE + "dto.ExplicitOrderDto") {
     return new ExplicitOrder(value["explicit"])
   }


Test patch:
diff --git a/core/src/test/java/io/squashql/query/ATestQueryExecutor.java b/core/src/test/java/io/squashql/query/ATestQueryExecutor.java
index be14534..462c81c 100644
--- a/core/src/test/java/io/squashql/query/ATestQueryExecutor.java
+++ b/core/src/test/java/io/squashql/query/ATestQueryExecutor.java
@@ -626,8 +626,8 @@ public abstract class ATestQueryExecutor extends ABaseTestQuery {
             .build();
     result = this.executor.executeQuery(query);
     Assertions.assertThat(result).containsExactly(
-            Arrays.asList("biscuit", 3L),
-            Arrays.asList(null, 6L));
+            Arrays.asList(null, 6L),
+            Arrays.asList("biscuit", 3L));
   }
 
   @Test
diff --git a/core/src/test/java/io/squashql/query/TestSqlTranslator.java b/core/src/test/java/io/squashql/query/TestSqlTranslator.java
index e25cef4..9e4f129 100644
--- a/core/src/test/java/io/squashql/query/TestSqlTranslator.java
+++ b/core/src/test/java/io/squashql/query/TestSqlTranslator.java
@@ -581,6 +581,12 @@ public class TestSqlTranslator {
     sql = new CompiledOrderBy(stringField, new SimpleOrderDto(OrderKeywordDto.DESC)).sqlExpression(qr);
     assertThat(sql).isEqualTo("`dataset.store`.`field` desc nulls first");
 
+    sql = new CompiledOrderBy(stringField, new SimpleOrderDto(OrderKeywordDto.ASC, NullsOrderDto.FIRST)).sqlExpression(qr);
+    assertThat(sql).isEqualTo("`dataset.store`.`field` asc nulls first");
+
+    sql = new CompiledOrderBy(stringField, new SimpleOrderDto(OrderKeywordDto.DESC, NullsOrderDto.LAST)).sqlExpression(qr);
+    assertThat(sql).isEqualTo("`dataset.store`.`field` desc nulls last");
+
     sql = new CompiledOrderBy(intField, new ExplicitOrderDto(List.of(2023, 2027))).sqlExpression(qr);
     assertThat(sql).isEqualTo("case when `dataset.store`.`field` is null then 1 when `dataset.store`.`field` = 2023 then 2 when `dataset.store`.`field` = 2027 then 3 else 4 end");
 
diff --git a/core/src/test/java/io/squashql/util/TestNullAndTotalComparator.java b/core/src/test/java/io/squashql/util/TestNullAndTotalComparator.java
index e93962f..9affecc 100644
--- a/core/src/test/java/io/squashql/util/TestNullAndTotalComparator.java
+++ b/core/src/test/java/io/squashql/util/TestNullAndTotalComparator.java
@@ -1,5 +1,6 @@
 package io.squashql.util;
 
+import io.squashql.query.dto.NullsOrderDto;
 import org.junit.jupiter.api.Assertions;
 import org.junit.jupiter.api.Test;
 
@@ -11,7 +12,7 @@ import static io.squashql.query.database.QueryEngine.TOTAL;
 public class TestNullAndTotalComparator {
 
   @Test
-  void test() {
+  void testNullsLastAndTotalsFirst() {
     Comparator<String> comp = NullAndTotalComparator.nullsLastAndTotalsFirst(Comparator.naturalOrder());
 
     Assertions.assertEquals(-1, comp.compare("a", "b"));
@@ -29,4 +30,44 @@ public class TestNullAndTotalComparator {
     Assertions.assertEquals(-1, comp.compare(GRAND_TOTAL, TOTAL));
     Assertions.assertEquals(1, comp.compare(TOTAL, GRAND_TOTAL));
   }
+
+  @Test
+  void testTotalsFirstNullsFirstCase() {
+    Comparator<String> comp = NullAndTotalComparator.totalsFirst(Comparator.naturalOrder(), NullsOrderDto.FIRST);
+
+    Assertions.assertEquals(-1, comp.compare("a", "b"));
+    Assertions.assertEquals(1, comp.compare("b", "a"));
+    Assertions.assertEquals(-1, comp.compare(null, "a"));
+    Assertions.assertEquals(1, comp.compare("a", null));
+    Assertions.assertEquals(-1, comp.compare(GRAND_TOTAL, "a"));
+    Assertions.assertEquals(1, comp.compare("a", GRAND_TOTAL));
+    Assertions.assertEquals(-1, comp.compare(TOTAL, "a"));
+    Assertions.assertEquals(1, comp.compare("a", TOTAL));
+    Assertions.assertEquals(1, comp.compare(TOTAL, null));
+    Assertions.assertEquals(-1, comp.compare(null, TOTAL));
+    Assertions.assertEquals(0, comp.compare(GRAND_TOTAL, GRAND_TOTAL));
+    Assertions.assertEquals(0, comp.compare(TOTAL, TOTAL));
+    Assertions.assertEquals(-1, comp.compare(GRAND_TOTAL, TOTAL));
+    Assertions.assertEquals(1, comp.compare(TOTAL, GRAND_TOTAL));
+  }
+
+  @Test
+  void testTotalsFirstNullsLastCase() {
+    Comparator<String> comp = NullAndTotalComparator.totalsFirst(Comparator.naturalOrder(), NullsOrderDto.LAST);
+
+    Assertions.assertEquals(-1, comp.compare("a", "b"));
+    Assertions.assertEquals(1, comp.compare("b", "a"));
+    Assertions.assertEquals(1, comp.compare(null, "a"));
+    Assertions.assertEquals(-1, comp.compare("a", null));
+    Assertions.assertEquals(-1, comp.compare(GRAND_TOTAL, "a"));
+    Assertions.assertEquals(1, comp.compare("a", GRAND_TOTAL));
+    Assertions.assertEquals(-1, comp.compare(TOTAL, "a"));
+    Assertions.assertEquals(1, comp.compare("a", TOTAL));
+    Assertions.assertEquals(-1, comp.compare(TOTAL, null));
+    Assertions.assertEquals(1, comp.compare(null, TOTAL));
+    Assertions.assertEquals(0, comp.compare(GRAND_TOTAL, GRAND_TOTAL));
+    Assertions.assertEquals(0, comp.compare(TOTAL, TOTAL));
+    Assertions.assertEquals(-1, comp.compare(GRAND_TOTAL, TOTAL));
+    Assertions.assertEquals(1, comp.compare(TOTAL, GRAND_TOTAL));
+  }
 }
diff --git a/duckdb/src/test/java/io/squashql/TestDuckDBQuery.java b/duckdb/src/test/java/io/squashql/TestDuckDBQuery.java
index 256090e..731b38a 100644
--- a/duckdb/src/test/java/io/squashql/TestDuckDBQuery.java
+++ b/duckdb/src/test/java/io/squashql/TestDuckDBQuery.java
@@ -127,17 +127,17 @@ public class TestDuckDBQuery {
             .build();
     Table result = this.executor.executeQuery(query);
     Assertions.assertThat(result).containsExactly(
+            Arrays.asList(2020, null),  // null first by default
             List.of(2022, 7d),
-            List.of(2021, 1d),
-            Arrays.asList(2020, null));
+            List.of(2021, 1d));
 
     query.orders.clear();
     query.orderBy(new AliasedField("yoy"), OrderKeywordDto.ASC);
     result = this.executor.executeQuery(query);
     Assertions.assertThat(result).containsExactly(
+            Arrays.asList(2020, null), // null first by default
             List.of(2021, 1d),
-            List.of(2022, 7d),
-            Arrays.asList(2020, null)); // null always last
+            List.of(2022, 7d));
   }
 
   @Test
diff --git a/js/typescript-library/src/__tests__/generate-json-query-join.ts b/js/typescript-library/src/__tests__/generate-json-query-join.ts
index ed7802a..d0ff414 100644
--- a/js/typescript-library/src/__tests__/generate-json-query-join.ts
+++ b/js/typescript-library/src/__tests__/generate-json-query-join.ts
@@ -3,7 +3,7 @@ import {Field, TableField} from "../field"
 import {avg, sum} from "../measure"
 import {JoinType} from "../query"
 import {from} from "../queryBuilder"
-import {ExplicitOrder, Order, OrderKeyword, SimpleOrder} from "../order"
+import {ExplicitOrder, NullsOrderKeyword, Order, OrderKeyword, SimpleOrder} from "../order"
 import {QueryJoin} from "../queryJoin"
 import {all, ConditionType, criterion_} from "../condition"
 
@@ -27,8 +27,9 @@ export function generateFromQueryJoin() {
           .build()
 
   const orders: Map<Field, Order> = new Map()
-  orders.set(a, new SimpleOrder(OrderKeyword.ASC))
-  orders.set(c3, new SimpleOrder(OrderKeyword.DESC))
+  orders.set(a, new SimpleOrder(OrderKeyword.ASC, NullsOrderKeyword.FIRST))
+  orders.set(c2, new SimpleOrder(OrderKeyword.ASC))
+  orders.set(c3, new SimpleOrder(OrderKeyword.DESC, NullsOrderKeyword.LAST))
   orders.set(b2, new ExplicitOrder(["aa", "bb"]))
   const q = new QueryJoin(query1)
           .join(query2, JoinType.LEFT,
diff --git a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
index e5821e0..dfcdf79 100644
--- a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
+++ b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
@@ -324,8 +324,9 @@ public class TestJavascriptLibrary {
                             criterion(b1, b2, ConditionType.EQ),
                             criterion(c1, c2, ConditionType.EQ)))
             .orderBy(Map.of(
-                    a, new SimpleOrderDto(OrderKeywordDto.ASC),
-                    c3, new SimpleOrderDto(OrderKeywordDto.DESC),
+                    a, new SimpleOrderDto(OrderKeywordDto.ASC, NullsOrderDto.FIRST),
+                    c2, new SimpleOrderDto(OrderKeywordDto.ASC),
+                    c3, new SimpleOrderDto(OrderKeywordDto.DESC, NullsOrderDto.LAST),
                     b2, new ExplicitOrderDto(List.of("aa", "bb"))))
             .join(query3, JoinType.INNER)
             .limit(12);