title: Difference between "ROWS BETWEEN" and "RANGE BETWEEN" in (Presto) window function "OVER" clause
body: <p><em>This question is primarily about older versions of PrestoSQL, which have been resolved in the (now renamed) Trino project as of versions
  346. However, Amazon's Athena project is based off of Presto versions 0.217 (Athena Engine 2) and 0.172 (Athena Engine 1), which does have the issues described below. This question was written specifically around Athena Engine 1 / PrestoSQL version 0.172</em></p>
  <h1>Questions (tl;dr)</h1>
  <ol>
  <li>What is the difference between <code>ROWS BETWEEN</code> and <code>RANGE BETWEEN</code> in Presto window Functions?
  <ul>
  <li>Are these just synonyms for each other, or are there core conceptual differences?</li>
  <li>If they are just synonyms, why does <code>ROWS BETWEEN</code> allow more options than <code>RANGE BETWEEN</code>?</li>
  </ul>
  </li>
  <li>Is there a query scenario where it's possible to use the exact same parameters on <code>ROWS BETWEEN</code> and <code>RANGE BETWEEN</code> and get different results?
  <ul>
  <li>If using just <code>unbounded</code>/<code>current row</code>, is there a scenario where you'd use <code>RANGE</code> instead of <code>ROWS</code> (or vice-versa)?</li>
  </ul>
  </li>
  <li>Since <code>ROWS</code> has more options, why isn't it mentioned at all in the documentation? o_O</li>
  </ol>
  <h1>Comments</h1>
  <p>The <a href="https://prestodb.io/docs/current/functions/window.html" rel="noreferrer">presto documentation</a> is fairly quiet about even <code>RANGE</code>, and doesn't mention <code>ROWS</code>. I haven't found many discussions or examples around window functions in Presto. I'm starting to set through the Presto code-base to try to figure this out. Hopefully someone can save me from that, and we can improve the documentation together.</p>
  <p>The Presto code has <a href="https://github.com/prestodb/presto/blob/44c044ad827365369f9f255cbb272b3f07c4b30c/presto-parser/src/main/antlr4/com/facebook/presto/sql/parser/SqlBase.g4#L464" rel="noreferrer">a parser</a> and <a href="https://github.com/prestodb/presto/blob/3060c65a1812c6c8b0c2ab725b0184dbad67f0ed/presto-main/src/test/java/com/facebook/presto/operator/window/TestAggregateWindowFunction.java#L193" rel="noreferrer">test cases</a> for the <code>ROWS</code> variant, but there's <a href="https://prestodb.io/docs/current/functions/window.html" rel="noreferrer">no mention in the documentation</a> of <code>ROWS</code>.</p>
  <p>The <a href="https://github.com/prestodb/presto/blob/3060c65a1812c6c8b0c2ab725b0184dbad67f0ed/presto-main/src/test/java/com/facebook/presto/operator/window/TestAggregateWindowFunction.java#L193" rel="noreferrer">test cases</a> I found with both <code>ROWS</code> and <code>RANGE</code> don't test anything different between the two syntaxes.</p>
  <p>They almost look like synonyms, but they do behave differently in my testing, and have different <a href="https://github.com/prestodb/presto/blob/44c044ad827365369f9f255cbb272b3f07c4b30c/presto-main/src/main/java/com/facebook/presto/sql/analyzer/StatementAnalyzer.java#L1514" rel="noreferrer">allowed parameters</a> and <a href="https://github.com/prestodb/presto/blob/fc3d1ee6580ea2cffc495de0fdc357e966564001/presto-main/src/test/java/com/facebook/presto/sql/analyzer/TestAnalyzer.java#L642" rel="noreferrer">validation rules</a>.</p>
  <p>The following examples can be run with the <a href="https://hub.docker.com/r/starburstdata/presto" rel="noreferrer">starburstdata/presto</a> Docker image running Presto 0.213-e-0.1. Typically I run Presto 0.172 through Amazon Athena, and have almost always ended up using <code>ROWS</code>.</p>
  <h1>RANGE</h1>
  <p>RANGE seems to be limited to &quot;UNBOUNDED&quot; and &quot;CURRENT ROW&quot;. The following returns an error:</p>
  <p><em><strong><code>range between 1 preceding and 1 following</code></strong></em></p>
  <pre><code>use tpch.tiny;
  
  select custkey, orderdate,
         array_agg(orderdate) over ( 
             partition by custkey 
             order by orderdate asc 
             range between 1 preceding and 1 following
         ) previous_orders 
  from orders where custkey in (419, 320) and orderdate &lt; date('1996-01-01')
  order by custkey, orderdate asc;
  </code></pre>
  <p><strong>ERROR:</strong>
  <strong><code>Window frame RANGE PRECEDING is only supported with UNBOUNDED</code></strong></p>
  <p>The following range syntaxes do work fine (with expected differing results). <em>All following examples based on the above query, just changing the range</em></p>
  <p><strong><code>range between unbounded preceding and current row</code></strong></p>
  <pre><code> custkey | orderdate  |                             previous_orders
  ---------+------------+--------------------------------------------------------------------------
       320 | 1992-07-10 | [1992-07-10]
       320 | 1992-07-30 | [1992-07-10, 1992-07-30]
       320 | 1994-07-08 | [1992-07-10, 1992-07-30, 1994-07-08]
       320 | 1994-08-04 | [1992-07-10, 1992-07-30, 1994-07-08, 1994-08-04]
       320 | 1994-09-18 | [1992-07-10, 1992-07-30, 1994-07-08, 1994-08-04, 1994-09-18]
       320 | 1994-10-12 | [1992-07-10, 1992-07-30, 1994-07-08, 1994-08-04, 1994-09-18, 1994-10-12]
       419 | 1992-03-16 | [1992-03-16]
       419 | 1993-12-29 | [1992-03-16, 1993-12-29]
       419 | 1995-01-30 | [1992-03-16, 1993-12-29, 1995-01-30]
  </code></pre>
  <p><strong><code>range between current row and unbounded following</code></strong></p>
  <pre><code> custkey | orderdate  |                             previous_orders
  ---------+------------+--------------------------------------------------------------------------
       320 | 1992-07-10 | [1992-07-10, 1992-07-30, 1994-07-08, 1994-08-04, 1994-09-18, 1994-10-12]
       320 | 1992-07-30 | [1992-07-30, 1994-07-08, 1994-08-04, 1994-09-18, 1994-10-12]
       320 | 1994-07-08 | [1994-07-08, 1994-08-04, 1994-09-18, 1994-10-12]
       320 | 1994-08-04 | [1994-08-04, 1994-09-18, 1994-10-12]
       320 | 1994-09-18 | [1994-09-18, 1994-10-12]
       320 | 1994-10-12 | [1994-10-12]
       419 | 1992-03-16 | [1992-03-16, 1993-12-29, 1995-01-30]
       419 | 1993-12-29 | [1993-12-29, 1995-01-30]
       419 | 1995-01-30 | [1995-01-30]
  </code></pre>
  <p><strong>range between unbounded preceding and unbounded following</strong></p>
  <pre><code> custkey | orderdate  |                             previous_orders
  ---------+------------+--------------------------------------------------------------------------
       320 | 1992-07-10 | [1992-07-10, 1992-07-30, 1994-07-08, 1994-08-04, 1994-09-18, 1994-10-12]
       320 | 1992-07-30 | [1992-07-10, 1992-07-30, 1994-07-08, 1994-08-04, 1994-09-18, 1994-10-12]
       320 | 1994-07-08 | [1992-07-10, 1992-07-30, 1994-07-08, 1994-08-04, 1994-09-18, 1994-10-12]
       320 | 1994-08-04 | [1992-07-10, 1992-07-30, 1994-07-08, 1994-08-04, 1994-09-18, 1994-10-12]
       320 | 1994-09-18 | [1992-07-10, 1992-07-30, 1994-07-08, 1994-08-04, 1994-09-18, 1994-10-12]
       320 | 1994-10-12 | [1992-07-10, 1992-07-30, 1994-07-08, 1994-08-04, 1994-09-18, 1994-10-12]
       419 | 1992-03-16 | [1992-03-16, 1993-12-29, 1995-01-30]
       419 | 1993-12-29 | [1992-03-16, 1993-12-29, 1995-01-30]
       419 | 1995-01-30 | [1992-03-16, 1993-12-29, 1995-01-30]
  </code></pre>
  <h1>ROWS</h1>
  <p>The three working examples for <code>RANGE</code> above all work for <code>ROWS</code> and produce identical output.</p>
  <pre><code>rows between unbounded preceding and current row
  rows between current row and unbounded following
  rows between unbounded preceding and unbounded following
  </code></pre>
  <p><em>output omitted - identical to above</em></p>
  <p>However, <code>ROWS</code> allows for far more control, since you can also do the syntax above that fails with <code>range</code>:</p>
  <p><strong><code>rows between 1 preceding and 1 following</code></strong></p>
  <pre><code> custkey | orderdate  |           previous_orders
  ---------+------------+--------------------------------------
       320 | 1992-07-10 | [1992-07-10, 1992-07-30]
       320 | 1992-07-30 | [1992-07-10, 1992-07-30, 1994-07-08]
       320 | 1994-07-08 | [1992-07-30, 1994-07-08, 1994-08-04]
       320 | 1994-08-04 | [1994-07-08, 1994-08-04, 1994-09-18]
       320 | 1994-09-18 | [1994-08-04, 1994-09-18, 1994-10-12]
       320 | 1994-10-12 | [1994-09-18, 1994-10-12]
       419 | 1992-03-16 | [1992-03-16, 1993-12-29]
       419 | 1993-12-29 | [1992-03-16, 1993-12-29, 1995-01-30]
       419 | 1995-01-30 | [1993-12-29, 1995-01-30]
  </code></pre>
  <p><strong><code>rows between current row and 1 following</code></strong></p>
  <pre><code> custkey | orderdate  |     previous_orders
  ---------+------------+--------------------------
       320 | 1992-07-10 | [1992-07-10, 1992-07-30]
       320 | 1992-07-30 | [1992-07-30, 1994-07-08]
       320 | 1994-07-08 | [1994-07-08, 1994-08-04]
       320 | 1994-08-04 | [1994-08-04, 1994-09-18]
       320 | 1994-09-18 | [1994-09-18, 1994-10-12]
       320 | 1994-10-12 | [1994-10-12]
       419 | 1992-03-16 | [1992-03-16, 1993-12-29]
       419 | 1993-12-29 | [1993-12-29, 1995-01-30]
       419 | 1995-01-30 | [1995-01-30]
  </code></pre>
  <p><strong><code>rows between 5 preceding and 2 preceding</code></strong></p>
  <pre><code> custkey | orderdate  |                 previous_orders
  ---------+------------+--------------------------------------------------
       320 | 1992-07-10 | NULL
       320 | 1992-07-30 | NULL
       320 | 1994-07-08 | [1992-07-10]
       320 | 1994-08-04 | [1992-07-10, 1992-07-30]
       320 | 1994-09-18 | [1992-07-10, 1992-07-30, 1994-07-08]
       320 | 1994-10-12 | [1992-07-10, 1992-07-30, 1994-07-08, 1994-08-04]
       419 | 1992-03-16 | NULL
       419 | 1993-12-29 | NULL
       419 | 1995-01-30 | [1992-03-16]
  </code></pre>