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

Window functions Range defaults to CURRENT ROW

    Details

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

      Description

      If you provide a window function without any RANGE, the default lower bound and upper bound seems to be set to CURRENT ROW, where I think it should unbounded preceding and current row.

      However, even if this is implementation specific what the default value is, you cannot tell if the original query didn't provide it since the default was given in Optiq.

      ---------------- Imported from GitHub ----------------
      Url: https://github.com/julianhyde/optiq/issues/284
      Created by: tnachen
      Labels:
      Created at: Sun May 18 13:40:12 CEST 2014
      State: closed

        Activity

        Hide
        github-import GitHub Import added a comment -

        [Date: Mon May 19 06:56:42 CEST 2014, Author: tnachen]

        Yes sorry let me create a new issue.

        Show
        github-import GitHub Import added a comment - [Date: Mon May 19 06:56:42 CEST 2014, Author: tnachen ] Yes sorry let me create a new issue.
        Hide
        github-import GitHub Import added a comment -

        [Date: Mon May 19 00:16:08 CEST 2014, Author: julianhyde]

        It's possible Optiq is doing the wrong thing. See `SqlValidatorTest.testWindowFunctions`, `SqlToRelConverterTest.testOverXxx`.

        I believe that `ROWS 10 PRECEDING` is shorthand for `ROWS BETWEEN 10 PRECEDING AND CURRENT ROW`, so maybe that's where the `CURRENT ROW` is coming from.

        Suggest some use cases: SQL queries and whether they are invalid, or what SQL query they should be equivalent to.

        Also, @tnachen you said a few days ago that a query was giving an NPE but you never logged it.

        Show
        github-import GitHub Import added a comment - [Date: Mon May 19 00:16:08 CEST 2014, Author: julianhyde ] It's possible Optiq is doing the wrong thing. See `SqlValidatorTest.testWindowFunctions`, `SqlToRelConverterTest.testOverXxx`. I believe that `ROWS 10 PRECEDING` is shorthand for `ROWS BETWEEN 10 PRECEDING AND CURRENT ROW`, so maybe that's where the `CURRENT ROW` is coming from. Suggest some use cases: SQL queries and whether they are invalid, or what SQL query they should be equivalent to. Also, @tnachen you said a few days ago that a query was giving an NPE but you never logged it.
        Hide
        github-import GitHub Import added a comment -

        [Date: Sun May 18 22:56:58 CEST 2014, Author: tnachen]

        I actually get an exception from Optiq if I don't specify an order by in a window.

        Is that something Optiq already supports?

        Show
        github-import GitHub Import added a comment - [Date: Sun May 18 22:56:58 CEST 2014, Author: tnachen ] I actually get an exception from Optiq if I don't specify an order by in a window. Is that something Optiq already supports?
        Hide
        github-import GitHub Import added a comment -

        [Date: Sun May 18 14:19:14 CEST 2014, Author: vlsi]

        I guess the default range depends on the presence of `order by`.
        When no order by is given, `unbounded preceding and unbounded following` should appear, however optiq currently does not support `unbounded following`.

        Show
        github-import GitHub Import added a comment - [Date: Sun May 18 14:19:14 CEST 2014, Author: vlsi ] I guess the default range depends on the presence of `order by`. When no order by is given, `unbounded preceding and unbounded following` should appear, however optiq currently does not support `unbounded following`.

          People

          • Assignee:
            Unassigned
            Reporter:
            github-import GitHub Import
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development