[
https://issues.apache.org/jira/browse/CALCITE-3876?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17067904#comment-17067904
]
Steven Talbot edited comment on CALCITE-3876 at 3/26/20, 5:54 PM:
------------------------------------------------------------------
I see. I had not seen *_ProjectToWindowRule_* before. I will have to play
around with it and see if I can use it at all, and then I will have a better
understanding.
But yes, from the discussion on CALCITE-3079 it seems ultimately
ProjectMergeRule will have to be fixed. I don't know if I'll have a chance to
contribute a fix in the next little bit, but if I do I will comment back here.
Or should we close this as a duplicate of CALCITE-3079 ?
was (Author: swtalbot):
I see. I had not seen *_ProjectToWindowRule_* before. I will have to play
around with it and see if I can use it at all, and then I will have a better
understanding.
But yes, from the discussion on CALCITE-3079 it seems ultimately
ProjectMergeRule will have to be fixed. I don't know if I'll have a chance to
contribute a fix in the next little bit, but if I do I will comment back here.
Or should we close this as a duplicate of CALCITE-3079 ?
> 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)
