RelRoot is how Calcite does it. See
https://issues.apache.org/jira/browse/CALCITE-819.
On Fri, Oct 20, 2023 at 10:27 PM Akilis Zhang <zhangxin199...@gmail.com> wrote:
>
> 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.
