Steven Talbot created CALCITE-3876: -------------------------------------- Summary: 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
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)