Repository: squashql/squashql (java)

Issue:
Remove sub-query limit
To use bucketing with complex measures, implying 2 levels of aggregations, we need a sub-query inside a sub-query. Sadly, when we try to execute a query with 2 sub-queries, we face the following backend error:
```
java.lang.IllegalArgumentException: sub-query in a sub-query is not supported
```
Ideally, SquashQL should have no such limit, and allow an unlimited number of nested sub-queries.


Pull request description:
Support N-subqueries
Fixes #293 

Patch:
diff --git a/bigquery/src/main/java/io/squashql/BigQueryEndUserDatastore.java b/bigquery/src/main/java/io/squashql/BigQueryEndUserDatastore.java
index b83435f..4bc8226 100644
--- a/bigquery/src/main/java/io/squashql/BigQueryEndUserDatastore.java
+++ b/bigquery/src/main/java/io/squashql/BigQueryEndUserDatastore.java
@@ -68,7 +68,7 @@ public class BigQueryEndUserDatastore implements BigQueryDatastore {
   }
 
   @Override
-  public Map<String, Store> storesByName() {
+  public Map<String, Store> storeByName() {
     OAuth2AuthenticationToken token = getOAuth2AuthenticationToken();
     return this.stores.get(token.getPrincipal().getName(), name -> fetchStoresByName(this));
   }
diff --git a/bigquery/src/main/java/io/squashql/BigQueryServiceAccountDatastore.java b/bigquery/src/main/java/io/squashql/BigQueryServiceAccountDatastore.java
index 7fb9613..d1bc0ab 100644
--- a/bigquery/src/main/java/io/squashql/BigQueryServiceAccountDatastore.java
+++ b/bigquery/src/main/java/io/squashql/BigQueryServiceAccountDatastore.java
@@ -51,7 +51,7 @@ public class BigQueryServiceAccountDatastore implements BigQueryDatastore {
   }
 
   @Override
-  public Map<String, Store> storesByName() {
+  public Map<String, Store> storeByName() {
     return this.stores.get();
   }
 
diff --git a/clickhouse/src/main/java/io/squashql/ClickHouseDatastore.java b/clickhouse/src/main/java/io/squashql/ClickHouseDatastore.java
index d1d3a16..226ee85 100644
--- a/clickhouse/src/main/java/io/squashql/ClickHouseDatastore.java
+++ b/clickhouse/src/main/java/io/squashql/ClickHouseDatastore.java
@@ -45,7 +45,7 @@ public class ClickHouseDatastore implements Datastore {
   }
 
   @Override
-  public Map<String, Store> storesByName() {
+  public Map<String, Store> storeByName() {
     return this.stores.get();
   }
 
diff --git a/core/src/main/java/io/squashql/query/QueryExecutor.java b/core/src/main/java/io/squashql/query/QueryExecutor.java
index c0e7a04..85cbee0 100644
--- a/core/src/main/java/io/squashql/query/QueryExecutor.java
+++ b/core/src/main/java/io/squashql/query/QueryExecutor.java
@@ -100,7 +100,7 @@ public class QueryExecutor {
                             IntConsumer limitNotifier) {
     QueryDto preparedQuery = prepareQuery(query);
 
-    QueryResolver queryResolver = new QueryResolver(preparedQuery, this.queryEngine.datastore().storesByName());
+    QueryResolver queryResolver = new QueryResolver(preparedQuery, this.queryEngine.datastore().storeByName());
     DependencyGraph<QueryPlanNodeKey> dependencyGraph = computeDependencyGraph(
             queryResolver.getColumns(), queryResolver.getGroupColumns(), queryResolver.getMeasures().values(), queryResolver.getScope());
     // Compute what needs to be prefetched
diff --git a/core/src/main/java/io/squashql/query/QueryResolver.java b/core/src/main/java/io/squashql/query/QueryResolver.java
index cb968eb..cc3116e 100644
--- a/core/src/main/java/io/squashql/query/QueryResolver.java
+++ b/core/src/main/java/io/squashql/query/QueryResolver.java
@@ -26,13 +26,12 @@ import static io.squashql.query.compiled.CompiledAggregatedMeasure.COMPILED_COUN
 public class QueryResolver {
 
   private final QueryDto query;
-  private final Map<String, Store> storesByName;
+  private final Map<String, Store> storeByName;
   private final Set<String> cteTableNames = new HashSet<>();
   private final QueryScope scope;
   private final List<TypedField> groupColumns;
   private final List<TypedField> columns;
   private final CompilationCache cache = new CompilationCache();
-  private final Map<Measure, CompiledMeasure> subQueryMeasures;
   private final Map<Measure, CompiledMeasure> measures;
   private final Map<ColumnSetKey, CompiledColumnSet> compiledColumnSets;
   /**
@@ -40,25 +39,29 @@ public class QueryResolver {
    */
   private final List<CompiledOrderBy> compiledOrderByInDB;
 
-  public QueryResolver(QueryDto query, Map<String, Store> storesByName) {
+  public QueryResolver(QueryDto query, Map<String, Store> storeByName) {
     this.query = query;
-    this.storesByName = new HashMap<>(storesByName);
-    if (query.virtualTableDtos != null) {
-      for (VirtualTableDto virtualTableDto : query.virtualTableDtos) {
-        this.storesByName.put(virtualTableDto.name, VirtualTableDto.toStore(virtualTableDto));
-        this.cteTableNames.add(virtualTableDto.name);
-      }
-    }
+    this.storeByName = createStoreByName(query, storeByName);
     this.columns = query.columns.stream().map(this::resolveField).toList();
     this.groupColumns = Optional.ofNullable(query.columnSets.get(ColumnSetKey.GROUP))
             .stream().flatMap(cs -> cs.getColumnsForPrefetching().stream()).map(this::resolveField).toList();
-    this.subQueryMeasures = query.table.subQuery == null ? Collections.emptyMap() : compileMeasures(query.table.subQuery.measures, false);
     this.compiledOrderByInDB = compileOrderByInDB(query.orders);
     this.scope = toQueryScope(query);
     this.measures = compileMeasures(query.measures, true);
     this.compiledColumnSets = compiledColumnSets(query.columnSets);
   }
 
+  private Map<String, Store> createStoreByName(QueryDto query, Map<String, Store> storeByName) {
+    Map<String, Store> res = new HashMap<>(storeByName);
+    if (query.virtualTableDtos != null) {
+      for (VirtualTableDto virtualTableDto : query.virtualTableDtos) {
+        res.put(virtualTableDto.name, VirtualTableDto.toStore(virtualTableDto));
+        this.cteTableNames.add(virtualTableDto.name);
+      }
+    }
+    return res;
+  }
+
   /**
    * Retrieves or resolves the {@link TypedField} corresponding to the given Field.
    *
@@ -135,7 +138,7 @@ public class QueryResolver {
     if (split.length > 1) {
       final String tableName = split[0];
       final String fieldNameInTable = split[1];
-      Store store = this.storesByName.get(tableName);
+      Store store = this.storeByName.get(tableName);
       if (store != null) {
         for (TableTypedField field : store.fields()) {
           if (field.name().equals(fieldNameInTable)) {
@@ -144,7 +147,7 @@ public class QueryResolver {
         }
       }
     } else {
-      for (Store store : this.storesByName.values()) {
+      for (Store store : this.storeByName.values()) {
         for (TableTypedField field : store.fields()) {
           if (field.name().equals(fieldName)) {
             // We omit on purpose the store name. It will be determined by the underlying SQL engine of the DB.
@@ -188,32 +191,8 @@ public class QueryResolver {
     }
   }
 
-  private DatabaseQuery toSubQuery(QueryDto subQuery) {
-    checkSubQuery(subQuery);
-    final CompiledTable table = compileTable(subQuery.table);
-    final List<TypedField> select = subQuery.columns.stream().map(this::resolveField).toList();
-    final CompiledCriteria whereCriteria = compileCriteria(subQuery.whereCriteriaDto);
-    final CompiledCriteria havingCriteria = compileCriteria(subQuery.havingCriteriaDto);
-    // should we check groupingSet and rollup as well are empty ?
-    QueryScope queryScope = new QueryScope(table,
-            select,
-            whereCriteria,
-            havingCriteria,
-            Collections.emptyList(),
-            Collections.emptySet(),
-            null, // FIXME is it correct?
-            Collections.emptyList(),
-            subQuery.limit);
-    return new DatabaseQuery(queryScope, new ArrayList<>(this.subQueryMeasures.values()));
-  }
-
-  private void checkSubQuery(final QueryDto subQuery) {
-    if (subQuery.table.subQuery != null) {
-      throw new IllegalArgumentException("sub-query in a sub-query is not supported");
-    }
-    if (subQuery.virtualTableDtos != null && !subQuery.virtualTableDtos.isEmpty()) {
-      throw new IllegalArgumentException("virtualTables in a sub-query is not supported");
-    }
+  private void checkSubQuery(QueryDto subQuery) {
+    compileMeasures(subQuery.measures, false);
     if (subQuery.columnSets != null && !subQuery.columnSets.isEmpty()) {
       throw new IllegalArgumentException("column sets are not expected in sub query: " + subQuery);
     }
@@ -245,7 +224,10 @@ public class QueryResolver {
       return new MaterializedTable(table.name, joins);
     } else if (table.subQuery != null) {
       List<CompiledJoin> joins = compileJoins(table.joins);
-      return new NestedQueryTable(toSubQuery(table.subQuery), joins);
+      checkSubQuery(table.subQuery);
+      QueryResolver qr = new QueryResolver(table.subQuery, this.storeByName);
+      DatabaseQuery dq = new DatabaseQuery(qr.getScope(), new ArrayList<>(qr.measures.values()));
+      return new NestedQueryTable(dq, joins);
     } else {
       throw new IllegalStateException();
     }
diff --git a/core/src/main/java/io/squashql/query/join/ExperimentalQueryMergeExecutor.java b/core/src/main/java/io/squashql/query/join/ExperimentalQueryMergeExecutor.java
index 3ca4362..e58ae4d 100644
--- a/core/src/main/java/io/squashql/query/join/ExperimentalQueryMergeExecutor.java
+++ b/core/src/main/java/io/squashql/query/join/ExperimentalQueryMergeExecutor.java
@@ -41,7 +41,7 @@ public class ExperimentalQueryMergeExecutor {
     Holder(String cteTableName, QueryDto query) {
       this.query = query;
       this.cteTableName = cteTableName;
-      this.queryResolver = new QueryResolver(query, ExperimentalQueryMergeExecutor.this.queryEngine.datastore().storesByName());
+      this.queryResolver = new QueryResolver(query, ExperimentalQueryMergeExecutor.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.originalTableName = query.table != null ? query.table.name : null;
diff --git a/core/src/main/java/io/squashql/store/Datastore.java b/core/src/main/java/io/squashql/store/Datastore.java
index d20b143..3209d25 100644
--- a/core/src/main/java/io/squashql/store/Datastore.java
+++ b/core/src/main/java/io/squashql/store/Datastore.java
@@ -4,5 +4,5 @@ import java.util.Map;
 
 public interface Datastore {
 
-  Map<String, Store> storesByName();
+  Map<String, Store> storeByName();
 }
diff --git a/core/src/main/java/io/squashql/util/SchemaTypeScriptCodeGenerator.java b/core/src/main/java/io/squashql/util/SchemaTypeScriptCodeGenerator.java
index c8fa2cf..2f5a63f 100644
--- a/core/src/main/java/io/squashql/util/SchemaTypeScriptCodeGenerator.java
+++ b/core/src/main/java/io/squashql/util/SchemaTypeScriptCodeGenerator.java
@@ -42,7 +42,7 @@ public class SchemaTypeScriptCodeGenerator {
 
   public static String getFileContent(Datastore datastore) {
     StringBuilder sb = new StringBuilder();
-    Map<String, Store> storesByName = new TreeMap<>(datastore.storesByName()); // order by store name
+    Map<String, Store> storesByName = new TreeMap<>(datastore.storeByName()); // order by store name
     storesByName.forEach((storeName, store) -> {
       sb.append("export class ").append(transformName(storeName, true)).append("Table {").append(System.lineSeparator());
       addAttribute(sb, "tableName", storeName);
diff --git a/duckdb/src/main/java/io/squashql/DuckDBDatastore.java b/duckdb/src/main/java/io/squashql/DuckDBDatastore.java
index 15d5616..e0ad0da 100644
--- a/duckdb/src/main/java/io/squashql/DuckDBDatastore.java
+++ b/duckdb/src/main/java/io/squashql/DuckDBDatastore.java
@@ -56,7 +56,7 @@ public class DuckDBDatastore implements JdbcDatastore {
   }
 
   @Override
-  public Map<String, Store> storesByName() {
+  public Map<String, Store> storeByName() {
     return this.stores.get();
   }
 }
diff --git a/duckdb/src/main/java/io/squashql/transaction/DuckDBDataLoader.java b/duckdb/src/main/java/io/squashql/transaction/DuckDBDataLoader.java
index a5ec1f1..9143aaa 100644
--- a/duckdb/src/main/java/io/squashql/transaction/DuckDBDataLoader.java
+++ b/duckdb/src/main/java/io/squashql/transaction/DuckDBDataLoader.java
@@ -113,7 +113,7 @@ public class DuckDBDataLoader implements DataLoader {
   }
 
   private void ensureScenarioColumnIsPresent(String store) {
-    List<TableTypedField> fields = this.datastore.storesByName().get(store).fields();
+    List<TableTypedField> fields = this.datastore.storeByName().get(store).fields();
     boolean found = fields.stream().anyMatch(f -> f.name().equals(SCENARIO_FIELD_NAME));
     if (!found) {
       throw new RuntimeException(String.format("%s field not found", SCENARIO_FIELD_NAME));
@@ -121,7 +121,7 @@ public class DuckDBDataLoader implements DataLoader {
   }
 
   private boolean scenarioColumnIsPresent(String store) {
-    List<TableTypedField> fields = this.datastore.storesByName().get(store).fields();
+    List<TableTypedField> fields = this.datastore.storeByName().get(store).fields();
     return fields.stream().anyMatch(f -> f.name().equals(SCENARIO_FIELD_NAME));
   }
 
diff --git a/js/typescript-library/package-lock.json b/js/typescript-library/package-lock.json
index 9fa0f43..cbd2ecf 100644
--- a/js/typescript-library/package-lock.json
+++ b/js/typescript-library/package-lock.json
@@ -1,12 +1,12 @@
 {
   "name": "@squashql/squashql-js",
-  "version": "1.15.0",
+  "version": "1.15.1",
   "lockfileVersion": 2,
   "requires": true,
   "packages": {
     "": {
       "name": "@squashql/squashql-js",
-      "version": "1.15.0",
+      "version": "1.15.1",
       "license": "Apache-2.0",
       "dependencies": {
         "axios": "^1.6.7"
diff --git a/server/src/main/java/io/squashql/spring/web/rest/QueryController.java b/server/src/main/java/io/squashql/spring/web/rest/QueryController.java
index cb520e3..e713f71 100644
--- a/server/src/main/java/io/squashql/spring/web/rest/QueryController.java
+++ b/server/src/main/java/io/squashql/spring/web/rest/QueryController.java
@@ -134,7 +134,7 @@ public class QueryController {
   @GetMapping(MAPPING_METADATA)
   public ResponseEntity<MetadataResultDto> getMetadata() {
     List<MetadataResultDto.StoreMetadata> stores = new ArrayList<>();
-    for (Store store : this.queryEngine.datastore().storesByName().values()) {
+    for (Store store : this.queryEngine.datastore().storeByName().values()) {
       List<MetadataItem> items = store.fields().stream().map(f -> new MetadataItem(f.name(), f.name(), f.type())).toList();
       stores.add(new MetadataResultDto.StoreMetadata(store.name(), items));
     }
diff --git a/snowflake/src/main/java/io/squashql/SnowflakeDatastore.java b/snowflake/src/main/java/io/squashql/SnowflakeDatastore.java
index d7ffdba..01f8393 100644
--- a/snowflake/src/main/java/io/squashql/SnowflakeDatastore.java
+++ b/snowflake/src/main/java/io/squashql/SnowflakeDatastore.java
@@ -49,7 +49,7 @@ public class SnowflakeDatastore implements JdbcDatastore {
   }
 
   @Override
-  public Map<String, Store> storesByName() {
+  public Map<String, Store> storeByName() {
     return this.stores.get();
   }
 }
diff --git a/snowflake/src/main/java/io/squashql/transaction/SnowflakeDataLoader.java b/snowflake/src/main/java/io/squashql/transaction/SnowflakeDataLoader.java
index 47110e1..e9a8cc8 100644
--- a/snowflake/src/main/java/io/squashql/transaction/SnowflakeDataLoader.java
+++ b/snowflake/src/main/java/io/squashql/transaction/SnowflakeDataLoader.java
@@ -86,7 +86,7 @@ public class SnowflakeDataLoader implements DataLoader {
   }
 
   private void ensureScenarioColumnIsPresent(String store) {
-    List<TableTypedField> fields = this.snowflakeDatastore.storesByName().get(store).fields();
+    List<TableTypedField> fields = this.snowflakeDatastore.storeByName().get(store).fields();
     boolean found = fields.stream().anyMatch(f -> f.name().equals(SCENARIO_FIELD_NAME));
     if (!found) {
       throw new RuntimeException(String.format("%s field not found", SCENARIO_FIELD_NAME));
diff --git a/spark/src/main/java/io/squashql/SparkDatastore.java b/spark/src/main/java/io/squashql/SparkDatastore.java
index 3e5eeae..9662eee 100644
--- a/spark/src/main/java/io/squashql/SparkDatastore.java
+++ b/spark/src/main/java/io/squashql/SparkDatastore.java
@@ -49,7 +49,7 @@ public class SparkDatastore implements Datastore {
   }
 
   @Override
-  public Map<String, Store> storesByName() {
+  public Map<String, Store> storeByName() {
     return this.stores.get();
   }
 


Test patch:
diff --git a/core/src/test/java/io/squashql/query/ATestSubQuery.java b/core/src/test/java/io/squashql/query/ATestSubQuery.java
index b3b2c6b..fbb83d2 100644
--- a/core/src/test/java/io/squashql/query/ATestSubQuery.java
+++ b/core/src/test/java/io/squashql/query/ATestSubQuery.java
@@ -2,10 +2,11 @@ package io.squashql.query;
 
 import io.squashql.TestClass;
 import io.squashql.query.builder.Query;
-import io.squashql.query.dto.QueryDto;
+import io.squashql.query.dto.*;
 import io.squashql.table.Table;
 import io.squashql.type.TableTypedField;
 import org.assertj.core.api.Assertions;
+import org.junit.jupiter.api.Assumptions;
 import org.junit.jupiter.api.Test;
 import org.junit.jupiter.api.TestInstance;
 
@@ -14,7 +15,6 @@ import java.util.List;
 import java.util.Map;
 
 import static io.squashql.query.Functions.*;
-import static io.squashql.query.TableField.tableFields;
 import static io.squashql.query.agg.AggregationFunction.AVG;
 import static io.squashql.query.database.QueryEngine.GRAND_TOTAL;
 import static io.squashql.transaction.DataLoader.MAIN_SCENARIO_NAME;
@@ -23,45 +23,68 @@ import static io.squashql.transaction.DataLoader.MAIN_SCENARIO_NAME;
 @TestInstance(TestInstance.Lifecycle.PER_CLASS)
 public abstract class ATestSubQuery extends ABaseTestQuery {
 
+  final String store = "student" + getClass().getSimpleName().toLowerCase();
+  final TableField studentName = new TableField(this.store, "name");
+  final TableField group = new TableField(this.store, "group");
+  final TableField test = new TableField(this.store, "test");
+  final TableField score = new TableField(this.store, "score");
+
   @Override
   protected Map<String, List<TableTypedField>> getFieldsByStore() {
     // See https://mariadb.com/kb/en/subqueries-in-a-from-clause/
-    TableTypedField studentName = new TableTypedField("student", "name", String.class);
-    TableTypedField test = new TableTypedField("student", "test", String.class);
-    TableTypedField score = new TableTypedField("student", "score", int.class);
-    return Map.of("student", List.of(studentName, test, score));
+    TableTypedField studentName = new TableTypedField(this.store, "name", String.class);
+    TableTypedField group = new TableTypedField(this.store, "group", String.class);
+    TableTypedField test = new TableTypedField(this.store, "test", String.class);
+    TableTypedField score = new TableTypedField(this.store, "score", int.class);
+    return Map.of(this.store, List.of(group, studentName, test, score));
   }
 
   @Override
   protected void loadData() {
-    this.tm.load(MAIN_SCENARIO_NAME, "student", List.of(
-            new Object[]{"Paul", "sql", 75},
-            new Object[]{"Paul", "java", 73},
-            new Object[]{"Peter", "sql", 43},
-            new Object[]{"Peter", "java", 33},
-            new Object[]{"Tatiana", "sql", 87},
-            new Object[]{"Tatiana", "java", 85}
+    this.tm.load(MAIN_SCENARIO_NAME, this.store, List.of(
+            new Object[]{"group a", "Paul", "sql", 50},
+            new Object[]{"group a", "Paul", "java", 70},
+            new Object[]{"group a", "Peter", "sql", 45},
+            new Object[]{"group a", "Peter", "java", 35},
+            new Object[]{"group a", "Tatiana", "sql", 85},
+            new Object[]{"group a", "Tatiana", "java", 75},
+
+            new Object[]{"group b", "John", "sql", 50},
+            new Object[]{"group b", "John", "java", 80},
+            new Object[]{"group b", "William", "sql", 60},
+            new Object[]{"group b", "William", "java", 90},
+            new Object[]{"group b", "Kate", "sql", 70},
+            new Object[]{"group b", "Kate", "java", 70},
+
+            new Object[]{"group c", "Paul", "sql", 35},
+            new Object[]{"group c", "Paul", "java", 25},
+            new Object[]{"group c", "Liam", "sql", 56},
+            new Object[]{"group c", "Liam", "java", 24},
+            new Object[]{"group c", "Scott", "sql", 80},
+            new Object[]{"group c", "Scott", "java", 80}
     ));
   }
 
   @Test
   void testSubQuery() {
-    QueryDto subQuery = Query.from("student")
-            .select(tableFields(List.of("name")), List.of(sum("score_sum", "score")))
+    QueryDto subQuery = Query.from(this.store)
+            .where(criterion(this.group, eq("group a")))
+            .select(List.of(this.studentName), List.of(sum("score_sum", this.score)))
             .build();
     // See https://mariadb.com/kb/en/subqueries-in-a-from-clause/
     QueryDto queryDto = Query.from(subQuery)
             .select(Collections.emptyList(), List.of(avg("avg", "score_sum")))
             .build();
     Table result = this.executor.executeQuery(queryDto);
-    Assertions.assertThat(result).containsExactly(List.of(132d));
+    Assertions.assertThat(result).containsExactly(List.of(120d));
   }
 
   @Test
   void testSubQueryWithAlias() {
-    Field tableField = new TableField("name").as("student_name");
-    QueryDto subQuery = Query.from("student")
-            .select(List.of(tableField), List.of(sum("score_sum", "score")))
+    Field tableField = this.studentName.as("student_name");
+    QueryDto subQuery = Query.from(this.store)
+            .where(criterion(this.group, eq("group a")))
+            .select(List.of(tableField), List.of(sum("score_sum", this.score)))
             .build();
     // See https://mariadb.com/kb/en/subqueries-in-a-from-clause/
     QueryDto queryDto = Query.from(subQuery)
@@ -70,15 +93,16 @@ public abstract class ATestSubQuery extends ABaseTestQuery {
     Table result = this.executor.executeQuery(queryDto);
     Assertions.assertThat(result.headers().stream().map(Header::name)).containsExactly("student_name", "avg");
     Assertions.assertThat(result).containsExactly(
-            List.of("Paul", 148d),
-            List.of("Peter", 76d),
-            List.of("Tatiana", 172d));
+            List.of("Paul", 120d),
+            List.of("Peter", 80d),
+            List.of("Tatiana", 160d));
   }
 
   @Test
   void testSubQueryAggIfWithConditionOnSubQueryField() {
-    QueryDto subQuery = Query.from("student")
-            .select(tableFields(List.of("name")), List.of(sum("score_sum", "score")))
+    QueryDto subQuery = Query.from(this.store)
+            .where(criterion(this.group, eq("group a")))
+            .select(List.of(this.studentName), List.of(sum("score_sum", this.score)))
             .build();
 
     // Take into account only score.sum >= 100
@@ -88,26 +112,87 @@ public abstract class ATestSubQuery extends ABaseTestQuery {
             .select(Collections.emptyList(), List.of(avg))
             .build();
     Table result = this.executor.executeQuery(queryDto);
-    Assertions.assertThat(result).containsExactly(List.of(160d));
+    Assertions.assertThat(result).containsExactly(List.of(140d));
   }
 
   @Test
   void testSubQueryAndRollup() {
     // This sub-query does not really make sense in that case, but the idea is to have 1 remaining column in the
-    // top-select to do a rollup afterwards.
-    QueryDto subQuery = Query.from("student")
-            .select(tableFields(List.of("name", "score")), List.of(min("score_min", "score")))
+    // top-select to do a rollup afterward.
+    QueryDto subQuery = Query.from(this.store)
+            .where(criterion(this.group, eq("group a")))
+            .select(List.of(this.studentName, this.score), List.of(min("score_min", this.score)))
             .build();
 
     QueryDto queryDto = Query.from(subQuery)
-            .select(tableFields(List.of("name")), List.of(avg("avg", "score_min")))
-            .rollup(tableFields(List.of("name")))
+            .select(List.of(new AliasedField(this.studentName.fieldName)), List.of(avg("avg", "score_min")))
+            .rollup(List.of(new AliasedField(this.studentName.fieldName)))
             .build();
     Table result = this.executor.executeQuery(queryDto);
     Assertions.assertThat(result).containsExactly(
-            List.of(GRAND_TOTAL, 66d),
-            List.of("Paul", 74d),
-            List.of("Peter", 38d),
-            List.of("Tatiana", 86d));
+            List.of(GRAND_TOTAL, 60d),
+            List.of("Paul", 60d),
+            List.of("Peter", 40d),
+            List.of("Tatiana", 80d));
+  }
+
+  @Test
+  void testTwoNestedSubQueries() {
+    QueryDto subQuery2 = Query.from(this.store)
+            .select(List.of(this.group, this.studentName), List.of(avg("score_avg_student", this.score)))
+            .build();
+
+    QueryDto subQuery1 = Query.from(subQuery2)
+            .select(List.of(new AliasedField(this.group.fieldName)), List.of(avg("score_avg_group", new AliasedField("score_avg_student"))))
+            .build();
+
+    Table result = this.executor.executeQuery(subQuery1);
+    Assertions.assertThat(result).containsExactly(
+            List.of("group a", 60d),
+            List.of("group b", 70d),
+            List.of("group c", 50d));
+
+    QueryDto query = Query.from(subQuery1)
+            .select(List.of(), List.of(avg("score_avg", new AliasedField("score_avg_group"))))
+            .build();
+    result = this.executor.executeQuery(query);
+    Assertions.assertThat(result).containsExactly(
+            List.of(60d));
+  }
+
+  @Test
+  void testTwoNestedSubQueriesAndBucketing() {
+    // CLICKHOUSE does not support non-equi join
+    Assumptions.assumeFalse(this.queryEngine.getClass().getSimpleName().contains(TestClass.Type.CLICKHOUSE.className));
+
+    QueryDto subQuery2 = Query.from(this.store)
+            .select(List.of(this.group, this.studentName), List.of(avg("score_avg_student", this.score)))
+            .build();
+
+    QueryDto subQuery1 = Query.from(subQuery2)
+            .select(List.of(new AliasedField(this.group.fieldName)), List.of(avg("score_avg_group", new AliasedField("score_avg_student"))))
+            .build();
+
+    VirtualTableDto levels = new VirtualTableDto(
+            "levels",
+            List.of("level", "min", "max"),
+            List.of(
+                    List.of("good", 60d, 101d),
+                    List.of("bad", 0d, 60d)
+            ));
+
+    CriteriaDto criteria = all(
+            criterion(new AliasedField("score_avg_group"), new TableField("levels.min"), ConditionType.GE),
+            criterion(new AliasedField("score_avg_group"), new TableField("levels.max"), ConditionType.LT));
+
+    QueryDto query = Query.from(subQuery1)
+            .join(levels, JoinType.INNER)
+            .on(criteria)
+            .select(List.of(new TableField("levels.level")), List.of(CountMeasure.INSTANCE))
+            .build();
+    Table result = this.executor.executeQuery(query);
+    Assertions.assertThat(result).containsExactly(
+            List.of("bad", 1L),
+            List.of("good", 2L));
   }
 }
diff --git a/core/src/test/java/io/squashql/util/TestDatabaseQueryCreation.java b/core/src/test/java/io/squashql/util/TestDatabaseQueryCreation.java
index 2adc4e2..364acbe 100644
--- a/core/src/test/java/io/squashql/util/TestDatabaseQueryCreation.java
+++ b/core/src/test/java/io/squashql/util/TestDatabaseQueryCreation.java
@@ -1,17 +1,23 @@
 package io.squashql.util;
 
 import io.squashql.query.*;
+import io.squashql.query.builder.Query;
 import io.squashql.query.dto.GroupColumnSetDto;
 import io.squashql.query.dto.QueryDto;
 import io.squashql.query.dto.TableDto;
 import io.squashql.query.parameter.Parameter;
+import io.squashql.store.Store;
+import io.squashql.type.TableTypedField;
 import org.assertj.core.api.Assertions;
 import org.junit.jupiter.api.Test;
 import org.mockito.Mockito;
 
 import java.util.Collections;
+import java.util.List;
+import java.util.Map;
 
 import static io.squashql.query.TableField.tableField;
+import static io.squashql.query.TableField.tableFields;
 
 public class TestDatabaseQueryCreation {
 
@@ -24,17 +30,6 @@ public class TestDatabaseQueryCreation {
             .hasMessageContaining("table or sub-query was expected");
   }
 
-  @Test
-  void testSubQueryOfSubQuery() {
-    QueryDto subSubQuery = new QueryDto();
-    QueryDto subQuery = new QueryDto().table(subSubQuery);
-    QueryDto queryDto = new QueryDto().table(subQuery);
-
-    Assertions.assertThatThrownBy(() -> new QueryResolver(queryDto, Collections.emptyMap()))
-            .isInstanceOf(IllegalArgumentException.class)
-            .hasMessageContaining("not supported");
-  }
-
   @Test
   void testColumnSetInSubQuery() {
     QueryDto subQuery = new QueryDto().table("table")
@@ -67,4 +62,25 @@ public class TestDatabaseQueryCreation {
             .isInstanceOf(IllegalArgumentException.class)
             .hasMessageContaining("Only measures that can be computed by the underlying database can be used in a sub-query");
   }
+
+  @Test
+  void testUnsupportedMeasureInSubSubQuery() {
+    ComparisonMeasureReferencePosition m = new ComparisonMeasureReferencePosition("alias", ComparisonMethod.DIVIDE, new AggregatedMeasure("p", "price", "sum"), Collections.emptyMap(), ColumnSetKey.GROUP);
+    QueryDto subQuery2 = Query.from("table")
+            .select(tableFields(List.of("a", "b")), List.of(m))
+            .build();
+    QueryDto subQuery1 = Query.from(subQuery2)
+            .select(tableFields(List.of("a")), List.of(Functions.sum("alias2", new AliasedField("alias"))))
+            .build();
+    QueryDto query = Query.from(subQuery1)
+            .select(List.of(), List.of(Functions.sum("alias2", new AliasedField("alias"))))
+            .build();
+
+    Map<String, Store> tables = Map.of("table", new Store("table",
+            List.of(new TableTypedField("table", "a", String.class),
+                    new TableTypedField("table", "b", String.class))));
+    Assertions.assertThatThrownBy(() -> new QueryResolver(query, tables))
+            .isInstanceOf(IllegalArgumentException.class)
+            .hasMessageContaining("Only measures that can be computed by the underlying database can be used in a sub-query");
+  }
 }