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

RelToSqlConverter should not merge two window functions

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • None
    • 1.38.0
    • None

    Description

      For example, test in RelToSqlConverterTest:

      @Test void testNestWindow() { 
      String query = " SELECT RANK() OVER (ORDER BY \"daily_sales\" DESC) AS \"rank1\" " 
      + "FROM ( SELECT \"product_name\", " 
      + "SUM(\"product_id\") OVER (PARTITION BY \"product_name\") AS \"daily_sales\" "
      + "FROM \"product\" ) subquery"; 
      
      String expected0 = "SELECT RANK() OVER (ORDER BY 2 DESC) AS \"$0\"\n" 
      + "FROM \"foodmart\".\"product\""; 
      
      RuleSet rules = RuleSets.ofList(CoreRules.PROJECT_TO_LOGICAL_PROJECT_AND_WINDOW); 
      sql(query).optimize(rules, null) .ok(expected0); 
      }
      

      Plan:

      LogicalWindow(window#0=[window(order by [1 DESC] aggs [RANK()])])
        LogicalProject(product_name=[$3], $1=[$15])
          LogicalWindow(window#0=[window(partition {3} aggs [SUM($1)])])
            JdbcTableScan(table=[[foodmart, product]])
      

      The first window is not correctly converted to SQL

      Attachments

        Issue Links

          Activity

            People

              suibianwanwan33 suibianwanwan
              suibianwanwan33 suibianwanwan
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: