[jira] [Commented] (CALCITE-3876) Should not combine projects when top project contains window function referencing window function from bottom project

["Feng Zhu (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3876?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17067289#comment-17067289 ] 

Feng Zhu commented on CALCITE-3876:
-----------------------------------

It duplicates with CALCITE-3079, in which the proposed approach is not thorough.

According to the discussion, we need to prevent merging projects when such window expressions are detected.

[~swtalbot], would you like to contribute?

> Should not combine projects when top project contains window function referencing window function from bottom project
> ---------------------------------------------------------------------------------------------------------------------
>
>                 Key: CALCITE-3876
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3876
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Steven Talbot
>            Priority: Major
>
> Here's a test in RelToSqlConverterTest that should (roughly) pass
> {code:java}
> @Test public void testWindowOnWindowDoesNotCombineProjects() {
>  final String query = "SELECT \n" +
>  " ROW_NUMBER() OVER (ORDER BY rn) FROM\n" +
>  " (SELECT *, \n" +
>  " ROW_NUMBER() OVER (ORDER BY \"product_id\") as rn\n" +
>  " FROM \"foodmart\".\"product\")";
>  final String expected = "SELECT \n" +
>  " ROW_NUMBER() OVER (ORDER BY rn) FROM\n" +
>  " (SELECT *, \n" +
>  " ROW_NUMBER() OVER (ORDER BY \"product_id\") as rn\n" +
>  " FROM \"foodmart\".\"product\")";
>  sql(query)
>  .withPostgresql()
>  .ok(expected);
> }{code}
> Instead, we see the result
> {noformat}
> SELECT ROW_NUMBER() OVER (ORDER BY ROW_NUMBER() OVER (ORDER BY \"product_id\"))\nFROM \"foodmart\".\"product\"{noformat}
> That's because multiple steps in the pipeline want to squish those two projects together, but that's not valid to do (at least in Redshift where I tested, but I assume in most other SQL dialects as well). 
> So the expected behavior is that SqlToRelConverter, RelBuilder, and RelToSqlConverter are all knowledgeable enough about window functions to examine the arguments and avoid merging projects (or combining SelectNode[s], in the case of RelToSqlConverter), when one window references another window.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)