Repository: squashql/squashql
Pull request #308 (2024-04-15 09:35:33)

Support more sql functions
- upper/ucase
- lower/lcase
- current_date()


Patch:
diff --git a/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java b/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java
index 8a7c78c..f72078b 100644
--- a/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java
+++ b/bigquery/src/main/java/io/squashql/query/database/BigQueryQueryRewriter.java
@@ -1,7 +1,6 @@
 package io.squashql.query.database;
 
 import io.squashql.query.BinaryOperator;
-import io.squashql.query.date.DateFunctions;
 import io.squashql.type.FunctionTypedField;
 import io.squashql.type.TypedField;
 import lombok.AllArgsConstructor;
@@ -14,11 +13,11 @@ public class BigQueryQueryRewriter implements QueryRewriter {
 
   @Override
   public String functionExpression(FunctionTypedField ftf) {
-    if (DateFunctions.SUPPORTED_DATE_FUNCTIONS.contains(ftf.function())) {
+    if (SQLFunctions.SUPPORTED_DATE_FUNCTIONS.contains(ftf.function())) {
       // https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#extract
       return String.format("EXTRACT(%s FROM %s)", ftf.function(), ftf.field().sqlExpression(this));
     } else {
-      throw new IllegalArgumentException("Unsupported function " + ftf);
+      return QueryRewriter.super.functionExpression(ftf);
     }
   }
 
diff --git a/core/src/main/java/io/squashql/query/FunctionField.java b/core/src/main/java/io/squashql/query/FunctionField.java
index a409d42..7790d14 100644
--- a/core/src/main/java/io/squashql/query/FunctionField.java
+++ b/core/src/main/java/io/squashql/query/FunctionField.java
@@ -11,18 +11,22 @@ import lombok.ToString;
 @AllArgsConstructor
 public class FunctionField implements Field {
 
-  public String function;
-  public Field field;
+  public String functionName;
+  public Field operand;
   public String alias;
 
-  public FunctionField(String function, Field field) {
-    this.function = function;
-    this.field = field;
+  public FunctionField(String functionName) {
+    this(functionName, null);
+  }
+
+  public FunctionField(String functionName, Field operand) {
+    this.functionName = functionName;
+    this.operand = operand;
   }
 
   @Override
   public Field as(String alias) {
-    return new FunctionField(this.function, this.field, alias);
+    return new FunctionField(this.functionName, this.operand, alias);
   }
 
   @Override
diff --git a/core/src/main/java/io/squashql/query/Functions.java b/core/src/main/java/io/squashql/query/Functions.java
index 8900772..072a1e5 100644
--- a/core/src/main/java/io/squashql/query/Functions.java
+++ b/core/src/main/java/io/squashql/query/Functions.java
@@ -234,6 +234,9 @@ public class Functions {
     return new DoubleConstantMeasure(value);
   }
 
+
+  // FUNCTIONS
+
   public static Field year(String field) {
     return new FunctionField("YEAR", new TableField(field));
   }
@@ -246,6 +249,20 @@ public class Functions {
     return new FunctionField("MONTH", new TableField(field));
   }
 
+  public static Field lower(Field field) {
+    return new FunctionField("lower", field);
+  }
+
+  public static Field upper(Field field) {
+    return new FunctionField("upper", field);
+  }
+
+  public static Field currentDate() {
+    return new FunctionField("current_date");
+  }
+
+  // COMPARISONS
+
   public static Measure comparisonMeasureWithParentOfAxis(String alias,
                                                           ComparisonMethod comparisonMethod,
                                                           Measure underlyingMeasure,
diff --git a/core/src/main/java/io/squashql/query/QueryResolver.java b/core/src/main/java/io/squashql/query/QueryResolver.java
index e400977..21147ac 100644
--- a/core/src/main/java/io/squashql/query/QueryResolver.java
+++ b/core/src/main/java/io/squashql/query/QueryResolver.java
@@ -99,7 +99,7 @@ public class QueryResolver {
       if (f instanceof TableField tf) {
         return getTableTypedField(tf.fullName, field.alias());
       } else if (f instanceof FunctionField ff) {
-        return new FunctionTypedField(resolveField(ff.field), ff.function, ff.alias);
+        return new FunctionTypedField(ff.operand != null ? resolveField(ff.operand) : null, ff.functionName, ff.alias);
       } else if (f instanceof BinaryOperationField ff) {
         return new BinaryOperationTypedField(ff.operator, resolveField(ff.leftOperand), resolveField(ff.rightOperand), ff.alias);
       } else if (f instanceof ConstantField ff) {
diff --git a/core/src/main/java/io/squashql/query/compiled/CompiledCriteria.java b/core/src/main/java/io/squashql/query/compiled/CompiledCriteria.java
index 61b4ce5..7a74dde 100644
--- a/core/src/main/java/io/squashql/query/compiled/CompiledCriteria.java
+++ b/core/src/main/java/io/squashql/query/compiled/CompiledCriteria.java
@@ -48,7 +48,7 @@ public record CompiledCriteria(ConditionDto condition, ConditionType conditionTy
   public static String toSql(TypedField field, ConditionDto dto, QueryRewriter queryRewriter) {
     String expression = field.sqlExpression(queryRewriter); // FIXME should be queryRewriter.where(field) because some db supports using the alias, others not.
     if (dto instanceof SingleValueConditionDto || dto instanceof InConditionDto) {
-      Function<Object, String> sqlMapper = field instanceof TableTypedField ? SQLTranslator.getQuoteFn(field, queryRewriter) : String::valueOf; // FIXME dirty workaround
+      Function<Object, String> sqlMapper = SQLTranslator.getQuoteFn(field, queryRewriter);
       return switch (dto.type()) {
         case IN -> expression + " " + dto.type().sqlInfix + " (" +
                 ((InConditionDto) dto).values
diff --git a/core/src/main/java/io/squashql/query/database/QueryRewriter.java b/core/src/main/java/io/squashql/query/database/QueryRewriter.java
index a569ec1..59a0aaa 100644
--- a/core/src/main/java/io/squashql/query/database/QueryRewriter.java
+++ b/core/src/main/java/io/squashql/query/database/QueryRewriter.java
@@ -27,7 +27,14 @@ public interface QueryRewriter {
   }
 
   default String functionExpression(FunctionTypedField ftf) {
-    return ftf.function() + "(" + ftf.field().sqlExpression(this) + ")";
+    StringBuilder sb = new StringBuilder(ftf.function());
+    sb.append("(");
+    TypedField field = ftf.field();
+    if (field != null) {
+      sb.append(field.sqlExpression(this));
+    }
+    sb.append(")");
+    return sb.toString();
   }
 
   /**
diff --git a/core/src/main/java/io/squashql/query/database/SQLFunctions.java b/core/src/main/java/io/squashql/query/database/SQLFunctions.java
new file mode 100644
index 0000000..8c89519
--- /dev/null
+++ b/core/src/main/java/io/squashql/query/database/SQLFunctions.java
@@ -0,0 +1,12 @@
+package io.squashql.query.database;
+
+import java.util.Set;
+
+public final class SQLFunctions {
+
+  public static final Set<String> SUPPORTED_DATE_FUNCTIONS = Set.of("YEAR", "QUARTER", "MONTH");
+  public static final Set<String> SUPPORTED_STRING_FUNCTIONS = Set.of("lower", "upper");
+
+  private SQLFunctions() {
+  }
+}
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 dfceb7c..628d508 100644
--- a/core/src/main/java/io/squashql/query/database/SQLTranslator.java
+++ b/core/src/main/java/io/squashql/query/database/SQLTranslator.java
@@ -158,22 +158,23 @@ public class SQLTranslator {
   }
 
   public static Function<Object, String> getQuoteFn(TypedField field, QueryRewriter queryRewriter) {
-    if (Number.class.isAssignableFrom(field.type())
-            || field.type().equals(double.class)
-            || field.type().equals(int.class)
-            || field.type().equals(long.class)
-            || field.type().equals(float.class)
-            || field.type().equals(boolean.class)
-            || field.type().equals(Boolean.class)
-            || field.type().equals(UnknownType.class)
-            || field.type().equals(Lists.LongList.class)) {
+    Class<?> fType = field.type();
+    if (Number.class.isAssignableFrom(fType)
+            || fType.equals(double.class)
+            || fType.equals(int.class)
+            || fType.equals(long.class)
+            || fType.equals(float.class)
+            || fType.equals(boolean.class)
+            || fType.equals(Boolean.class)
+            || fType.equals(UnknownType.class)
+            || fType.equals(Lists.LongList.class)) {
       // no quote
       return String::valueOf;
-    } else if (field.type().equals(String.class) || field.type().equals(Lists.StringList.class)) {
+    } else if (fType.equals(String.class) || fType.equals(Lists.StringList.class)) {
       // quote
       return s -> "'" + queryRewriter.escapeSingleQuote(String.valueOf(s)) + "'";
     } else {
-      throw new RuntimeException("Not supported " + field.type());
+      throw new RuntimeException("Not supported " + fType);
     }
   }
 
diff --git a/core/src/main/java/io/squashql/query/database/SqlUtils.java b/core/src/main/java/io/squashql/query/database/SqlUtils.java
index 8daae79..ee0a7ed 100644
--- a/core/src/main/java/io/squashql/query/database/SqlUtils.java
+++ b/core/src/main/java/io/squashql/query/database/SqlUtils.java
@@ -48,7 +48,7 @@ public class SqlUtils {
     if (f instanceof TableTypedField ttf) {
       return getFieldFullName(ttf);
     } else if (f instanceof FunctionTypedField ftf) {
-      return singleOperandFunctionName(ftf.function(), squashqlExpression(ftf.field()));
+      return singleOperandFunctionName(ftf.function(), ftf.field() != null ? squashqlExpression(ftf.field()) : "");
     } else {
       throw new IllegalArgumentException(f.getClass().getName());
     }
@@ -62,7 +62,7 @@ public class SqlUtils {
     if (f instanceof TableField tf) {
       return getFieldFullName(tf.tableName, tf.fieldName);
     } else if (f instanceof FunctionField ftf) {
-      return singleOperandFunctionName(ftf.function, squashqlExpression(ftf.field));
+      return singleOperandFunctionName(ftf.functionName, ftf.operand != null ? squashqlExpression(ftf.operand) : "");
     } else {
       throw new IllegalArgumentException(f.getClass().getName());
     }
diff --git a/core/src/main/java/io/squashql/query/date/DateFunctions.java b/core/src/main/java/io/squashql/query/date/DateFunctions.java
deleted file mode 100644
index 49d9a5d..0000000
--- a/core/src/main/java/io/squashql/query/date/DateFunctions.java
+++ /dev/null
@@ -1,8 +0,0 @@
-package io.squashql.query.date;
-
-import java.util.Set;
-
-public class DateFunctions {
-
-  public static final Set<String> SUPPORTED_DATE_FUNCTIONS = Set.of("YEAR", "QUARTER", "MONTH");
-}
diff --git a/core/src/main/java/io/squashql/type/FunctionTypedField.java b/core/src/main/java/io/squashql/type/FunctionTypedField.java
index 4b7e993..f75fb48 100644
--- a/core/src/main/java/io/squashql/type/FunctionTypedField.java
+++ b/core/src/main/java/io/squashql/type/FunctionTypedField.java
@@ -3,7 +3,8 @@ package io.squashql.type;
 import io.squashql.query.database.QueryRewriter;
 import io.squashql.store.UnknownType;
 
-import static io.squashql.query.date.DateFunctions.SUPPORTED_DATE_FUNCTIONS;
+import static io.squashql.query.database.SQLFunctions.SUPPORTED_DATE_FUNCTIONS;
+import static io.squashql.query.database.SQLFunctions.SUPPORTED_STRING_FUNCTIONS;
 
 public record FunctionTypedField(TypedField field, String function, String alias) implements TypedField {
 
@@ -16,6 +17,8 @@ public record FunctionTypedField(TypedField field, String function, String alias
   public Class<?> type() {
     if (SUPPORTED_DATE_FUNCTIONS.contains(this.function)) {
       return int.class;
+    } else if (SUPPORTED_STRING_FUNCTIONS.contains(this.function)) {
+      return String.class;
     } else {
       return UnknownType.class;
     }
diff --git a/documentation/QUERY.md b/documentation/QUERY.md
index e8397b6..5fb1dc4 100644
--- a/documentation/QUERY.md
+++ b/documentation/QUERY.md
@@ -544,6 +544,7 @@ The interface `Field` can be used to represent either:
 - A column/field of a table: `TableField` e.g. `const a = new TableField("myTable.a")`
 - A constant value: `ConstantField` e.g. `const one = new ConstantField(1)`
 - An aliased column: `AliasedField` e.g. `const aliased = new AliasedField("myColumn")`
+- An SQL function with a single operand: `FunctionField` e.g. `const func = new FunctionField("lower", new TableField("myTable.a"))`
 
 Fields can be combined to produce other fields to be used in calculations and perform more complex computations.
 
@@ -600,6 +601,29 @@ const query = fromSubQuery(subQuery)
         .build()
 ```
 
+`FunctionField` is made to apply a transformation to a column. Only functions that take a single argument are supported 
+for the time being. The library provides helpers to create useful functions. For instance with `const a = new TableField("myTable.a")`
+
+```typescript
+const a = new TableField("myTable.a")
+const query = from("myTable")
+        .select([lower(a), upper(a), currentDate()], [], [countRows])
+        .build()
+```
+
+```sql
+SELECT lower(myTable.a), upper(myTable.a), current_date() AS id, count(*) FROM myTable
+```
+
+SQL functions:
+
+| function name | Description                  | Example        | Result     | Alias |
+|---------------|------------------------------|----------------|------------|-------|
+| upper         | Convert string to upper case | upper('Hello') | HELLO      | ucase | 
+| lower         | Convert string to lower case | lower('Hello') | hello      | lcase | 
+| current_date  | Current date                 | current_date() | 2024-10-25 |       | 
+
+
 ## Measures
 
 A Measure represents aggregated values and is usually numeric. Measure can be split into two categories depending on 
diff --git a/js/typescript-library/src/field.ts b/js/typescript-library/src/field.ts
index 565ae07..bfffbd3 100644
--- a/js/typescript-library/src/field.ts
+++ b/js/typescript-library/src/field.ts
@@ -113,6 +113,27 @@ export class TableField extends AField {
   }
 }
 
+export class FunctionField extends AField {
+  readonly class: string = PACKAGE + "FunctionField"
+
+  constructor(readonly functionName: string, readonly operand?: Field, readonly alias?: string) {
+    super()
+  }
+
+  as(alias: string): Field {
+    return new FunctionField(this.functionName, this.operand, alias)
+  }
+
+  toJSON() {
+    return {
+      "@class": this.class,
+      "functionName": this.functionName,
+      "operand": this.operand,
+      "alias": this.alias,
+    }
+  }
+}
+
 export function tableField(fullName: string) {
   return new TableField(fullName)
 }
@@ -142,3 +163,17 @@ export class BinaryOperationField extends AField {
     }
   }
 }
+
+// Helpers
+
+export function currentDate(): Field {
+  return new FunctionField("current_date")
+}
+
+export function lower(field: Field): Field {
+  return new FunctionField("lower", field)
+}
+
+export function upper(field: Field): Field {
+  return new FunctionField("upper", field)
+}
diff --git a/js/typescript-library/src/generate-json-queryDto.ts b/js/typescript-library/src/generate-json-queryDto.ts
index 510f3a3..f21b9cc 100644
--- a/js/typescript-library/src/generate-json-queryDto.ts
+++ b/js/typescript-library/src/generate-json-queryDto.ts
@@ -41,7 +41,7 @@ import {
 import * as fs from "fs"
 import {OrderKeyword} from "./order"
 import {GroupColumnSet} from "./columnset"
-import {AliasedField, ConstantField, TableField, tableField} from "./field"
+import {AliasedField, ConstantField, currentDate, lower, TableField, tableField, upper} from "./field"
 import {Month} from "./period"
 import {Action, QueryCacheParameter} from "./parameter"
 import {countRows, totalCount} from "./index"
@@ -123,7 +123,10 @@ export function buildQuery(): Query {
     criterion(new TableField("f3"), _in([0, 1, 2])),
     criterion(new TableField("f4"), isNull()),
     criterion(new TableField("f5"), isNotNull()),
-    criterion(new TableField("f6"), contains(2))
+    criterion(new TableField("f6"), contains(2)),
+    criterion(lower(new TableField("f7")), eq("a")),
+    criterion(upper(new TableField("f8")), eq("A")),
+    criterion_(new TableField("f9"), currentDate(), ConditionType.GT),
   ]))
 
   q.withHavingCriteria(all([
diff --git a/js/typescript-library/src/index.ts b/js/typescript-library/src/index.ts
index 02d853f..415c2de 100644
--- a/js/typescript-library/src/index.ts
+++ b/js/typescript-library/src/index.ts
@@ -90,7 +90,8 @@ export {
 } from './queryBuilder'
 
 export {
-  TableField, ConstantField, Field, AliasedField, BinaryOperationField, tableFields, tableField,
+  TableField, ConstantField, Field, AliasedField, BinaryOperationField, FunctionField,
+  tableFields, tableField, lower, upper, currentDate
 } from './field'
 export {
   default as Criteria
diff --git a/js/typescript-library/src/util.ts b/js/typescript-library/src/util.ts
index b990916..df6a121 100644
--- a/js/typescript-library/src/util.ts
+++ b/js/typescript-library/src/util.ts
@@ -1,4 +1,4 @@
-import {AliasedField, BinaryOperationField, ConstantField, Field, TableField} from "./field"
+import {AliasedField, BinaryOperationField, ConstantField, Field, FunctionField, TableField} from "./field"
 import PACKAGE from "./package"
 import {ColumnSet, GroupColumnSet} from "./columnset"
 import {Parameter, QueryCacheParameter} from "./parameter"
@@ -36,6 +36,8 @@ function transformToObject(key: string, value: any, reviverFallback?: (key: stri
     return new TableField(value["fullName"], value["alias"])
   } else if (clazz === PACKAGE + "AliasedField") {
     return new AliasedField(value["alias"])
+  } else if (clazz === PACKAGE + "FunctionField") {
+    return new FunctionField(value["functionName"], transformToObject("operand", value["operand"]), value["alias"])
   } else if (clazz === PACKAGE + "dto.GroupColumnSetDto") {
     const m: Map<string, Array<string>> = new Map
     value["values"] && Object.entries(value["values"])?.forEach(([k, v]) => m.set(k, transformToObject(k, v)))


Test patch:
diff --git a/bigquery/src/test/java/io/squashql/query/TestBigQuerySQLFunction.java b/bigquery/src/test/java/io/squashql/query/TestBigQuerySQLFunction.java
new file mode 100644
index 0000000..4f75019
--- /dev/null
+++ b/bigquery/src/test/java/io/squashql/query/TestBigQuerySQLFunction.java
@@ -0,0 +1,51 @@
+package io.squashql.query;
+
+import io.squashql.BigQueryDatastore;
+import io.squashql.BigQueryServiceAccountDatastore;
+import io.squashql.query.database.BigQueryEngine;
+import io.squashql.query.database.QueryEngine;
+import io.squashql.store.Datastore;
+import io.squashql.transaction.BigQueryDataLoader;
+import io.squashql.transaction.DataLoader;
+import org.junit.jupiter.api.AfterAll;
+
+import static io.squashql.query.BigQueryTestUtil.DATASET_NAME;
+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 TestBigQuerySQLFunction extends ATestSQLFunction {
+
+  @AfterAll
+  void tearDown() {
+    this.fieldsByStore.forEach((store, fields) -> BigQueryTestUtil.deleteTable(((BigQueryDatastore) this.datastore).getBigquery(), store));
+  }
+
+  @Override
+  protected void createTables() {
+    BigQueryDataLoader tm = (BigQueryDataLoader) this.tm;
+    BigQueryTestUtil.createDatasetIfDoesNotExist(tm.getBigQuery(), DATASET_NAME);
+    this.fieldsByStore.forEach((store, fields) -> tm.dropAndCreateInMemoryTable(store, fields));
+  }
+
+  @Override
+  protected QueryEngine createQueryEngine(Datastore datastore) {
+    return new BigQueryEngine((BigQueryDatastore) datastore);
+  }
+
+  @Override
+  protected Datastore createDatastore() {
+    return new BigQueryServiceAccountDatastore(BigQueryTestUtil.createServiceAccountCredentials(), PROJECT_ID, DATASET_NAME);
+  }
+
+  @Override
+  protected DataLoader createDataLoader() {
+    return new BigQueryDataLoader(((BigQueryDatastore) this.datastore).getBigquery(), DATASET_NAME);
+  }
+
+  @Override
+  protected Object translate(Object o) {
+    return BigQueryTestUtil.translate(o);
+  }
+}
diff --git a/clickhouse/src/test/java/io/squashql/query/TestClickHouseSQLFunction.java b/clickhouse/src/test/java/io/squashql/query/TestClickHouseSQLFunction.java
new file mode 100644
index 0000000..b7a2d20
--- /dev/null
+++ b/clickhouse/src/test/java/io/squashql/query/TestClickHouseSQLFunction.java
@@ -0,0 +1,53 @@
+package io.squashql.query;
+
+import io.squashql.ClickHouseDatastore;
+import io.squashql.query.database.ClickHouseQueryEngine;
+import io.squashql.query.database.QueryEngine;
+import io.squashql.store.Datastore;
+import io.squashql.template.ClickHouseClassTemplateGenerator;
+import io.squashql.transaction.ClickHouseDataLoader;
+import io.squashql.transaction.DataLoader;
+import org.junit.jupiter.api.AfterAll;
+import org.junit.jupiter.api.BeforeAll;
+
+/**
+ * Do not edit this class, it has been generated automatically by {@link ClickHouseClassTemplateGenerator}.
+ */
+public class TestClickHouseSQLFunction extends ATestSQLFunction {
+
+  public org.testcontainers.containers.GenericContainer container = ClickHouseTestUtil.createClickHouseContainer();
+
+  @BeforeAll
+  @Override
+  void setup() {
+    this.container.start();
+    super.setup();
+  }
+
+  @AfterAll
+  void tearDown() {
+    // we do not stop the container to be able to reuse it between tests.
+    ((ClickHouseDataLoader) this.tm).dropTables(this.fieldsByStore.keySet());
+  }
+
+  @Override
+  protected void createTables() {
+    ClickHouseDataLoader tm = (ClickHouseDataLoader) this.tm;
+    this.fieldsByStore.forEach((store, fields) -> tm.dropAndCreateInMemoryTable(store, fields));
+  }
+
+  @Override
+  protected QueryEngine createQueryEngine(Datastore datastore) {
+    return new ClickHouseQueryEngine((ClickHouseDatastore) datastore);
+  }
+
+  @Override
+  protected Datastore createDatastore() {
+    return new ClickHouseDatastore(ClickHouseTestUtil.jdbcUrl.apply(this.container));
+  }
+
+  @Override
+  protected DataLoader createDataLoader() {
+    return new ClickHouseDataLoader(((ClickHouseDatastore) this.datastore).dataSource);
+  }
+}
diff --git a/core/src/test/java/io/squashql/query/ATestSQLFunction.java b/core/src/test/java/io/squashql/query/ATestSQLFunction.java
new file mode 100644
index 0000000..2707f13
--- /dev/null
+++ b/core/src/test/java/io/squashql/query/ATestSQLFunction.java
@@ -0,0 +1,163 @@
+package io.squashql.query;
+
+import io.squashql.TestClass;
+import io.squashql.query.builder.Query;
+import io.squashql.query.database.SqlUtils;
+import io.squashql.query.dto.ConditionType;
+import io.squashql.query.dto.QueryDto;
+import io.squashql.table.Table;
+import io.squashql.type.TableTypedField;
+import org.assertj.core.api.Assertions;
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.TestInstance;
+
+import java.time.LocalDate;
+import java.util.List;
+import java.util.Map;
+
+import static io.squashql.query.Functions.*;
+
+@TestClass
+@TestInstance(TestInstance.Lifecycle.PER_CLASS)
+public abstract class ATestSQLFunction extends ABaseTestQuery {
+
+  private final String storeName = "store" + ATestSQLFunction.class.getSimpleName().toLowerCase();
+  private final TableField ean = new TableField(this.storeName, "ean");
+  private final TableField category = new TableField(this.storeName, "category");
+  private final TableField price = new TableField(this.storeName, "price");
+  private final TableField date = new TableField(this.storeName, "date");
+  private final LocalDate now = LocalDate.now();
+
+  @Override
+  protected Map<String, List<TableTypedField>> getFieldsByStore() {
+    TableTypedField ean = new TableTypedField(this.storeName, "ean", String.class);
+    TableTypedField category = new TableTypedField(this.storeName, "category", String.class);
+    TableTypedField price = new TableTypedField(this.storeName, "price", double.class);
+    TableTypedField date = new TableTypedField(this.storeName, "date", LocalDate.class);
+
+    return Map.of(this.storeName, List.of(ean, category, price, date));
+  }
+
+  @Override
+  protected void loadData() {
+    // In a test, we are testing current date. So we need to make sure the result is predictable
+    this.tm.load(this.storeName, List.of(
+            new Object[]{"0", "drink", 2d, this.now.minusMonths(1)},
+            new Object[]{"1", "DrinK", 1d, this.now.plusMonths(1)},
+            new Object[]{"2", "food", 3d, this.now.plusMonths(2)},
+            new Object[]{"3", "cloth", 10d, this.now.plusMonths(3)}
+    ));
+  }
+
+  /**
+   * Test {@link Functions#lower(Field)}
+   */
+  @Test
+  void testLower() {
+    // lower in where
+    QueryDto query = Query
+            .from(this.storeName)
+            .where(criterion(lower(this.category), eq("drink")))
+            .select(List.of(this.category), List.of(CountMeasure.INSTANCE))
+            .build();
+    Table result = this.executor.executeQuery(query);
+    Assertions.assertThat(result.headers().stream().map(Header::name)).containsExactly(SqlUtils.squashqlExpression(this.category), CountMeasure.INSTANCE.alias);
+    Assertions.assertThat(result).containsExactly(
+            List.of("DrinK", 1L),
+            List.of("drink", 1L));
+
+    // lower in select and where
+    query = Query
+            .from(this.storeName)
+            .where(criterion(lower(this.category), eq("drink")))
+            .select(List.of(lower(this.category)), List.of(CountMeasure.INSTANCE))
+            .build();
+    result = this.executor.executeQuery(query);
+    Assertions.assertThat(result.headers().stream().map(Header::name)).containsExactly("lower(" + SqlUtils.squashqlExpression(this.category) + ")", CountMeasure.INSTANCE.alias);
+    Assertions.assertThat(result).containsExactly(List.of("drink", 2L));
+
+    // lower in select and where + alias
+    Field lower = lower(this.category).as("lower");
+    query = Query
+            .from(this.storeName)
+            .where(criterion(lower, eq("drink")))
+            .select(List.of(lower), List.of(CountMeasure.INSTANCE))
+            .build();
+    result = this.executor.executeQuery(query);
+    Assertions.assertThat(result.headers().stream().map(Header::name)).containsExactly("lower", CountMeasure.INSTANCE.alias);
+    Assertions.assertThat(result).containsExactly(List.of("drink", 2L));
+  }
+
+  /**
+   * Test {@link Functions#upper(Field)}
+   */
+  @Test
+  void testUpper() {
+    // upper in where
+    QueryDto query = Query
+            .from(this.storeName)
+            .where(criterion(upper(this.category), eq("DRINK")))
+            .select(List.of(this.category), List.of(CountMeasure.INSTANCE))
+            .build();
+    Table result = this.executor.executeQuery(query);
+    Assertions.assertThat(result.headers().stream().map(Header::name)).containsExactly(SqlUtils.squashqlExpression(this.category), CountMeasure.INSTANCE.alias);
+    Assertions.assertThat(result).containsExactly(
+            List.of("DrinK", 1L),
+            List.of("drink", 1L));
+
+    // upper in select and where
+    query = Query
+            .from(this.storeName)
+            .where(criterion(upper(this.category), eq("DRINK")))
+            .select(List.of(upper(this.category)), List.of(CountMeasure.INSTANCE))
+            .build();
+    result = this.executor.executeQuery(query);
+    Assertions.assertThat(result.headers().stream().map(Header::name)).containsExactly("upper(" + SqlUtils.squashqlExpression(this.category) + ")", CountMeasure.INSTANCE.alias);
+    Assertions.assertThat(result).containsExactly(List.of("DRINK", 2L));
+
+    // upper in select and where + alias
+    Field upper = upper(this.category).as("upper");
+    query = Query
+            .from(this.storeName)
+            .where(criterion(upper, eq("DRINK")))
+            .select(List.of(upper), List.of(CountMeasure.INSTANCE))
+            .build();
+    result = this.executor.executeQuery(query);
+    Assertions.assertThat(result.headers().stream().map(Header::name)).containsExactly("upper", CountMeasure.INSTANCE.alias);
+    Assertions.assertThat(result).containsExactly(List.of("DRINK", 2L));
+  }
+
+  @Test
+  void testCurrentDate() {
+    // currentDate in where
+    QueryDto query = Query
+            .from(this.storeName)
+            .where(criterion(this.date, Functions.currentDate(), ConditionType.LT))
+            .select(List.of(this.ean), List.of(CountMeasure.INSTANCE))
+            .build();
+    Table result = this.executor.executeQuery(query);
+    Assertions.assertThat(result.headers().stream().map(Header::name)).containsExactly(SqlUtils.squashqlExpression(this.ean), CountMeasure.INSTANCE.alias);
+    Assertions.assertThat(result).containsExactly(List.of("0", 1L));
+
+    // currentDate in select and where. This query does not make any sense...
+    query = Query
+            .from(this.storeName)
+            .where(criterion(this.date, Functions.currentDate(), ConditionType.LT))
+            .select(List.of(Functions.currentDate()), List.of(CountMeasure.INSTANCE))
+            .build();
+    result = this.executor.executeQuery(query);
+    Assertions.assertThat(result.headers().stream().map(Header::name)).containsExactly("current_date()", CountMeasure.INSTANCE.alias);
+    Assertions.assertThat(result).containsExactly(List.of(this.now, 1L));
+
+    // currentDate in select and where + alias. This query does not make any sense...
+    Field cr = currentDate().as("CR");
+    query = Query
+            .from(this.storeName)
+            .where(criterion(this.date, cr, ConditionType.LT))
+            .select(List.of(cr), List.of(CountMeasure.INSTANCE))
+            .build();
+    result = this.executor.executeQuery(query);
+    Assertions.assertThat(result.headers().stream().map(Header::name)).containsExactly("CR", CountMeasure.INSTANCE.alias);
+    Assertions.assertThat(result).containsExactly(List.of(this.now, 1L));
+  }
+}
diff --git a/duckdb/src/test/java/io/squashql/query/TestDuckDBSQLFunction.java b/duckdb/src/test/java/io/squashql/query/TestDuckDBSQLFunction.java
new file mode 100644
index 0000000..af6ebe2
--- /dev/null
+++ b/duckdb/src/test/java/io/squashql/query/TestDuckDBSQLFunction.java
@@ -0,0 +1,35 @@
+package io.squashql.query;
+
+import io.squashql.DuckDBDatastore;
+import io.squashql.query.database.DuckDBQueryEngine;
+import io.squashql.query.database.QueryEngine;
+import io.squashql.store.Datastore;
+import io.squashql.transaction.DataLoader;
+import io.squashql.transaction.DuckDBDataLoader;
+
+/**
+ * Do not edit this class, it has been generated automatically by {@link io.squashql.template.DuckDBClassTemplateGenerator}.
+ */
+public class TestDuckDBSQLFunction extends ATestSQLFunction {
+
+  @Override
+  protected QueryEngine createQueryEngine(Datastore datastore) {
+    return new DuckDBQueryEngine((DuckDBDatastore) datastore);
+  }
+
+  @Override
+  protected Datastore createDatastore() {
+    return new DuckDBDatastore();
+  }
+
+  @Override
+  protected DataLoader createDataLoader() {
+    return new DuckDBDataLoader((DuckDBDatastore) this.datastore);
+  }
+
+  @Override
+  protected void createTables() {
+    DuckDBDataLoader tm = (DuckDBDataLoader) this.tm;
+    this.fieldsByStore.forEach(tm::createOrReplaceTable);
+  }
+}
diff --git a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
index d23be12..538e586 100644
--- a/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
+++ b/server/src/test/java/io/squashql/js/TestJavascriptLibrary.java
@@ -113,14 +113,22 @@ public class TestJavascriptLibrary {
     ));
 
     var queryCondition = or(and(eq("a"), eq("b")), lt(5), like("a%"));
-    q.withCondition(tableField("f1"), queryCondition);
-    q.withCondition(new AliasedField("f2"), gt(659));
-    q.withCondition(tableField("f3"), in(0, 1, 2));
-    q.withCondition(tableField("f4"), isNull());
-    q.withCondition(tableField("f5"), isNotNull());
-    q.withCondition(tableField("f6"), contains(2));
-
-    q.withHavingCriteria(all(criterion(price, ge(10)), criterion(expression, lt(100))));
+    q.withWhereCriteria(all(
+            criterion(tableField("f1"), queryCondition),
+            criterion(new AliasedField("f2"), gt(659)),
+            criterion(tableField("f3"), in(0, 1, 2)),
+            criterion(tableField("f4"), isNull()),
+            criterion(tableField("f5"), isNotNull()),
+            criterion(tableField("f6"), contains(2)),
+            criterion(lower(tableField("f7")), eq("a")),
+            criterion(upper(tableField("f8")), eq("A")),
+            criterion(new TableField("f9"), currentDate(), ConditionType.GT)
+    ));
+
+    q.withHavingCriteria(all(
+            criterion(price, ge(10)),
+            criterion(expression, lt(100))
+    ));
 
     q.orderBy(a, OrderKeywordDto.ASC);
     q.orderBy(b, List.of("1", "l", "p"));
diff --git a/snowflake/src/test/java/io/squashql/query/TestSnowflakeSQLFunction.java b/snowflake/src/test/java/io/squashql/query/TestSnowflakeSQLFunction.java
new file mode 100644
index 0000000..5b750a1
--- /dev/null
+++ b/snowflake/src/test/java/io/squashql/query/TestSnowflakeSQLFunction.java
@@ -0,0 +1,52 @@
+package io.squashql.query;
+
+import io.squashql.SnowflakeDatastore;
+import io.squashql.query.database.QueryEngine;
+import io.squashql.query.database.SnowflakeQueryEngine;
+import io.squashql.store.Datastore;
+import io.squashql.transaction.DataLoader;
+import io.squashql.transaction.SnowflakeDataLoader;
+import org.junit.jupiter.api.AfterAll;
+
+/**
+ * Do not edit this class, it has been generated automatically by {@link io.squashql.template.SnowflakeClassTemplateGenerator}.
+ */
+public class TestSnowflakeSQLFunction extends ATestSQLFunction {
+
+  @AfterAll
+  void tearDown() {
+    SnowflakeDataLoader tm = (SnowflakeDataLoader) this.tm;
+    this.fieldsByStore.forEach((storeName, storeFields) -> tm.dropTable(storeName));
+  }
+
+  @Override
+  protected void createTables() {
+    SnowflakeDataLoader tm = (SnowflakeDataLoader) this.tm;
+    this.fieldsByStore.forEach(tm::createOrReplaceTable);
+  }
+
+  @Override
+  protected QueryEngine createQueryEngine(Datastore datastore) {
+    return new SnowflakeQueryEngine((SnowflakeDatastore) datastore);
+  }
+
+  @Override
+  protected Datastore createDatastore() {
+    return new SnowflakeDatastore(
+            SnowflakeTestUtil.jdbcUrl,
+            SnowflakeTestUtil.database,
+            SnowflakeTestUtil.schema,
+            SnowflakeTestUtil.properties
+    );
+  }
+
+  @Override
+  protected DataLoader createDataLoader() {
+    return new SnowflakeDataLoader((SnowflakeDatastore) this.datastore);
+  }
+
+  @Override
+  protected Object translate(Object o) {
+    return SnowflakeTestUtil.translate(o);
+  }
+}
diff --git a/spark/src/test/java/io/squashql/query/TestSparkSQLFunction.java b/spark/src/test/java/io/squashql/query/TestSparkSQLFunction.java
new file mode 100644
index 0000000..71a75ce
--- /dev/null
+++ b/spark/src/test/java/io/squashql/query/TestSparkSQLFunction.java
@@ -0,0 +1,42 @@
+package io.squashql.query;
+
+import io.squashql.SparkDatastore;
+import io.squashql.query.database.QueryEngine;
+import io.squashql.query.database.SparkQueryEngine;
+import io.squashql.store.Datastore;
+import io.squashql.transaction.DataLoader;
+import io.squashql.transaction.SparkDataLoader;
+import org.junit.jupiter.api.AfterAll;
+
+/**
+ * Do not edit this class, it has been generated automatically by {@link io.squashql.template.SparkClassTemplateGenerator}.
+ */
+public class TestSparkSQLFunction extends ATestSQLFunction {
+
+  @AfterAll
+  void tearDown() {
+    this.fieldsByStore.keySet().forEach(storeName -> ((SparkDatastore) this.datastore).spark.catalog().dropTempView(storeName));
+  }
+
+  @Override
+  protected QueryEngine createQueryEngine(Datastore datastore) {
+    return new SparkQueryEngine((SparkDatastore) datastore);
+  }
+
+  @Override
+  protected Datastore createDatastore() {
+    return new SparkDatastore();
+  }
+
+  @Override
+  protected DataLoader createDataLoader() {
+    SparkDatastore ds = (SparkDatastore) this.datastore;
+    return new SparkDataLoader(ds.spark);
+  }
+
+  @Override
+  protected void createTables() {
+    SparkDataLoader tm = (SparkDataLoader) this.tm;
+    this.fieldsByStore.forEach((store, fields) -> tm.createTemporaryTable(store, fields));
+  }
+}