[jira] [Created] (CALCITE-3402) Allow RANGE with compoud ORDER BY clause

[benj (Jira)]

benj created CALCITE-3402:
-

 Summary: Allow RANGE with compoud ORDER BY clause
 Key: CALCITE-3402
 URL: https://issues.apache.org/jira/browse/CALCITE-3402
 Project: Calcite
  Issue Type: Improvement
  Components: core
Affects Versions: 1.19.0, 1.18.0
Reporter: benj


It will be very useful to have the capacity to use compound ORDER BY clause 
with RANGE
{code:sql}
apache drill (dfs.tmp)> SELECT a
, last_value(c) OVER(PARTITION BY a ORDER BY c, b DESC RANGE BETWEEN UNBOUNDED 
PRECEDING AND UNBOUNDED FOLLOWING)
FROM (SELECT 1 a, 'b' b, 3 c 
  UNION SELECT 2, 'c', 4 
  UNION SELECT 1, 'c', 4
  /* UNION ... */
 ) x;
Error: VALIDATION ERROR: From line 2, column 56 to line 2, column 60: RANGE 
clause cannot be used with compound ORDER BY clause
{code}
This is possible with some SGBDR like Postgres: 
[https://www.postgresql.org/docs/9.3/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS]



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

[jira] [Created] (CALCITE-3244) Decorrelating sub-queries - Adding Implicit RowID support

[benj (JIRA)]

benj created CALCITE-3244:
-

 Summary: Decorrelating sub-queries - Adding Implicit RowID support
 Key: CALCITE-3244
 URL: https://issues.apache.org/jira/browse/CALCITE-3244
 Project: Calcite
  Issue Type: Improvement
Affects Versions: 1.20.0
Reporter: benj


Some very useful type of requests currently failed with:
{code:java}
SYSTEM ERROR: UnsupportedOperationException: Adding Implicit RowID column is 
not supported for ValuesPrel operator 
{code}
Examples from DRILL-7050:
{code:sql}
select t1.id,
 (select count(t2.id) 
 from (
 select 1 as id 
 union all 
 select 2 as id
 ) t2 where t2.id = t1.id)
 from (
 select 1 as id 
 union all 
 select 2 as id
 ) t1
{code}
{code:sql}
SELECT t,
(SELECT count(*) FROM
 (SELECT split(r,' ') AS r FROM
  (SELECT sub.t AS r)) AS x
 ,LATERAL(SELECT $unnest AS u FROM unnest(x.r))
 /* WHERE ... */) t2
FROM
(SELECT 'unnest is useful' AS t) sub
{code}
 

_Please note that in 1.18 the error for these requests was:_
{code:java}
Error: PLAN ERROR: Cannot convert RexNode to equivalent Drill expression. 
RexNode Class: org.apache.calcite.rex.RexCorrelVariable, RexNode Digest: $cor0
{code}



--
This message was sent by Atlassian JIRA
(v7.6.14#76016)