How can Apache Calcite keep aliases in groupby/having/orderby after validation and optimization?

55 views Asked by At

I use Calcite (v1.30.0) to optimize sql. I found the aliases (see g, cnt) in group by/order by clauses were expanded as expressions like below,

here is my original sql,

select concat(b, '-', cast(c AS char)) as g, COUNT(if(a > 0, b, null)) as cnt 
from d.t 
group by g 
order by cnt 
limit 3 offset 10

console output:

-------------sqlNode--------------
SELECT CONCAT(`b`, '-', CAST(`c` AS CHAR)) AS `g`, COUNT(IF(`a` > 0, `b`, NULL)) AS `cnt`
FROM `d`.`t`
GROUP BY `g`
ORDER BY `cnt`
LIMIT 10, 3
-----------validated-----------
SELECT CONCAT(`b`, '-', CAST(`c` AS CHAR)) AS `g`, COUNT(IF(`a` > 0, `b`, NULL)) AS `cnt`
FROM `d`.`t`
--------- expanded!!! ----------------------
GROUP BY CONCAT(`b`, '-', CAST(`c` AS CHAR))
ORDER BY `cnt`
LIMIT 10, 3
------------relNode-------------
LogicalSort(sort0=[$1], dir0=[ASC], offset=[10], fetch=[3]): rowcount = 1.0, cumulative cost = 4.125, id = 5
  LogicalProject(g=[$0], cnt=[$1]): rowcount = 1.0, cumulative cost = 3.125, id = 4
    LogicalAggregate(group=[{0}], cnt=[COUNT($1)]): rowcount = 1.0, cumulative cost = 2.125, id = 3
      LogicalProject($f0=[CONCAT($1, '-', CAST($2):CHAR(1) NOT NULL)], $f1=[IF(>($0, 0), $1, null:NULL)]): rowcount = 1.0, cumulative cost = 1.0, id = 2
        LogicalTableScan(table=[[d, t]]): rowcount = 1.0, cumulative cost = 0.0, id = 1

------------converted-------------
SELECT CONCAT(`b`, '-', CAST(`c` AS CHAR(1))) AS `g`, COUNT(IF(`a` > 0, `b`, NULL)) AS `cnt`
FROM `d`.`t`
--------- expanded!!! --------------------------
GROUP BY CONCAT(`b`, '-', CAST(`c` AS CHAR(1)))
--------- expanded!!! --------------------------
ORDER BY COUNT(IF(`a` > 0, `b`, NULL)) NULLS LAST
LIMIT 10, 3

part of core code:

        SqlValidator.Config validatorConfig = SqlValidator.Config.DEFAULT
                .withCallRewrite(false)
                .withLenientOperatorLookup(calciteConnectionConfig.lenientOperatorLookup())
                .withConformance(calciteConnectionConfig.conformance())
                .withDefaultNullCollation(calciteConnectionConfig.defaultNullCollation())
                .withIdentifierExpansion(false)
                .withColumnReferenceExpansion(false)
                ;

        SqlValidator validator =
                SqlValidatorUtil.newValidator(SqlStdOperatorTable.instance(), catalogReader, typeFactory,
                        validatorConfig);

        SqlNode validated = validator.validate(sqlNode);
        System.out.println("-----------validated-----------\n" + validated.toSqlString(ClickHouseSqlDialect.DEFAULT));


        HepProgramBuilder builder = new HepProgramBuilder();
        RelOptPlanner relOptPlanner = new HepPlanner(builder.build());
        RelOptCluster cluster = RelOptCluster.create(relOptPlanner, new RexBuilder(typeFactory));


        SqlToRelConverter.Config converterConfig =
                SqlToRelConverter.config().withTrimUnusedFields(true).withExpand(false);

        FrameworkConfig frameworkConfig = Frameworks.newConfigBuilder()
                .build();

        SqlToRelConverter sqlToRelConverter = new SqlToRelConverter(new PlannerImpl(frameworkConfig),
                validator, catalogReader, cluster, StandardConvertletTable.INSTANCE, converterConfig);
        RelNode relNode = sqlToRelConverter.convertQuery(validated, false, true).rel;

        System.out.println("------------relNode-------------\n" + RelOptUtil.toString(relNode, SqlExplainLevel.ALL_ATTRIBUTES));

        SqlNode converted = new RelToSqlConverter(ClickHouseSqlDialect.DEFAULT).visitRoot(relNode).asStatement();
        System.out.println("------------converted-------------\n" + converted.toSqlString(ClickHouseSqlDialect.DEFAULT));

I'm gonna add some rules to the optimizer. How can I keep aliases (see g, cnt) in final sql after validation and optimization?

Thanks.

0

There are 0 answers