I was able to get the test running by adding some code before running the test. The code basically forces Component Supplier to be loaded into the config map. The workaround looks like the following:

@ComponentSupplier(MyComponentSupplier.class)
public class CustomSqlAggregatorTest extends BaseCalciteQueryTest {

    @Test
    public void testCustomAggSql() {
        initializeGuiceConfiguration();
        cannotVectorize();
        testBuilder()
                .sql("select CUSTOM_AGG(m1) from foo")
                .expectedQueries(
                        List.of(
                                Druids.newTimeseriesQueryBuilder()
                                        .dataSource(CalciteTests.DATASOURCE1)
                                        .intervals(querySegmentSpec(Filtration.eternity()))
                                        .granularity(Granularities.ALL)
                                        .aggregators(aggregators(getAggFactory()))
                                        .context(QUERY_CONTEXT_DEFAULT)
                                        .build()
                        )
                )
                .expectedResults(ImmutableList.of(new Object[]{21.0F}))
                .run();
    }
    
    private static void initializeGuiceConfiguration() {
        List<Annotation> annotations = List.of(ArrayWithLimitSqlAggregatorTest.class.getAnnotations());
        queryFrameworkRule.setConfig(new SqlTestFrameworkConfig(annotations));
    }
    ...
}

For some reason @ComponentSupplier annotation doesn't seems to be working properly and I couldn't figure out the configuration needed for it to work as it should.