[CALCITE-3876] RelToSqlConverter should not merge a Project that contains a window function that references a window function in input Project

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.23.0
• Component/s: jdbc-adapter
• Assignee: Julian Hyde
• Reporter: Steven Talbot
• Created: 25/Mar/20 23:31
• Updated: 04/Sep/24 16:09
• Resolved: 07/Apr/20 11:33

Description

Here's a test in RelToSqlConverterTest that should (roughly) pass

@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);
}

Instead, we see the result

SELECT ROW_NUMBER() OVER (ORDER BY ROW_NUMBER() OVER (ORDER BY \"product_id\"))\nFROM \"foodmart\".\"product\"

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.

Issue Links

• Blocked: CALCITE-6563 RelToSqlConverter should not merge two window functions
• duplicates: CALCITE-3079 Successive dependent windows cannot be implemented in same expression level
• relates to: CALCITE-3079 Successive dependent windows cannot be implemented in same expression level