Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-3876

RelToSqlConverter should not merge a Project that contains a window function that references a window function in input Project

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.23.0
    • Component/s: jdbc-adapter
    • Labels:
      None

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                swtalbot Steven Talbot
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: