Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-5506

RelToSqlConverter should retain the aggregation logic when Project without RexInputRef on the Aggregate

    XMLWordPrintableJSON

Details

    Description

      Here is a sql

      select  l.v as l_cost
      from    (
                  select  0 as v,
                          1 as k
              ) l
      join    (
          select  sum("cost") as v,
                          1 as k
          from    (
              select 1 as "cost"
              union
              select 2 as "cost"
              )
      ) r
      on l.k = r.k 

      Before trimming, the RelNode is 

      LogicalProject(L_COST=[$0])
        LogicalJoin(condition=[=($1, $3)], joinType=[inner])
          LogicalValues(tuples=[[{ 0, 1 }]])
          LogicalProject(V=[$0], K=[1])
            LogicalAggregate(group=[{}], V=[SUM($0)]) <!-- Here is SUM() -->
               LogicalUnion(all=[false])
                LogicalValues(tuples=[[{ 1 }]])
                LogicalValues(tuples=[[{ 2 }]]) 

      After trimming, the RelNode is

      LogicalProject(L_COST=[$0])
        LogicalJoin(condition=[=($1, $2)], joinType=[inner])
          LogicalValues(tuples=[[{ 0, 1 }]])
          LogicalProject(K=[1]) 
            LogicalAggregate(group=[{}], DUMMY=[COUNT()]) <!-- Missing SUM() -->
              LogicalUnion(all=[false])
                LogicalValues(tuples=[[{ 1 }]])
                LogicalValues(tuples=[[{ 2 }]])

      If we convert trimmed RelNode to sql, the sql will be

      SELECT *
      FROM 
        (VALUES (0, 1)) AS "t" ("V", "K")
      INNER JOIN 
        (SELECT 1 AS "K" -- Missing SUM()
         FROM (SELECT *
               FROM (VALUES (1)) AS "t" ("cost")
               UNION
               SELECT *
               FROM (VALUES (2)) AS "t" ("cost")) AS "t2"
        ) AS "t4" 
      ON "t"."K" = "t4"."K" 

      The origin sql only has one row result, but the new sql that be trimmed has two row result.

       

      Attachments

        Issue Links

          Activity

            People

              jiajunbernoulli Jiajun Xie
              jiajunbernoulli Jiajun Xie
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 20m
                  20m