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.