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

["Christian Tzolov (JIRA)" <ji...@apache.org>]

Christian Tzolov created CALCITE-1506:
-----------------------------------------

             Summary: 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


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)