[jira] [Resolved] (CALCITE-1506) Push OVER Clause to underlying SQL via JDBC adapter

["Julian Hyde (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-1506?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Hyde resolved CALCITE-1506.
----------------------------------
       Resolution: Fixed
    Fix Version/s: 1.11.0

Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/e4fb4c4e. Thanks for the PR, [~tzolov]!

> Push OVER Clause to underlying SQL via JDBC adapter
> ---------------------------------------------------
>
>                 Key: CALCITE-1506
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1506
>             Project: Calcite
>          Issue Type: Bug
>          Components: jdbc-adapter
>    Affects Versions: 1.10.0
>            Reporter: Christian Tzolov
>            Assignee: Julian Hyde
>             Fix For: 1.11.0
>
>
> The jdbc adapter adapter should push down the OVER clause  for all dialects that support window functions. 
> At the moment the Rel to SQL conversion ignores the 'OVER(...)'. The RexOver expression is treated as a plain RexCall and the RexOver#window attribute is not converted into SQL. 
> For example if the following sql query (using Postgres dialect): 
> {code:sql}
> SELECT "id", "device_id", "transaction_value", "account_id", "ts_millis", MAX("ts_millis") OVER(partition by "device_id") as "last_version_number" 
> FROM "HAWQ"."transaction"
> WHERE "device_id" = 1445
> {code}
> is pushed down to the jdbc like this:
> {code:sql}
> SELECT "id", "device_id", "transaction_value", "account_id", "ts_millis", MAX("ts_millis") AS "last_version_number"
> FROM "transaction"
> WHERE "device_id" = 1445
> {code}
> The OVER clause is completely dropped!  Here is the plan:
> {code}
> JdbcToEnumerableConverter
>   JdbcProject(id=[$0], device_id=[$1], transaction_value=[$2], account_id=[$3], ts_millis=[$4], last_version_number=[MAX($4) OVER (PARTITION BY $1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
>     JdbcFilter(condition=[=($1, 1445)])
>       JdbcTableScan(table=[[HAWQ, transaction]])
> {code}



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