Repository: squashql/squashql (java)

Issue:
Issue when using date in criterion
I'm trying to use a date filter in the where condition, but I got this issue `Not supported class java.time.LocalDate` in the `getQuoteFn` method in `SqlTranslator.java` file.


Pull request:
#345 Add quote for date fields
Closes #345 

Patch:
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 847a33c..c59ef6c 100644
--- a/core/src/main/java/io/squashql/query/database/SqlTranslator.java
+++ b/core/src/main/java/io/squashql/query/database/SqlTranslator.java
@@ -7,6 +7,7 @@ import io.squashql.query.compiled.CompiledOrderBy;
 import io.squashql.query.compiled.CteRecordTable;
 import io.squashql.store.UnknownType;
 
+import java.time.LocalDate;
 import java.util.ArrayList;
 import java.util.Collections;
 import java.util.List;
@@ -172,6 +173,8 @@ public class SqlTranslator {
     } else if (fieldType.equals(String.class) || fieldType.equals(Lists.StringList.class)) {
       // quote
       return s -> "'" + queryRewriter.escapeSingleQuote(String.valueOf(s)) + "'";
+    } else if (guessedClass.equals(LocalDate.class)) {
+      return date -> "'" + date.toString() + "'";
     } else {
       throw new RuntimeException("Not supported " + fieldType);
     }


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 462c81c..5dea4fa 100644
--- a/core/src/test/java/io/squashql/query/ATestQueryExecutor.java
+++ b/core/src/test/java/io/squashql/query/ATestQueryExecutor.java
@@ -8,6 +8,7 @@ import io.squashql.query.database.SqlUtils;
 import io.squashql.query.dto.*;
 import io.squashql.table.Table;
 import io.squashql.type.TableTypedField;
+import java.time.LocalDate;
 import org.assertj.core.api.Assertions;
 import org.junit.jupiter.api.Test;
 import org.junit.jupiter.api.TestInstance;
@@ -43,27 +44,28 @@ public abstract class ATestQueryExecutor extends ABaseTestQuery {
     TableTypedField price = new TableTypedField(this.storeName, "price", double.class);
     TableTypedField qty = new TableTypedField(this.storeName, "quantity", int.class);
     TableTypedField isFood = new TableTypedField(this.storeName, "isFood", boolean.class);
-    return Map.of(this.storeName, List.of(scenario, eanId, ean, category, subcategory, price, qty, isFood));
+    TableTypedField creationDate = new TableTypedField(this.storeName, "creationDate", LocalDate.class);
+    return Map.of(this.storeName, List.of(scenario, eanId, ean, category, subcategory, price, qty, isFood, creationDate));
   }
 
   @Override
   protected void loadData() {
     this.tm.load(this.storeName, List.of(
-            new Object[]{MAIN_SCENARIO_NAME, 0, "starbuck's coffee", "drink", null, 2d, 10, true}, // use a string with '
-            new Object[]{MAIN_SCENARIO_NAME, 1, "cookie", "food", "biscuit", 3d, 20, true},
-            new Object[]{MAIN_SCENARIO_NAME, 2, "shirt", "cloth", null, 10d, 3, false}
+            new Object[]{MAIN_SCENARIO_NAME, 0, "starbuck's coffee", "drink", null, 2d, 10, true, LocalDate.of(2025, 1, 14)}, // use a string with '
+            new Object[]{MAIN_SCENARIO_NAME, 1, "cookie", "food", "biscuit", 3d, 20, true, LocalDate.of(2024, 1, 14)},
+            new Object[]{MAIN_SCENARIO_NAME, 2, "shirt", "cloth", null, 10d, 3, false, LocalDate.of(2023, 1, 14)}
     ));
 
     this.tm.load(this.storeName, List.of(
-            new Object[]{"s1", 0, "starbuck's coffee", "drink", null, 4d, 10, true},
-            new Object[]{"s1", 1, "cookie", "food", "biscuit", 3d, 20, true},
-            new Object[]{"s1", 2, "shirt", "cloth", null, 10d, 3, false}
+            new Object[]{"s1", 0, "starbuck's coffee", "drink", null, 4d, 10, true, LocalDate.of(2025, 1, 14)},
+            new Object[]{"s1", 1, "cookie", "food", "biscuit", 3d, 20, true, LocalDate.of(2024, 1, 14)},
+            new Object[]{"s1", 2, "shirt", "cloth", null, 10d, 3, false, LocalDate.of(2023, 1, 14)}
     ));
 
     this.tm.load(this.storeName, List.of(
-            new Object[]{"s2", 0, "starbuck's coffee", "drink", null, 1.5d, 10, true},
-            new Object[]{"s2", 1, "cookie", "food", "biscuit", 3d, 20, true},
-            new Object[]{"s2", 2, "shirt", "cloth", null, 10d, 3, false}
+            new Object[]{"s2", 0, "starbuck's coffee", "drink", null, 1.5d, 10, true, LocalDate.of(2025, 1, 14)},
+            new Object[]{"s2", 1, "cookie", "food", "biscuit", 3d, 20, true, LocalDate.of(2024, 1, 14)},
+            new Object[]{"s2", 2, "shirt", "cloth", null, 10d, 3, false, LocalDate.of(2023, 1, 14)}
     ));
   }
 
@@ -402,6 +404,17 @@ public abstract class ATestQueryExecutor extends ABaseTestQuery {
     Assertions.assertThat(table).containsExactly(List.of("cookie"), List.of("starbuck's coffee"));
   }
 
+  @Test
+  void testDateCondition() {
+    QueryDto query = Query
+            .from(this.storeName)
+            .where(criterion("creationDate", ge(LocalDate.of(2024, 1, 1))))
+            .select(tableFields(List.of("ean")), List.of())
+            .build();
+    Table table = this.executor.executeQuery(query);
+    Assertions.assertThat(table).containsExactly(List.of("cookie"), List.of("starbuck's coffee"));
+  }
+
   @Test
   void testConditionAliasedField() {
     Field scenario = tableField(SCENARIO_FIELD_NAME).as("scenario_aliased");
diff --git a/core/src/test/java/io/squashql/query/TestSqlTranslator.java b/core/src/test/java/io/squashql/query/TestSqlTranslator.java
index 9e4f129..c9c9768 100644
--- a/core/src/test/java/io/squashql/query/TestSqlTranslator.java
+++ b/core/src/test/java/io/squashql/query/TestSqlTranslator.java
@@ -10,6 +10,7 @@ import io.squashql.store.Store;
 import io.squashql.type.AliasedTypedField;
 import io.squashql.type.TableTypedField;
 import io.squashql.type.TypedField;
+import java.time.LocalDate;
 import lombok.AllArgsConstructor;
 import org.junit.jupiter.api.Test;
 
@@ -43,7 +44,8 @@ public class TestSqlTranslator {
               new TableTypedField(BASE_STORE_NAME, "c1", String.class),
               new TableTypedField(BASE_STORE_NAME, "c2", String.class),
               new TableTypedField(BASE_STORE_NAME, "c3", String.class),
-              new TableTypedField(BASE_STORE_NAME, "scenario", String.class))));
+              new TableTypedField(BASE_STORE_NAME, "scenario", String.class),
+              new TableTypedField(BASE_STORE_NAME, "date", LocalDate.class))));
 
       String recommendation = "recommendation";
       stores.put(recommendation, new Store(recommendation, List.of(
@@ -690,4 +692,17 @@ public class TestSqlTranslator {
     sql = compileCriteria(criterion(a, eq(true))).sqlExpression(qr);
     assertThat(sql).isEqualTo("`a` = true");
   }
+
+  @Test
+  void testCriteriaWithDate() {
+    Field a = new AliasedField(BASE_STORE_NAME + ".date");
+    ConstantField cf = new ConstantField(LocalDate.of(2025, 1, 10));
+    CriteriaDto c1 = criterion(a, cf, ConditionType.EQ);
+    QueryDto q1 = Query.from(BASE_STORE_NAME)
+            .where(c1)
+            .select(List.of(a), List.of())
+            .build();
+    assertThat(translate(compileQuery(q1)))
+            .isEqualTo("select `baseStore.date` from `dataset.baseStore` where `baseStore.date` = '2025-01-10' group by `baseStore.date`");
+  }
 }