[jira] [Comment Edited] (CALCITE-800) Window function defined within another window function should be invalid

["Vladimir Sitnikov (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-800?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14630922#comment-14630922 ] 

Vladimir Sitnikov edited comment on CALCITE-800 at 7/17/15 7:47 AM:
--------------------------------------------------------------------

Regarding (4)

4.1) It is likely an application bug if it generates row_number without order by. That does not make much sense in allowing such queries in.
4.2) 
Quote from SQL2003:
{quote}The ROW_NUMBER function computes the sequential row number, starting with 1 (one) for the first row, of the row within its window partition according to the window ordering of the window.{quote}
This assumes "window ordering" exists. Doesn't it?

4.3)
Quote from SQL2003:
{quote}ROW_NUMBER() OVER WNS is equivalent to the <window function>: COUNT \(*) OVER (WNS1 ROWS UNBOUNDED PRECEDING){quote}

When I issue {{count\(*) over (range unbounded preceding)}} PostgreSQL returns ALL values equal. That means that is at least PG bug.

I do not find yet in the spec, however I believe it should require specification of ordering if you provide framing.
Well, there is "If the window ordering clause of a window structure descriptor is absent, then the window ordering is
implementation-dependent.", however that is not a good point to "enable row_number without order by".



was (Author: vladimirsitnikov):
Regarding (4)

4.1) It is likely an application bug if it generates row_number without order by. That does not make much sense in allowing such queries in.
4.2) 
Quote from SQL2003:
{quote}The ROW_NUMBER function computes the sequential row number, starting with 1 (one) for the first row, of the row within its window partition according to the window ordering of the window.{quote}
This assumes "window ordering" exists. Doesn't it?

4.3)
Quote from SQL2003:
{quote}ROW_NUMBER() OVER WNS is equivalent to the <window function>: COUNT (*) OVER (WNS1 ROWS UNBOUNDED PRECEDING){quote}

When I issue {{count(*) over (range unbounded preceding)}} PostgreSQL returns ALL values equal. That means that is at least PG bug.

I do not find yet in the spec, however I believe it should require specification of ordering if you provide framing.
Well, there is "If the window ordering clause of a window structure descriptor is absent, then the window ordering is
implementation-dependent.", however that is not a good point to "enable row_number without order by".


> Window function defined within another window function should be invalid
> ------------------------------------------------------------------------
>
>                 Key: CALCITE-800
>                 URL: https://issues.apache.org/jira/browse/CALCITE-800
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Sean Hsuan-Yi Chu
>            Assignee: Julian Hyde
>
> For instance,
> {code}
> select sum(deptno) over (order by 
> sum(deptno) over(order by deptno)) 
> from emp
> {code}
> This is an invalid query. However, it passes the validation.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)