[CALCITE-6557] AggregateMergeRule throws 'type mismatch' AssertionError

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 1.37.0
• Fix Version/s: 1.38.0
• Labels: pull-request-available
• Assignee: Julian Hyde
• Reporter: Corvin Kuebler
• Created: 30/Aug/24 06:59
• Updated: 17/Oct/24 22:41
• Resolved: 17/Oct/24 22:41

Description

Hey all!
The following Test in JdbcAdapterTests:

  @Test
  void testSumOfSumWithGroupBy() {
    CalciteAssert.model(JdbcTest.SCOTT_MODEL)
        .query("SELECT\n" +
               "    SUM(DEPTNO)\n" +
               "FROM\n" +
               "    (\n" +
               "        SELECT\n" +
               "            SUM(DEPTNO) AS DEPTNO\n" +
               "        FROM\n" +
               "            \"SCOTT\".\"DEPT\"\n" +
               "        GROUP BY\n" +
               "            DNAME\n" +
               "    )")
        .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
        .runs();
  }

fails with:

java.lang.AssertionError: type mismatch:
aggCall type:
TINYINT NOT NULL
inferred type:
TINYINT
	at org.apache.calcite.util.Litmus.lambda$static$0(Litmus.java:31)
	at org.apache.calcite.plan.RelOptUtil.eq(RelOptUtil.java:2204)
	at org.apache.calcite.rel.core.Aggregate.typeMatchesInferred(Aggregate.java:450)
	at org.apache.calcite.rel.core.Aggregate.<init>(Aggregate.java:177)

Its possible to workaround this issue by adding a COALESCE block
(see

  @Test
  void testSumOfSumWithGroupBy() {
    CalciteAssert.model(JdbcTest.SCOTT_MODEL)
        .query("SELECT\n" +
               "    SUM(DEPTNO)\n" +
               "FROM\n" +
               "    (\n" +
               "        SELECT\n" +
               "            COALESCE(SUM(DEPTNO),0) AS DEPTNO\n" +
               "        FROM\n" +
               "            \"SCOTT\".\"DEPT\"\n" +
               "        GROUP BY\n" +
               "            DNAME\n" +
               "    )")
        .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
        .runs();
  }

)

however I think the statement should also work without the workaround.

Since "TINYINT NOT NULL" is a subset of "TINYINT" the equality check could be made more tolerant?

BR,
Corvin

Issue Links

• is related to: CALCITE-1172 Add rule to flatten two Aggregate operators into one
• links to: GitHub Pull Request #3951