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.