[CALCITE-7242] Implement a rule to eliminate LITERAL_AGG so that other databases can handle it

[iwanttobepowerful]

CALCITE-7242

[iwanttobepowerful]

For example:

select e.deptno, e.deptno < some (select deptno from emp where emp.ename = e.ename) as v from emp as e;

LogicalProject(DEPTNO=[$1], V=[OR(AND(IS TRUE(<($1, $3)), IS NOT TRUE(OR(IS NULL($6), =($4, 0)))), AND(IS TRUE(>($4, $5)), null, IS NOT TRUE(OR(IS NULL($6), =($4, 0))), IS NOT TRUE(<($1, $3))), AND(<($1, $3), IS NOT TRUE(OR(IS NULL($6), =($4, 0))), IS NOT TRUE(<($1, $3)), IS NOT TRUE(>($4, $5))))]), id = 7108
  LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $2)], joinType=[left]), id = 7106
    LogicalProject(ename=[$1], deptno=[$8]), id = 7088
      LogicalTableScan(table=[[scott, emp]]), id = 7017
    LogicalProject(ename=[$0], m=[$2], c=[CASE(IS NOT NULL($3), $3, 0)], d=[CASE(IS NOT NULL($4), $4, 0)], trueLiteral=[$5]), id = 7104
      LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)], joinType=[left]), id = 7102
        LogicalAggregate(group=[{0}]), id = 7093
          LogicalProject(ename=[$1]), id = 7091
            LogicalTableScan(table=[[scott, emp]]), id = 7017
        LogicalAggregate(group=[{0}], m=[MAX($1)], c=[COUNT()], d=[COUNT($1)], trueLiteral=[LITERAL_AGG(true)]), id = 7100
          LogicalProject(ename=[$1], DEPTNO=[$8]), id = 7098
            LogicalFilter(condition=[IS NOT NULL($1)]), id = 7096
              LogicalTableScan(table=[[scott, emp]]), id = 7017
I20251024 08:00:51.245133 767227 RuleEventLogger.java:45] 7b4b20a3988a9436:cf9ea6a600000000] call#1505: Apply rule [ExtractLiteralAgg] to [rel#7116:LogicalAggregate]
I20251024 08:00:51.245357 767227 RuleEventLogger.java:45] 7b4b20a3988a9436:cf9ea6a600000000] call#1506: Apply rule [ExtractLiteralAgg] to [rel#7123:LogicalAggregate]
I20251024 08:00:51.245544 767227 RuleEventLogger.java:54] 7b4b20a3988a9436:cf9ea6a600000000] call#1506: Full plan for rule input [rel#7123:LogicalAggregate]: 
LogicalAggregate(group=[{0}], m=[MAX($1)], c=[COUNT()], d=[COUNT($1)], trueLiteral=[LITERAL_AGG(true)])
  LogicalProject(ename=[$1], DEPTNO=[$8])
    LogicalFilter(condition=[IS NOT NULL($1)])
      LogicalTableScan(table=[[scott, emp]])
I20251024 08:00:51.245569 767227 RuleEventLogger.java:60] 7b4b20a3988a9436:cf9ea6a600000000] call#1506: Rule [ExtractLiteralAgg] produced [rel#7134:LogicalProject]
I20251024 08:00:51.245609 767227 RuleEventLogger.java:62] 7b4b20a3988a9436:cf9ea6a600000000] call#1506: Full plan for [rel#7134:LogicalProject]:
LogicalProject(ename=[$0], m=[$1], c=[$2], d=[$3], trueLiteral=[true])
  LogicalAggregate(group=[{0}], m=[MAX($1)], c=[COUNT()], d=[COUNT($1)])
    LogicalProject(ename=[$1], DEPTNO=[$8])
      LogicalFilter(condition=[IS NOT NULL($1)])
        LogicalTableScan(table=[[scott, emp]])

[xiedeyantu]

For example:

select e.deptno, e.deptno < some (select deptno from emp where emp.ename = e.ename) as v from emp as e;

LogicalProject(DEPTNO=[$1], V=[OR(AND(IS TRUE(<($1, $3)), IS NOT TRUE(OR(IS NULL($6), =($4, 0)))), AND(IS TRUE(>($4, $5)), null, IS NOT TRUE(OR(IS NULL($6), =($4, 0))), IS NOT TRUE(<($1, $3))), AND(<($1, $3), IS NOT TRUE(OR(IS NULL($6), =($4, 0))), IS NOT TRUE(<($1, $3)), IS NOT TRUE(>($4, $5))))]), id = 7108
  LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $2)], joinType=[left]), id = 7106
    LogicalProject(ename=[$1], deptno=[$8]), id = 7088
      LogicalTableScan(table=[[scott, emp]]), id = 7017
    LogicalProject(ename=[$0], m=[$2], c=[CASE(IS NOT NULL($3), $3, 0)], d=[CASE(IS NOT NULL($4), $4, 0)], trueLiteral=[$5]), id = 7104
      LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)], joinType=[left]), id = 7102
        LogicalAggregate(group=[{0}]), id = 7093
          LogicalProject(ename=[$1]), id = 7091
            LogicalTableScan(table=[[scott, emp]]), id = 7017
        LogicalAggregate(group=[{0}], m=[MAX($1)], c=[COUNT()], d=[COUNT($1)], trueLiteral=[LITERAL_AGG(true)]), id = 7100
          LogicalProject(ename=[$1], DEPTNO=[$8]), id = 7098
            LogicalFilter(condition=[IS NOT NULL($1)]), id = 7096
              LogicalTableScan(table=[[scott, emp]]), id = 7017
I20251024 08:00:51.245133 767227 RuleEventLogger.java:45] 7b4b20a3988a9436:cf9ea6a600000000] call#1505: Apply rule [ExtractLiteralAgg] to [rel#7116:LogicalAggregate]
I20251024 08:00:51.245357 767227 RuleEventLogger.java:45] 7b4b20a3988a9436:cf9ea6a600000000] call#1506: Apply rule [ExtractLiteralAgg] to [rel#7123:LogicalAggregate]
I20251024 08:00:51.245544 767227 RuleEventLogger.java:54] 7b4b20a3988a9436:cf9ea6a600000000] call#1506: Full plan for rule input [rel#7123:LogicalAggregate]: 
LogicalAggregate(group=[{0}], m=[MAX($1)], c=[COUNT()], d=[COUNT($1)], trueLiteral=[LITERAL_AGG(true)])
  LogicalProject(ename=[$1], DEPTNO=[$8])
    LogicalFilter(condition=[IS NOT NULL($1)])
      LogicalTableScan(table=[[scott, emp]])
I20251024 08:00:51.245569 767227 RuleEventLogger.java:60] 7b4b20a3988a9436:cf9ea6a600000000] call#1506: Rule [ExtractLiteralAgg] produced [rel#7134:LogicalProject]
I20251024 08:00:51.245609 767227 RuleEventLogger.java:62] 7b4b20a3988a9436:cf9ea6a600000000] call#1506: Full plan for [rel#7134:LogicalProject]:
LogicalProject(ename=[$0], m=[$1], c=[$2], d=[$3], trueLiteral=[true])
  LogicalAggregate(group=[{0}], m=[MAX($1)], c=[COUNT()], d=[COUNT($1)])
    LogicalProject(ename=[$1], DEPTNO=[$8])
      LogicalFilter(condition=[IS NOT NULL($1)])
        LogicalTableScan(table=[[scott, emp]])

Can this log be converted into a test case? Typically, a rule should have one or more test cases in RelOptRulesTest.java for verification. To go a step further, a corresponding end-to-end test can be added in planner.iq to validate the correctness of the result.

[xiedeyantu]

CI failed, message is:

FAILURE   0.0sec, org.apache.calcite.test.LintTest > testLintLog()
    java.lang.AssertionError: 
    Expected: an empty collection
         but: <[invalid git log message 'address comments and add tests'; Message must start with upper-case letter]>
        at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:18)
        at org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:6)
        at org.apache.calcite.test.LintTest.testLintLog(LintTest.java:308)

[sonarqubecloud[bot]]

Quality Gate passed

Issues
1 New issue
0 Accepted issues

Measures
0 Security Hotspots
87.0% Coverage on New Code
0.0% Duplication on New Code

See analysis details on SonarQube Cloud

[github-actions[bot]]

This pull request has been marked as stale due to 30 days of inactivity. It will be closed in 90 days if no further activity occurs. If you think that’s incorrect or this pull request requires a review, please simply write any comment. If closed, you can revive the PR at any time and @mention a reviewer or discuss it on the [email protected] list. Thank you for your contributions.

[mihaibudiu]

What is the status of this PR?

[iwanttobepowerful]

What is the status of this PR?

@mihaibudiu I’ll work on this PR once I’ve finished processing the other several PRs.