[
https://issues.apache.org/jira/browse/CALCITE-7420?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18061473#comment-18061473
]
Julian Hyde commented on CALCITE-7420:
--------------------------------------
Thank you for your support, [~zabetak], [~jensen] and [~mbudiu]. I'll take that
as a green light to start this task.
[~zabetak], I do agree that the set of Quidem commands I have proposed to
accomplish this task is a bit unwieldly. We need to replicate the basic
structure of the RelOptRules fixture - given a query, execute program P1
(possibly empty) to get the plan to a state, dump the 'before' plan, execute
program P2 to take that plan to another state, and dump the 'after' plan. The
test is comprehensible and stable because 'after' differs from 'before' only in
what P2 has done.
So, I propose:
* Add an optional rule-set argument to {{{}!plan{}}}, which affects only that
command
* Allow that rule-set argument to be {{NONE}}, and also allow it be be a
concatenation of rule sets using "{{;}}"
* Because {{!plan}} does not change the global rule set, the {{!ok}} command
will be able to execute the query.
So, the example becomes
{noformat}
# Pull aggregate through union ====================
# Tests that AggregateUnionAggregateRule can convert
# a union of two ’select distinct’ queries to a ’select distinct’
# of a union.
select deptno, job
from (select deptno, job
from emp as e1
group by deptno, job
union all
select deptno, job
from emp as e2
group by deptno, job)
group by deptno, job;
+--------+-----------+
| DEPTNO | JOB |
+--------+-----------+
| 20 | CLERK |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | MANAGER |
| 10 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | CLERK |
| 10 | CLERK |
+--------+-----------+
(9 rows)
!ok
LogicalAggregate(group=[{0, 1}])
LogicalUnion(all=[true])
LogicalAggregate(group=[{0, 1}])
LogicalProject(DEPTNO=[$7], JOB=[$2])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0, 1}])
LogicalProject(DEPTNO=[$7], JOB=[$2])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
!plan "NONE"
LogicalAggregate(group=[{0, 1}])
LogicalUnion(all=[true])
LogicalProject(DEPTNO=[$7], JOB=[$2])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(DEPTNO=[$7], JOB=[$2])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
!plan "NONE; AGGREGATE_UNION_AGGREGATE"
{noformat}
> Convert RelOptRulesTest to Quidem scripts
> -----------------------------------------
>
> Key: CALCITE-7420
> URL: https://issues.apache.org/jira/browse/CALCITE-7420
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Major
>
> Many test cases in RelOptRulesTest are simple - involve just a SQL query, set
> of rules, before and after plan. If these were converted to Quidem scripts
> they would be easier to write (because you are writing just one file), more
> maintainable (because Quidem scripts merge more easily than XML), and could
> potentially be run from another language (which would enable ports of Calcite
> to language such as Rust or Python).
> Here is how one test would look:
> {noformat}
> # Pull aggregate through union ====================
> # Tests that AggregateUnionAggregateRule can convert
> # a union of two ’select distinct’ queries to a ’select distinct’
> # of a union.
> !set rules "AGGREGATE_UNION_AGGREGATE"
> select deptno, job
> from (select deptno, job
> from emp as e1
> group by deptno, job
> union all
> select deptno, job
> from emp as e2
> group by deptno, job)
> group by deptno, job;
> +--------+-----------+
> | DEPTNO | JOB |
> +--------+-----------+
> | 20 | CLERK |
> | 30 | SALESMAN |
> | 20 | MANAGER |
> | 30 | MANAGER |
> | 10 | MANAGER |
> | 20 | ANALYST |
> | 10 | PRESIDENT |
> | 30 | CLERK |
> | 10 | CLERK |
> +--------+-----------+
> (9 rows)
> !ok
> LogicalAggregate(group=[{0, 1}])
> LogicalUnion(all=[true])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$7], JOB=[$2])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$7], JOB=[$2])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> !plan-before
> LogicalAggregate(group=[{0, 1}])
> LogicalUnion(all=[true])
> LogicalProject(DEPTNO=[$7], JOB=[$2])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalProject(DEPTNO=[$7], JOB=[$2])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> !plan-after
> {noformat}
>
> It has the same information as the Java method
> {code:java}
> @Test void testPullAggregateThroughUnion() {
> final String sql = "select deptno, job from"
> + " (select deptno, job from emp as e1"
> + " group by deptno,job"
> + " union all"
> + " select deptno, job from emp as e2"
> + " group by deptno,job)"
> + " group by deptno,job";
> sql(sql)
> .withRule(CoreRules.AGGREGATE_UNION_AGGREGATE)
> .check();
> }
> {code}
> and XML resources
> {code:xml}
> <TestCase name="testPullAggregateThroughUnion">
> <Resource name="sql">
> <![CDATA[select deptno, job from (select deptno, job from emp as e1
> group by deptno,job union all select deptno, job from emp as e2 group by
> deptno,job) group by deptno,job]]>
> </Resource>
> <Resource name="planBefore">
> <![CDATA[
> LogicalAggregate(group=[{0, 1}])
> LogicalUnion(all=[true])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$7], JOB=[$2])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$7], JOB=[$2])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> ]]>
> </Resource>
> {code}
> It uses {{!set rules}} command (added in CALCITE-6335) and would require new
> {{!plan-before}} and {{!plan-after}} commands.
> I suspect that Claude would do a quite good job migrating tests from .java
> and .xml to .iq. I think it could also group tests by subject area (e.g. join
> and aggregate) so that .iq files are ~1,000 lines.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
