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)
