Subject: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options

From: Oliver Ford <[email-masked]>
Date: Fri, 13 Jul 2018 13:52:00 +0100

Adds the options RESPECT/IGNORE NULLS (null treatment clause) and FROM
FIRST/LAST to the non-aggregate window functions.

A previous patch
(https://www.postgresql.org/message-id/CA+=[email-masked])
partially implemented this feature. However, that patch worked by
adding the null treatment clause to the window frame's frameOptions
variable, and consequently had the limitation that it wasn't possible
to reuse a window frame definition in a single query where two
functions were called that had different null treatment options. This
meant that the patch was never committed. The attached path takes a
different approach which gets around this limitation.

For example, the following query would not work correctly with the
implementation in the old patch but does with the attached patch:

WITH cte (x) AS (
select null union select 1 union select 2)
SELECT x,
first_value(x) over w as with_default,
first_value(x) respect nulls over w as with_respect,
first_value(x) ignore nulls over w as with_ignore
from cte WINDOW w as (order by x nulls first rows between unbounded
preceding and unbounded following);
 x | with_default | with_respect | with_ignore
---+--------------+--------------+-------------
   |              |              |           1
 1 |              |              |           1
 2 |              |              |           1
(3 rows)

== Implementation ==

The patch adds two types to the pg_type catalog: "ignorenulls" and
"fromlast". These types are of the Boolean category, and work as
wrappers around the bool type. They are used as function arguments to
extra versions of the window functions that take additional boolean
arguments. RESPECT NULLS and FROM FIRST are ignored by the parser, but
IGNORE NULLS and FROM LAST lead to the extra versions being called
with arguments to ignore nulls and order from last.

== Testing ==

Updated documentation and added regression tests. All existing tests
pass. This change will need a catversion bump.
Thanks to Krasiyan Andreev for initially testing this patch.

---

From: David Fetter <[email-masked]>
Date: Sat, 28 Jul 2018 20:59:58 +0200

> FIRST/LAST to the non-aggregate window functions.

Please find attached an updated version for OID drift.

Best,
David.
-- 
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

---

From: Krasiyan Andreev <[email-masked]>
Date: Tue, 18 Sep 2018 15:05:07 +0300

Hi,
Patch applies and compiles, all included tests and building of the docs
pass.
I am using last version from more than two months ago in production
environment with real data and I didn't find any bugs,
so I'm marking this patch as ready for committer in the commitfest app.

---

From: Andrew Gierth <[email-masked]>
Date: Sat, 22 Sep 2018 21:06:32 +0100

>>>>> "Krasiyan" == Krasiyan Andreev <[email-masked]> writes:

 Krasiyan> Hi,

 Krasiyan> Patch applies and compiles, all included tests and building
 Krasiyan> of the docs pass. I am using last version from more than two
 Krasiyan> months ago in production environment with real data and I
 Krasiyan> didn't find any bugs, so I'm marking this patch as ready for
 Krasiyan> committer in the commitfest app.

Unfortunately, reviewing it from a committer perspective - I can't
possibly commit this as it stands, and anything I did to it would be
basically a rewrite of much of it.

Some of the problems could be fixed. For example the type names could be
given pg_* prefixes (it's clearly not acceptable to create random
special-purpose boolean subtypes in pg_catalog and _not_ give them such
a prefix), and the precedence hackery in gram.y could have comments
added (gram.y is already bad enough; _anything_ fancy with precedence
has to be described in the comments). But I don't like that hack with
the special types at all, and I think that needs a better solution.

Normally I'd push hard to try and get some solution that's sufficiently
generic to allow user-defined functions to make use of the feature. But
I think the SQL spec people have managed to make that literally
impossible in this case, what with the FROM keyword appearing in the
middle of a production and not followed by anything sufficiently
distinctive to even use for extra token lookahead.

Also, as has been pointed out in a number of previous features, we're
starting to accumulate identifiers that are reserved in subtly different
ways from our basic four-category system (which is itself a significant
elaboration compared to the spec's simple reserved/unreserved
distinction). As I recall this objection was specifically raised for
CUBE, but justified there by the existence of the contrib/cube extension
(and the fact that the standard CUBE() construct is used only in very
specific places in the syntax). This patch would make lead / lag /
first_value / last_value / nth_value syntactically "special" while not
actually reserving them (beyond having them in unreserved_keywords); I
think serious consideration should be given to whether they should
instead become col_name_keywords (which would, I believe, make it
unnecessary to mess with precedence).

Anyone have any thoughts or comments on the above?

-- 
Andrew (irc:RhodiumToad)

---

From: Tom Lane <[email-masked]>
Date: Sun, 23 Sep 2018 17:13:52 -0400

> distinctive to even use for extra token lookahead.

Yeah.  Is there any appetite for a "Just Say No" approach?  That is,
refuse to implement the spec's syntax on the grounds that it's too
brain-dead to even consider, and instead provide some less random,
more extensibility-friendly way to accomplish the same thing?

The FROM FIRST/LAST bit seems particularly badly thought through,
because AFAICS it is flat out ambiguous with a normal FROM clause
immediately following the window function call.  The only way to
make it not so would be to make FIRST and LAST be fully reserved,
which is neither a good idea nor spec-compliant.

In short, there's a really good case to be made here that the SQL
committee is completely clueless about syntax design, and so we
shouldn't follow this particular pied piper.

> ... This patch would make lead / lag /

I agree that messing with the precedence rules is likely to have
unforeseen and undesirable side-effects.  Generally, if you need
to create a precedence rule, that's because your grammar is
ambiguous.  Precedence fixes that in a well-behaved way only for
cases that actually are very much like operator precedence rules.
Otherwise, you may just be papering over something that isn't
working very well.  See e.g. commits 670a6c7a2 and 12b716457
for past cases where we learned that the hard way.  (The latter
also points out that if you must have a precedence hack, it's
safer to hack individual rules than to stick precedences onto
terminal symbols.)  In the case at hand, since the proposed patch
doesn't make FIRST and LAST be fully reserved, it seems just about
certain that it can be made to misbehave, including failing on
queries that were and should remain legal.

			regards, tom lane

---

From: Andrew Gierth <[email-masked]>
Date: Sun, 23 Sep 2018 23:22:30 +0100

>>>>> "Tom" == Tom Lane <[email-masked]> writes:

 Tom> The FROM FIRST/LAST bit seems particularly badly thought through,
 Tom> because AFAICS it is flat out ambiguous with a normal FROM clause
 Tom> immediately following the window function call. The only way to
 Tom> make it not so would be to make FIRST and LAST be fully reserved,
 Tom> which is neither a good idea nor spec-compliant.

In the actual spec syntax it's not ambiguous at all because NTH_VALUE is
a reserved word (as are LEAD, LAG, FIRST_VALUE and LAST_VALUE), and OVER
is a mandatory clause in its syntax, so a FROM appearing before the OVER
must be part of a FROM FIRST/LAST and not introducing a FROM-clause.

In our syntax, if we made NTH_VALUE etc. a col_name_keyword (and thus
not legal as a function name outside its own special syntax) it would
also become unambiguous.

i.e. given this token sequence (with . marking the current posision):

  select nth_value(x) . from first ignore 

if we know up front that "nth_value" is a window function and not any
other kind of function, we know that we have to shift the "from" rather
than reducing the select-list because we haven't seen an "over" yet.
(Neither "first" nor "ignore" are reserved, so "select foo(x) from first
ignore;" is a valid and complete query, and without reserving the
function name we'd need at least four tokens of lookahead to decide
otherwise.)

This is why I think the col_name_keyword option needs to be given
serious consideration - it still doesn't reserve the names as strongly
as the spec does, but enough to make the standard syntax work without
needing any dubious hacks.

-- 
Andrew (irc:RhodiumToad)

---

From: Tom Lane <[email-masked]>
Date: Sun, 23 Sep 2018 19:10:25 -0400

> must be part of a FROM FIRST/LAST and not introducing a FROM-clause.

Hmm ...

> than reducing the select-list because we haven't seen an "over" yet.

I don't really find that to be a desirable solution, because quite aside
from the extensibility problem, it would mean that a lot of errors
become "syntax error" where we formerly gave a more useful message.

This does open up a thought about how to proceed, though.  I'd been
trying to think of a way to solve this using base_yylex's ability to
do some internal lookahead and change token types based on that.
If you just think of recognizing FROM FIRST/LAST, you get nowhere
because that's still legal in other contexts.  But if you were to
look for FROM followed by FIRST/LAST followed by IGNORE/RESPECT/OVER,
I think that could only validly happen in this syntax.  It'd take
some work to extend base_yylex to look ahead 2 tokens not one, but
I'm sure that could be done.  (You'd also need a lookahead rule to
match "IGNORE/RESPECT NULLS OVER", but that seems just as doable.)
Then the relevant productions use FROM_LA, IGNORE_LA, RESPECT_LA
instead of the corresponding bare tokens, and the grammar no longer
has an ambiguity problem.

			regards, tom lane

---

From: Andrew Gierth <[email-masked]>
Date: Mon, 24 Sep 2018 00:46:40 +0100

>>>>> "Tom" == Tom Lane <[email-masked]> writes:

 Tom> If you just think of recognizing FROM FIRST/LAST, you get nowhere
 Tom> because that's still legal in other contexts. But if you were to
 Tom> look for FROM followed by FIRST/LAST followed by
 Tom> IGNORE/RESPECT/OVER, I think that could only validly happen in
 Tom> this syntax.

No; you need to go four tokens ahead in total, not three. Assuming
nth_value is unreserved, then

  select nth_value(x) from first ignore;

is a valid query that has nth_value(x) as an expression, "first" as a
table name and "ignore" as its alias. Only when you see NULLS after
IGNORE, or OVER after FIRST/LAST, do you know that you're looking at
a window function and not a from clause.

So FROM_LA would have to mean "FROM" followed by any of:

  FIRST IGNORE NULLS
  LAST IGNORE NULLS
  FIRST RESPECT NULLS
  LAST RESPECT NULLS
  FIRST OVER
  LAST OVER

Remember that while OVER is reserved, all of FIRST, LAST, RESPECT and
IGNORE are unreserved.

 Tom> It'd take some work to extend base_yylex to look ahead 2 tokens
 Tom> not one, but I'm sure that could be done. (You'd also need a
 Tom> lookahead rule to match "IGNORE/RESPECT NULLS OVER", but that
 Tom> seems just as doable.) Then the relevant productions use FROM_LA,
 Tom> IGNORE_LA, RESPECT_LA instead of the corresponding bare tokens,
 Tom> and the grammar no longer has an ambiguity problem.

Yeah, but at the cost of having to extend base_yylex to go 3 tokens
ahead (not 2) rather than the current single lookahead slot.

Doable, certainly (probably not much harder to do 3 than 2 actually)

-- 
Andrew (irc:RhodiumToad)

---

From: Tom Lane <[email-masked]>
Date: Sun, 23 Sep 2018 20:20:02 -0400

> table name and "ignore" as its alias.

No, because once IGNORE is a keyword, even unreserved, it's not legal
as an AS-less alias.  We'd be breaking queries like that no matter what.

(I know there are people around here who'd like to remove that
restriction, but it's not happening anytime soon IMO.)

			regards, tom lane

---

From: Andrew Gierth <[email-masked]>
Date: Mon, 24 Sep 2018 01:26:44 +0100

>> select nth_value(x) from first ignore;

 Tom> No, because once IGNORE is a keyword, even unreserved, it's not
 Tom> legal as an AS-less alias.

That rule only applies in the select-list, not in the FROM clause; table
aliases in FROM are just ColId, so they can be anything except a fully
reserved or type_func_name keyword.

-- 
Andrew (irc:RhodiumToad)

---

From: Andrew Gierth <[email-masked]>
Date: Tue, 25 Sep 2018 05:16:27 +0100

So I've tried to rough out a decision tree for the various options on
how this might be implemented (discarding the "use precedence hacks"
option). Opinions? Additions?

(formatted for emacs outline-mode)

* 1. use lexical lookahead

  +: relatively straightforward parser changes
  +: no new reserved words
  +: has the option of working extensibly with all functions

  -: base_yylex needs extending to 3 lookahead tokens

** 1.1. Allow from/ignore clause on all (or all non-agg) window function calls

  If the clauses are legal on all window functions, what to do about existing
  window functions for which the clauses do not make sense?

*** 1.1.1. Ignore the clause when the function isn't aware of it

  +: simple
  -: somewhat surprising for users perhaps?

*** 1.1.2. Change the behavior of the windowapi in some consistent way

  Not sure if this can work.
  +: fairly simple(maybe?) and predictable
  -: changes the behavior of existing window functions

** 1.2. Allow from/ignore clause on only certain functions

  +: avoids any unexpected behavior
  -: needs some way to control what functions allow it

*** 1.2.1. Check the function name in parse analysis against a fixed list.

  +: simple
  -: not extensible

*** 1.2.2. Provide some option in CREATE FUNCTION

  +: extensible
  -: fairly intrusive, adding stuff to create function and pg_proc

*** 1.2.3. Do something magical with function argument types

  +: doesn't need changes in create function / pg_proc
  -: it's an ugly hack

* 2. reserve nth_value etc. as functions

  +: follows the spec reasonably well
  +: less of a hack than extending base_yylex

  -: new reserved words
  -: more parser rules
  -: not extensible

  (now goto 1.2.1)

* 3. "just say no" to the spec

  e.g. add new functions like lead_ignore_nulls(), or add extra boolean
  args to lead() etc. telling them to skip nulls

  +: simple
  -: doesn't conform to spec
  -: using extra args isn't quite the right semantics

-- 
Andrew (irc:RhodiumToad)

---

From: Tom Lane <[email-masked]>
Date: Tue, 25 Sep 2018 10:07:19 -0400

> option). Opinions? Additions?

I think it'd be worth at least drafting an implementation for the
lexical-lookahead fix.  I think it's likely that we'll need to extend
base_yylex to do more lookahead in the future even if we don't do it
for this, given the SQL committee's evident love for COBOL-ish syntax
and lack of regard for what you can do in LALR(1).

The questions of how we interface to the individual window functions
are really independent of how we handle the parsing problem.  My
first inclination is to just pass the flags down to the window functions
(store them in WindowObject and provide some additional inquiry functions
in windowapi.h) and let them deal with it.

>   window functions for which the clauses do not make sense?

Option 1: do nothing, document that nothing happens if w.f. doesn't
implement it.

Option 2: record whether the inquiry functions got called.  At end of
query, error out if they weren't and the options were used.

It's also worth wondering if we couldn't just implement the flags in
some generic fashion and not need to involve the window functions at
all.  FROM LAST, for example, could and perhaps should be implemented
by inverting the sort order.  Possibly IGNORE NULLS could be implemented
inside the WinGetFuncArgXXX functions?  These behaviors might or might
not make much sense with other window functions, but that doesn't seem
like it's our problem.

			regards, tom lane

---

From: Andrew Gierth <[email-masked]>
Date: Thu, 27 Sep 2018 05:40:27 +0100

>> So I've tried to rough out a decision tree for the various options
 >> on how this might be implemented (discarding the "use precedence
 >> hacks" option). Opinions? Additions?

 Tom> I think it'd be worth at least drafting an implementation for the
 Tom> lexical-lookahead fix. I think it's likely that we'll need to
 Tom> extend base_yylex to do more lookahead in the future even if we
 Tom> don't do it for this, given the SQL committee's evident love for
 Tom> COBOL-ish syntax and lack of regard for what you can do in
 Tom> LALR(1).

That's not _quite_ a fair criticism of the SQL committee; they're not
ignoring the capabilities of parsers, they're just not making their
syntax robust in the presence of the kinds of extensions and
generalizations that we want to do.

Without exception that I know of, every time we've run into a problem
that needed ugly precedence rules or extra lookahead it's been caused by
us not reserving something that is reserved in the spec, or by us
allowing constructs that are not in the spec at all (postfix operators,
especially), or by us deliberately generalizing what the spec allows
(e.g. allowing full expressions where the spec only allows column
references, or allowing extra parens around subselects) or where we've
repurposed syntax from the spec in an incompatible way (as in
ANY(array)).

Anyway, for the time being I will mark this patch as "returned with
feedback".

 >> If the clauses are legal on all window functions, what to do about
 >> existing window functions for which the clauses do not make sense?

 Tom> Option 1: do nothing, document that nothing happens if w.f.
 Tom> doesn't implement it.

That was 1.1.1 on my list.

 Tom> Option 2: record whether the inquiry functions got called. At end
 Tom> of query, error out if they weren't and the options were used.

Erroring at the _end_ of the query seems a bit of a potential surprise.

 Tom> It's also worth wondering if we couldn't just implement the flags
 Tom> in some generic fashion and not need to involve the window
 Tom> functions at all.

That was what I meant by option 1.1.2 on my list.

 Tom> FROM LAST, for example, could and perhaps should be implemented by
 Tom> inverting the sort order.

Actually that can't work for reasons brought up in the recent discussion
of optimization of window function sorts: if you change the sort order
you potentially disturb the ordering of peer rows, and the spec requires
that an (nth_value(x,n) from last over w) and (otherfunc(x) over w) for
order-equivalent windows "w" must see the peer rows in the same order.

So FROM LAST really does have to keep the original sort order, and count
backwards from the end of the window.

 Tom> Possibly IGNORE NULLS could be implemented inside the
 Tom> WinGetFuncArgXXX functions? These behaviors might or might not
 Tom> make much sense with other window functions, but that doesn't seem
 Tom> like it's our problem.

That's about what I was thinking for option 1.1.2, yes.

-- 
Andrew (irc:RhodiumToad)

---

From: Andrew Gierth <[email-masked]>
Date: Thu, 27 Sep 2018 05:58:48 +0100

>>>>> "Krasiyan" == Krasiyan Andreev <[email-masked]> writes:

 Krasiyan> I am using last version from more than two months ago in
 Krasiyan> production environment with real data and I didn't find any
 Krasiyan> bugs, so I'm marking this patch as ready for committer in the
 Krasiyan> commitfest app.

Oliver (or anyone else), do you plan to continue working on this in the
immediate future, in line with the comments from myself and Tom in this
thread? If so I'll bump it to the next CF, otherwise I'll mark it
"returned with feedback".

I'm happy to help out with further work on this patch if needed, time
permitting.

-- 
Andrew (irc:RhodiumToad)

---

From: Stephen Frost <[email-masked]>
Date: Thu, 30 Apr 2020 14:58:55 -0400

Greetings,

This seems to have died out, and that's pretty unfortunate because this
is awfully useful SQL standard syntax that people look for and wish we
had.

>   -: base_yylex needs extending to 3 lookahead tokens

This sounds awful grotty and challenging to do and get right, and the
alternative (just reserving these, as the spec does) doesn't seem so
draconian as to be that much of an issue.

>   +: less of a hack than extending base_yylex

For my 2c, at least, reserving these strikes me as entirely reasonable.
Yes, it sucks that we have to partially-reserve some additional
keywords, but such is life.  I get that we'll throw syntax errors
sometimes when we might have given a better error, but I think we can
accept that.

>   (now goto 1.2.1)

Hmm, not sure this was right?  but sure, I'll try...

> *** 1.2.1. Check the function name in parse analysis against a fixed list.

Seems like this is more-or-less required since we'd be reserving them..?

>   -: fairly intrusive, adding stuff to create function and pg_proc

How would this work though, if we reserve the functions as keywords..?
Maybe I'm not entirely following, but wouldn't attempts to use other
functions end up with syntax errors in at least some of the cases,
meaning that having other functions support this wouldn't really work?
I don't particularly like the idea that some built-in functions would
always work but others would work but only some of the time.

>   +: doesn't need changes in create function / pg_proc

Not really a fan of 'ugly hack'.

>   -: using extra args isn't quite the right semantics

Ugh, no thank you.

Thanks!

Stephen

---

From: Krasiyan Andreev <[email-masked]>
Date: Thu, 30 Apr 2020 22:50:00 +0300

Thank you very much for feedback and yes, that is very useful SQL syntax.
Maybe you miss my previous answer, but you are right, that patch is
currently dead,
because some important design questions must be discussed here, before
patch rewriting.

I have dropped support of from first/last for nth_value(),
but also I reimplemented it in a different way,
by using negative number for the position argument, to be able to get the
same frame in exact reverse order.
After that patch becomes much more simple and some concerns about
precedence hack has gone.

I have not renamed special bool type "ignorenulls"
(I know that it is not acceptable way for calling extra version
of window functions, but also it makes things very easy and it can reuse
frames),
but I removed the other special bool type "fromlast".

Attached file was for PostgreSQL 13 (master git branch, last commit fest),
everything was working and patch was at the time in very good shape, all
tests was passed.

I read previous review and suggestions from Tom about special bool type and
unreserved keywords and also,
that IGNORE NULLS could be implemented inside the WinGetFuncArgXXX
functions,
but I am not sure how exactly to proceed (some example will be very
helpful).

> >   -: using extra args isn't quite the right semantics
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 28035f1635..3d73c96891 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -15702,7 +15702,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
        <function>
          lag(<replaceable class="parameter">value</replaceable> <type>anyelement</type>
              [, <replaceable class="parameter">offset</replaceable> <type>integer</type>
-             [, <replaceable class="parameter">default</replaceable> <type>anyelement</type> ]])
+             [, <replaceable class="parameter">default</replaceable> <type>anyelement</type> ]]) [null_treatment]
        </function>
       </entry>
       <entry>
@@ -15731,7 +15731,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
        <function>
          lead(<replaceable class="parameter">value</replaceable> <type>anyelement</type>
               [, <replaceable class="parameter">offset</replaceable> <type>integer</type>
-              [, <replaceable class="parameter">default</replaceable> <type>anyelement</type> ]])
+              [, <replaceable class="parameter">default</replaceable> <type>anyelement</type> ]]) [null_treatment]
        </function>
       </entry>
       <entry>
@@ -15757,7 +15757,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
        <indexterm>
         <primary>first_value</primary>
        </indexterm>
-       <function>first_value(<replaceable class="parameter">value</replaceable> <type>any</type>)</function>
+       <function>first_value(<replaceable class="parameter">value</replaceable> <type>any</type>) [null_treatment]</function>
       </entry>
       <entry>
        <type>same type as <replaceable class="parameter">value</replaceable></type>
@@ -15773,7 +15773,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
        <indexterm>
         <primary>last_value</primary>
        </indexterm>
-       <function>last_value(<replaceable class="parameter">value</replaceable> <type>any</type>)</function>
+       <function>last_value(<replaceable class="parameter">value</replaceable> <type>any</type>) [null_treatment]</function>
       </entry>
       <entry>
        <type>same type as <replaceable class="parameter">value</replaceable></type>
@@ -15790,7 +15790,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
         <primary>nth_value</primary>
        </indexterm>
        <function>
-         nth_value(<replaceable class="parameter">value</replaceable> <type>any</type>, <replaceable class="parameter">nth</replaceable> <type>integer</type>)
+         nth_value(<replaceable class="parameter">value</replaceable> <type>any</type>, <replaceable class="parameter">nth</replaceable> <type>integer</type>) [null_treatment]
        </function>
       </entry>
       <entry>
@@ -15806,6 +15806,16 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
    </tgroup>
   </table>
 
+  <para>
+   In <xref linkend="functions-window-table"/>, <replaceable>null_treatment</replaceable> is one of:
+   <synopsis>
+     RESPECT NULLS
+     IGNORE NULLS
+   </synopsis>
+   <literal>RESPECT NULLS</literal> specifies the default behavior to include nulls in the result.
+   <literal>IGNORE NULLS</literal> ignores any null values when determining a result.
+  </para>
+
   <para>
    All of the functions listed in
    <xref linkend="functions-window-table"/> depend on the sort ordering
@@ -15843,17 +15853,11 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
 
   <note>
    <para>
-    The SQL standard defines a <literal>RESPECT NULLS</literal> or
-    <literal>IGNORE NULLS</literal> option for <function>lead</function>, <function>lag</function>,
-    <function>first_value</function>, <function>last_value</function>, and
-    <function>nth_value</function>.  This is not implemented in
-    <productname>PostgreSQL</productname>: the behavior is always the
-    same as the standard's default, namely <literal>RESPECT NULLS</literal>.
-    Likewise, the standard's <literal>FROM FIRST</literal> or <literal>FROM LAST</literal>
-    option for <function>nth_value</function> is not implemented: only the
-    default <literal>FROM FIRST</literal> behavior is supported.  (You can achieve
-    the result of <literal>FROM LAST</literal> by reversing the <literal>ORDER BY</literal>
-    ordering.)
+    The SQL standard defines a <literal>FROM FIRST</literal> or <literal>FROM LAST</literal>
+    option for <function>nth_value</function>. This is not implemented in <productname>PostgreSQL</productname>:
+    only the default <literal>FROM FIRST</literal> behavior is supported.
+    (You can achieve the result of <literal>FROM LAST</literal> by using negative number for the position argument,
+    as is done in many languages to indicate a <literal>FROM END</literal> index.)
    </para>
   </note>
 
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 9f840ddfd2..a355e379e7 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -508,9 +508,9 @@ T612	Advanced OLAP operations			YES
 T613	Sampling			YES	
 T614	NTILE function			YES	
 T615	LEAD and LAG functions			YES	
-T616	Null treatment option for LEAD and LAG functions			NO	
+T616	Null treatment option for LEAD and LAG functions			YES	
 T617	FIRST_VALUE and LAST_VALUE function			YES	
-T618	NTH_VALUE function			NO	function exists, but some options missing
+T618	NTH_VALUE function			YES	FROM LAST is supported by using negative number for the position argument
 T619	Nested window functions			NO	
 T620	WINDOW clause: GROUPS option			YES	
 T621	Enhanced numeric functions			YES	
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 96e7fdbcfe..db369b1f9a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -154,6 +154,7 @@ static Node *makeBitStringConst(char *str, int location);
 static Node *makeNullAConst(int location);
 static Node *makeAConst(Value *v, int location);
 static Node *makeBoolAConst(bool state, int location);
+static Node *makeTypedBoolAConst(bool state, char *type, int location);
 static RoleSpec *makeRoleSpec(RoleSpecType type, int location);
 static void check_qualified_name(List *names, core_yyscan_t yyscanner);
 static List *check_func_name(List *names, core_yyscan_t yyscanner);
@@ -569,7 +570,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>	xml_namespace_list
 %type <target>	xml_namespace_el
 
-%type <node>	func_application func_expr_common_subexpr
+%type <node>	func_application func_expr_common_subexpr func_expr_respect_ignore
 %type <node>	func_expr func_expr_windowless
 %type <node>	common_table_expr
 %type <with>	with_clause opt_with_clause
@@ -577,6 +578,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 %type <list>	within_group_clause
 %type <node>	filter_clause
+%type <ival>	null_treatment_clause
 %type <list>	window_clause window_definition_list opt_partition_clause
 %type <windef>	window_definition over_clause window_specification
 				opt_frame_clause frame_extent frame_bound
@@ -642,14 +644,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	EXCLUDE EXCLUDING EXCLUSIVE EXECUTE EXISTS EXPLAIN EXPRESSION
 	EXTENSION EXTERNAL EXTRACT
 
-	FALSE_P FAMILY FETCH FILTER FIRST_P FLOAT_P FOLLOWING FOR
+	FALSE_P FAMILY FETCH FILTER FIRST_P FIRST_VALUE FLOAT_P FOLLOWING FOR
 	FORCE FOREIGN FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS
 
 	GENERATED GLOBAL GRANT GRANTED GREATEST GROUP_P GROUPING GROUPS
 
 	HANDLER HAVING HEADER_P HOLD HOUR_P
 
-	IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
+	IDENTITY_P IF_P IGNORE_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE
 	INCLUDING INCREMENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P
 	INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER
 	INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION
@@ -658,14 +660,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 	KEY
 
-	LABEL LANGUAGE LARGE_P LAST_P LATERAL_P
-	LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL
+	LABEL LAG LANGUAGE LARGE_P LAST_P LAST_VALUE LATERAL_P
+	LEAD LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL
 	LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED
 
 	MAPPING MATCH MATERIALIZED MAXVALUE METHOD MINUTE_P MINVALUE MODE MONTH_P MOVE
 
 	NAME_P NAMES NATIONAL NATURAL NCHAR NEW NEXT NO NONE
-	NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
+	NOT NOTHING NOTIFY NOTNULL NOWAIT NTH_VALUE NULL_P NULLIF
 	NULLS_P NUMERIC
 
 	OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR
@@ -680,7 +682,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 	RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REFERENCING
 	REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
-	RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
+	RESET RESPECT RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
 	ROUTINE ROUTINES ROW ROWS RULE
 
 	SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES
@@ -724,6 +726,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 /* Precedence: lowest to highest */
 %nonassoc	SET				/* see relation_expr_opt_alias */
+%nonassoc	FIRST_VALUE LAG LAST_VALUE LEAD NTH_VALUE
 %left		UNION EXCEPT
 %left		INTERSECT
 %left		OR
@@ -13769,6 +13772,10 @@ func_application: func_name '(' ')'
 				}
 		;
 
+null_treatment_clause:
+			RESPECT NULLS_P			{ $$ = 0; }
+			| IGNORE_P NULLS_P		{ $$ = WINFUNC_OPT_IGNORE_NULLS; }
+		;
 
 /*
  * func_expr and its cousin func_expr_windowless are split out from c_expr just
@@ -13816,8 +13823,133 @@ func_expr: func_application within_group_clause filter_clause over_clause
 				}
 			| func_expr_common_subexpr
 				{ $$ = $1; }
+			| func_expr_respect_ignore over_clause
+				{
+					FuncCall *n = (FuncCall *) $1;
+					n->over = $2;
+					$$ = (Node *) n;
+				}
+		;
+
+func_expr_respect_ignore:
+			FIRST_VALUE '(' func_arg_list opt_sort_clause ')' null_treatment_clause
+				{
+					FuncCall *n;
+					List *l = $3;
+					int winFuncArgs = $6;
+
+					/* Convert Ignore Nulls option to bool */
+					if (winFuncArgs & WINFUNC_OPT_IGNORE_NULLS)
+						l = lappend(l, makeTypedBoolAConst(true, "ignorenulls", @2));
+
+					n = makeFuncCall(list_make1(makeString("first_value")), l, @1);
+					n->agg_order = $4;
+					$$ = (Node *) n;
+				}
+			| FIRST_VALUE '(' func_arg_list opt_sort_clause ')'
+				{
+					FuncCall *n;
+					List *l = $3;
+
+					n = makeFuncCall(list_make1(makeString("first_value")), l, @1);
+					n->agg_order = $4;
+					$$ = (Node *) n;
+				}
+			| LAG '(' func_arg_list opt_sort_clause ')' null_treatment_clause
+				{
+					FuncCall *n;
+					List *l = $3;
+					int winFuncArgs = $6;
+
+					/* Convert Ignore Nulls option to bool */
+					if (winFuncArgs & WINFUNC_OPT_IGNORE_NULLS)
+						l = lappend(l, makeTypedBoolAConst(true, "ignorenulls", @2));
+
+					n = makeFuncCall(list_make1(makeString("lag")), l, @1);
+					n->agg_order = $4;
+					$$ = (Node *) n;
+				}
+			| LAG '(' func_arg_list opt_sort_clause ')'
+				{
+					FuncCall *n;
+					List *l = $3;
+
+					n = makeFuncCall(list_make1(makeString("lag")), l, @1);
+					n->agg_order = $4;
+					$$ = (Node *) n;
+				}
+			| LAST_VALUE '(' func_arg_list opt_sort_clause ')' null_treatment_clause
+				{
+					FuncCall *n;
+					List *l = $3;
+					int winFuncArgs = $6;
+
+					/* Convert Ignore Nulls option to bool */
+					if (winFuncArgs & WINFUNC_OPT_IGNORE_NULLS)
+						l = lappend(l, makeTypedBoolAConst(true, "ignorenulls", @2));
+
+					n = makeFuncCall(list_make1(makeString("last_value")), l, @1);
+					n->agg_order = $4;
+					$$ = (Node *) n;
+				}
+			| LAST_VALUE '(' func_arg_list opt_sort_clause ')'
+				{
+					FuncCall *n;
+					List *l = $3;
+
+					n = makeFuncCall(list_make1(makeString("last_value")), l, @1);
+					n->agg_order = $4;
+					$$ = (Node *) n;
+				}
+			| LEAD '(' func_arg_list opt_sort_clause ')' null_treatment_clause
+				{
+					FuncCall *n;
+					List *l = $3;
+					int winFuncArgs = $6;
+
+					/* Convert Ignore Nulls option to bool */
+					if (winFuncArgs & WINFUNC_OPT_IGNORE_NULLS)
+						l = lappend(l, makeTypedBoolAConst(true, "ignorenulls", @2));
+
+					n = makeFuncCall(list_make1(makeString("lead")), l, @1);
+					n->agg_order = $4;
+					$$ = (Node *) n;
+				}
+			| LEAD '(' func_arg_list opt_sort_clause ')'
+				{
+					FuncCall *n;
+					List *l = $3;
+
+					n = makeFuncCall(list_make1(makeString("lead")), l, @1);
+					n->agg_order = $4;
+					$$ = (Node *) n;
+				}
+			| NTH_VALUE '(' func_arg_list opt_sort_clause ')' null_treatment_clause
+				{
+					FuncCall *n;
+					List *l = $3;
+					int winFuncArgs = $6;
+
+					/* Convert Nulls option to bool */
+					if (winFuncArgs & WINFUNC_OPT_IGNORE_NULLS)
+						l = lappend(l, makeTypedBoolAConst(true, "ignorenulls", @2));
+
+					n = makeFuncCall(list_make1(makeString("nth_value")), l, @1);
+					n->agg_order = $4;
+					$$ = (Node *) n;
+				}
+			| NTH_VALUE '(' func_arg_list opt_sort_clause ')'
+				{
+					FuncCall *n;
+					List *l = $3;
+
+					n = makeFuncCall(list_make1(makeString("nth_value")), l, @1);
+					n->agg_order = $4;
+					$$ = (Node *) n;
+				}
 		;
 
+
 /*
  * As func_expr but does not accept WINDOW functions directly
  * (but they can still be contained in arguments for functions etc).
@@ -15225,6 +15357,7 @@ unreserved_keyword:
 			| FAMILY
 			| FILTER
 			| FIRST_P
+			| FIRST_VALUE
 			| FOLLOWING
 			| FORCE
 			| FORWARD
@@ -15240,6 +15373,7 @@ unreserved_keyword:
 			| HOUR_P
 			| IDENTITY_P
 			| IF_P
+			| IGNORE_P
 			| IMMEDIATE
 			| IMMUTABLE
 			| IMPLICIT_P
@@ -15260,9 +15394,12 @@ unreserved_keyword:
 			| ISOLATION
 			| KEY
 			| LABEL
+			| LAG
 			| LANGUAGE
 			| LARGE_P
 			| LAST_P
+			| LAST_VALUE
+			| LEAD
 			| LEAKPROOF
 			| LEVEL
 			| LISTEN
@@ -15290,6 +15427,7 @@ unreserved_keyword:
 			| NOTHING
 			| NOTIFY
 			| NOWAIT
+			| NTH_VALUE
 			| NULLS_P
 			| OBJECT_P
 			| OF
@@ -15341,6 +15479,7 @@ unreserved_keyword:
 			| REPLACE
 			| REPLICA
 			| RESET
+			| RESPECT
 			| RESTART
 			| RESTRICT
 			| RETURNS
@@ -15815,6 +15954,15 @@ makeAConst(Value *v, int location)
  */
 static Node *
 makeBoolAConst(bool state, int location)
+{
+	return makeTypedBoolAConst(state, "bool", location);
+}
+
+/* makeTypedBoolAConst()
+ * Create an A_Const string node from a boolean and store inside the specified type.
+ */
+static Node *
+makeTypedBoolAConst(bool state, char *type, int location)
 {
 	A_Const *n = makeNode(A_Const);
 
@@ -15822,7 +15970,7 @@ makeBoolAConst(bool state, int location)
 	n->val.val.str = (state ? "t" : "f");
 	n->location = location;
 
-	return makeTypeCast((Node *)n, SystemTypeName("bool"), -1);
+	return makeTypeCast((Node *)n, SystemTypeName(type), -1);
 }
 
 /* makeRoleSpec
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 158784474d..ae7e821ab2 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9437,6 +9437,7 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
 	int			nargs;
 	List	   *argnames;
 	ListCell   *l;
+	bool		ignorenulls = false;
 
 	if (list_length(wfunc->args) > FUNC_MAX_ARGS)
 		ereport(ERROR,
@@ -9463,7 +9464,32 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
 	if (wfunc->winstar)
 		appendStringInfoChar(buf, '*');
 	else
-		get_rule_expr((Node *) wfunc->args, context, true);
+	{
+		ListCell	*arglist;
+		Node *argnode = (Node *) wfunc->args;
+
+		get_rule_expr(argnode, context, true);
+
+		/* Determine if IGNORE NULLS should be appended */
+		foreach(arglist, (List *) argnode)
+		{
+			Node *arg = (Node *) lfirst(arglist);
+			if (nodeTag(arg) == T_Const)
+			{
+				Const *constnode = (Const *) arg;
+				if (constnode->consttype == IGNORENULLSOID)
+				{
+					/* parser does not save RESPECT NULLS arguments */
+					ignorenulls = true;
+					buf->len -= 2;
+				}
+			}
+		}
+        }
+
+	appendStringInfoChar(buf, ')');
+	if (ignorenulls)
+		appendStringInfoString(buf, " IGNORE NULLS");
 
 	if (wfunc->aggfilter != NULL)
 	{
@@ -9471,7 +9497,7 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
 		get_rule_expr((Node *) wfunc->aggfilter, context, false);
 	}
 
-	appendStringInfoString(buf, ") OVER ");
+	appendStringInfoString(buf, " OVER ");
 
 	foreach(l, context->windowClause)
 	{
@@ -9649,6 +9675,10 @@ get_const_expr(Const *constval, deparse_context *context, int showtype)
 				appendStringInfoString(buf, "false");
 			break;
 
+		case IGNORENULLSOID:
+			showtype = -1;
+			break;
+
 		default:
 			simple_quote_literal(buf, extval);
 			break;
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index f0c8ae686d..bc639b1883 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -38,7 +38,12 @@ typedef struct
 static bool rank_up(WindowObject winobj);
 static Datum leadlag_common(FunctionCallInfo fcinfo,
 							bool forward, bool withoffset, bool withdefault);
-
+static Datum leadlag_common_ignore_nulls(FunctionCallInfo fcinfo,
+							bool forward, bool withoffset, bool withdefault);
+static Datum
+window_nth_value_ignorenulls_common(FunctionCallInfo fcinfo, int32 nth);
+static Datum
+window_nth_value_respectnulls_common(FunctionCallInfo fcinfo, int32 nth);
 
 /*
  * utility routine for *_rank functions.
@@ -328,6 +333,79 @@ leadlag_common(FunctionCallInfo fcinfo,
 	PG_RETURN_DATUM(result);
 }
 
+static Datum
+leadlag_common_ignore_nulls(FunctionCallInfo fcinfo,
+			    bool forward, bool withoffset, bool withdefault)
+{
+	WindowObject winobj = PG_WINDOW_OBJECT();
+	int32           offset;
+	Datum           result;
+	bool            isnull;
+	bool            isout = false;
+	int32           notnull_offset = 0, tmp_offset = 0;
+
+	if (withoffset)
+	{
+		offset = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
+		if (isnull)
+			PG_RETURN_NULL();
+		if (offset < 0)
+		{
+			offset = abs(offset);
+			forward = !forward;
+		} else if (offset == 0)
+		{
+			result = WinGetFuncArgInPartition(winobj, 0, 0,
+									    WINDOW_SEEK_CURRENT,
+									    false,
+									    &isnull, &isout);
+			if (isnull || isout)
+				PG_RETURN_NULL();
+			else
+				PG_RETURN_DATUM(result);
+		}
+	}
+	else
+		offset = 1;
+
+	while (notnull_offset < offset)
+	{
+		tmp_offset++;
+		result = WinGetFuncArgInPartition(winobj, 0,
+									    (forward ? tmp_offset : -tmp_offset),
+									    WINDOW_SEEK_CURRENT,
+									    false,
+									    &isnull, &isout);
+		if (isout)
+			goto out_of_frame;
+		else if (!isnull)
+			notnull_offset++;
+	}
+
+	result = WinGetFuncArgInPartition(winobj, 0,
+									    (forward ? tmp_offset : -tmp_offset),
+									    WINDOW_SEEK_CURRENT,
+									    false,
+									    &isnull, &isout);
+	if (isout)
+		goto out_of_frame;
+	else
+		PG_RETURN_DATUM(result);
+
+	out_of_frame:
+	/*
+	* target row is out of the partition; supply default value if
+	* provided. Otherwise return NULL.
+	*/
+	if (withdefault)
+	{
+		result = WinGetFuncArgCurrent(winobj, 2, &isnull);
+		PG_RETURN_DATUM(result);
+	}
+	else
+		PG_RETURN_NULL();
+}
+
 /*
  * lag
  * returns the value of VE evaluated on a row that is 1
@@ -363,6 +441,24 @@ window_lag_with_offset_and_default(PG_FUNCTION_ARGS)
 	return leadlag_common(fcinfo, false, true, true);
 }
 
+Datum
+window_lag_nulls_opt(PG_FUNCTION_ARGS)
+{
+	return leadlag_common_ignore_nulls(fcinfo, false, false, false);
+}
+
+Datum
+window_lag_with_offset_nulls_opt(PG_FUNCTION_ARGS)
+{
+	return leadlag_common_ignore_nulls(fcinfo, false, true, false);
+}
+
+Datum
+window_lag_with_offset_and_default_nulls_opt(PG_FUNCTION_ARGS)
+{
+	return leadlag_common_ignore_nulls(fcinfo, false, true, true);
+}
+
 /*
  * lead
  * returns the value of VE evaluated on a row that is 1
@@ -398,6 +494,24 @@ window_lead_with_offset_and_default(PG_FUNCTION_ARGS)
 	return leadlag_common(fcinfo, true, true, true);
 }
 
+Datum
+window_lead_nulls_opt(PG_FUNCTION_ARGS)
+{
+	return leadlag_common_ignore_nulls(fcinfo, true, false, false);
+}
+
+Datum
+window_lead_with_offset_nulls_opt(PG_FUNCTION_ARGS)
+{
+	return leadlag_common_ignore_nulls(fcinfo, true, true, false);
+}
+
+Datum
+window_lead_with_offset_and_default_nulls_opt(PG_FUNCTION_ARGS)
+{
+	return leadlag_common_ignore_nulls(fcinfo, true, true, true);
+}
+
 /*
  * first_value
  * return the value of VE evaluated on the first row of the
@@ -419,6 +533,31 @@ window_first_value(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(result);
 }
 
+Datum
+window_first_value_nulls_opt(PG_FUNCTION_ARGS)
+{
+	WindowObject winobj = PG_WINDOW_OBJECT();
+	Datum		result;
+	bool		isnull,
+				isout;
+	int64		pos;
+
+	isout = false;
+	pos = 0;
+
+	while (!isout)
+	{
+		result = WinGetFuncArgInFrame(winobj, 0,
+							    pos, WINDOW_SEEK_HEAD, false,
+							    &isnull, &isout);
+	if (!isnull)
+		PG_RETURN_DATUM(result);
+	pos++;
+	}
+
+	PG_RETURN_NULL();
+}
+
 /*
  * last_value
  * return the value of VE evaluated on the last row of the
@@ -440,35 +579,149 @@ window_last_value(PG_FUNCTION_ARGS)
 	PG_RETURN_DATUM(result);
 }
 
+Datum
+window_last_value_nulls_opt(PG_FUNCTION_ARGS)
+{
+	WindowObject winobj = PG_WINDOW_OBJECT();
+	Datum		result;
+	bool		isnull,
+				isout;
+	int64		pos;
+
+	isout = false;
+	pos = 0;
+
+	while (!isout)
+	{
+		result = WinGetFuncArgInFrame(winobj, 0,
+								    pos, WINDOW_SEEK_TAIL, false,
+								    &isnull, &isout);
+		if (!isnull)
+			PG_RETURN_DATUM(result);
+		pos--;
+	}
+
+	PG_RETURN_NULL();
+}
+
 /*
  * nth_value
  * return the value of VE evaluated on the n-th row from the first
  * row of the window frame, per spec.
  */
-Datum
-window_nth_value(PG_FUNCTION_ARGS)
+static Datum
+window_nth_value_respectnulls_common(FunctionCallInfo fcinfo, int32 nth)
 {
 	WindowObject winobj = PG_WINDOW_OBJECT();
 	bool		const_offset;
 	Datum		result;
 	bool		isnull;
-	int32		nth;
+	bool		fromlast;
 
 	nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
 	if (isnull)
 		PG_RETURN_NULL();
 	const_offset = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
 
-	if (nth <= 0)
+	if (nth == 0)
 		ereport(ERROR,
-				(errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
-				 errmsg("argument of nth_value must be greater than zero")));
+		(errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
+		errmsg("argument of nth_value must be greater or less than zero")));
+	else if (nth < 0)
+	{
+		nth = abs(nth);
+		fromlast = true;
+	}
+	else
+		fromlast = false;
+
+	result = WinGetFuncArgInFrame(winobj,
+								0,
+								nth - 1,
+								fromlast ? WINDOW_SEEK_TAIL : WINDOW_SEEK_HEAD, const_offset,
+								&isnull,
+								NULL);
 
-	result = WinGetFuncArgInFrame(winobj, 0,
-								  nth - 1, WINDOW_SEEK_HEAD, const_offset,
-								  &isnull, NULL);
 	if (isnull)
 		PG_RETURN_NULL();
 
 	PG_RETURN_DATUM(result);
 }
+
+static Datum
+window_nth_value_ignorenulls_common(FunctionCallInfo fcinfo, int32 nth)
+{
+	WindowObject winobj = PG_WINDOW_OBJECT();
+	Datum		result;
+	bool		isnull,
+				isout;
+	bool		fromlast;
+	int32		tmp_offset, notnull_offset = 0;
+
+	nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
+	if (isnull)
+		PG_RETURN_NULL();
+
+	if (nth == 0)
+		ereport(ERROR,
+		(errcode(ERRCODE_INVALID_ARGUMENT_FOR_NTH_VALUE),
+		errmsg("argument of nth_value must be greater or less than zero")));
+	else if (nth < 0)
+	{
+		nth = abs(nth);
+		fromlast = true;
+		tmp_offset = 1;
+	}
+	else
+	{
+		fromlast = false;
+		tmp_offset = -1;
+	}
+
+	while (notnull_offset < nth)
+	{
+		fromlast ? tmp_offset-- : tmp_offset++;
+		result = WinGetFuncArgInFrame(winobj, 0,
+									tmp_offset, fromlast ? WINDOW_SEEK_TAIL : WINDOW_SEEK_HEAD,
+									false, &isnull, &isout);
+		if (isout)
+			PG_RETURN_NULL();
+		if (!isnull)
+			notnull_offset++;
+	}
+
+	result = WinGetFuncArgInFrame(winobj, 0,
+								tmp_offset, fromlast ? WINDOW_SEEK_TAIL : WINDOW_SEEK_HEAD,
+								false, &isnull, &isout);
+
+	if (isout || isnull)
+		PG_RETURN_NULL();
+
+	PG_RETURN_DATUM(result);
+}
+
+Datum
+window_nth_value(PG_FUNCTION_ARGS)
+{
+	WindowObject winobj = PG_WINDOW_OBJECT();
+	bool		isnull;
+	int32		nth;
+
+	nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
+	if (isnull)
+		PG_RETURN_NULL();
+	PG_RETURN_DATUM(window_nth_value_respectnulls_common(fcinfo, nth));
+}
+
+Datum
+window_nth_value_with_nulls_opt(PG_FUNCTION_ARGS)
+{
+	WindowObject winobj = PG_WINDOW_OBJECT();
+	bool		isnull;
+	int32		nth;
+
+	nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
+	if (isnull)
+		PG_RETURN_NULL();
+	PG_RETURN_DATUM(window_nth_value_ignorenulls_common(fcinfo, nth));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 07a86c7b7b..f07a9e442d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -9539,32 +9539,64 @@
 { oid => '3106', descr => 'fetch the preceding row value',
   proname => 'lag', prokind => 'w', prorettype => 'anyelement',
   proargtypes => 'anyelement', prosrc => 'window_lag' },
+{ oid => '4191', descr => 'fetch the preceding row value with nulls option',
+  proname => 'lag', prokind => 'w', prorettype => 'anyelement',
+  proargtypes => 'anyelement ignorenulls', prosrc => 'window_lag_nulls_opt' },
 { oid => '3107', descr => 'fetch the Nth preceding row value',
   proname => 'lag', prokind => 'w', prorettype => 'anyelement',
   proargtypes => 'anyelement int4', prosrc => 'window_lag_with_offset' },
+{ oid => '4192', descr => 'fetch the Nth preceding row value with nulls option',
+  proname => 'lag', prokind => 'w', prorettype => 'anyelement',
+  proargtypes => 'anyelement int4 ignorenulls',
+  prosrc => 'window_lag_with_offset_nulls_opt' },
 { oid => '3108', descr => 'fetch the Nth preceding row value with default',
   proname => 'lag', prokind => 'w', prorettype => 'anyelement',
   proargtypes => 'anyelement int4 anyelement',
   prosrc => 'window_lag_with_offset_and_default' },
+{ oid => '4193', descr => 'fetch the Nth preceding row value with default and nulls option',
+  proname => 'lag', prokind => 'w', prorettype => 'anyelement',
+  proargtypes => 'anyelement int4 anyelement ignorenulls',
+  prosrc => 'window_lag_with_offset_and_default_nulls_opt' },
 { oid => '3109', descr => 'fetch the following row value',
   proname => 'lead', prokind => 'w', prorettype => 'anyelement',
   proargtypes => 'anyelement', prosrc => 'window_lead' },
+{ oid => '4194', descr => 'fetch the following row value with nulls option',
+ proname => 'lead', prokind => 'w', prorettype => 'anyelement',
+ proargtypes => 'anyelement ignorenulls', prosrc => 'window_lead_nulls_opt' },
 { oid => '3110', descr => 'fetch the Nth following row value',
   proname => 'lead', prokind => 'w', prorettype => 'anyelement',
   proargtypes => 'anyelement int4', prosrc => 'window_lead_with_offset' },
+{ oid => '4195', descr => 'fetch the Nth following row value with nulls option',
+  proname => 'lead', prokind => 'w', prorettype => 'anyelement',
+  proargtypes => 'anyelement int4 ignorenulls',
+  prosrc => 'window_lead_with_offset_nulls_opt' },
 { oid => '3111', descr => 'fetch the Nth following row value with default',
   proname => 'lead', prokind => 'w', prorettype => 'anyelement',
   proargtypes => 'anyelement int4 anyelement',
   prosrc => 'window_lead_with_offset_and_default' },
+{ oid => '4196', descr => 'fetch the Nth following row value with default and nulls option',
+  proname => 'lead', prokind => 'w', prorettype => 'anyelement',
+  proargtypes => 'anyelement int4 anyelement ignorenulls',
+  prosrc => 'window_lead_with_offset_and_default_nulls_opt' },
 { oid => '3112', descr => 'fetch the first row value',
   proname => 'first_value', prokind => 'w', prorettype => 'anyelement',
   proargtypes => 'anyelement', prosrc => 'window_first_value' },
+{ oid => '4197', descr => 'fetch the first row value with nulls option',
+  proname => 'first_value', prokind => 'w', prorettype => 'anyelement',
+  proargtypes => 'anyelement ignorenulls', prosrc => 'window_first_value_nulls_opt' },
 { oid => '3113', descr => 'fetch the last row value',
   proname => 'last_value', prokind => 'w', prorettype => 'anyelement',
   proargtypes => 'anyelement', prosrc => 'window_last_value' },
+{ oid => '4198', descr => 'fetch the last row value with nulls option',
+  proname => 'last_value', prokind => 'w', prorettype => 'anyelement',
+  proargtypes => 'anyelement ignorenulls', prosrc => 'window_last_value_nulls_opt' },
 { oid => '3114', descr => 'fetch the Nth row value',
   proname => 'nth_value', prokind => 'w', prorettype => 'anyelement',
   proargtypes => 'anyelement int4', prosrc => 'window_nth_value' },
+{ oid => '4199', descr => 'fetch the Nth row value with nulls option',
+  proname => 'nth_value', prokind => 'w', prorettype => 'anyelement',
+  proargtypes => 'anyelement int4 ignorenulls',
+  prosrc => 'window_nth_value_with_nulls_opt' },
 
 # functions for range types
 { oid => '3832', descr => 'I/O',
diff --git a/src/include/catalog/pg_type.dat b/src/include/catalog/pg_type.dat
index 4cf2b9df7b..3c847e6db5 100644
--- a/src/include/catalog/pg_type.dat
+++ b/src/include/catalog/pg_type.dat
@@ -594,5 +594,10 @@
   typname => 'anyrange', typlen => '-1', typbyval => 'f', typtype => 'p',
   typcategory => 'P', typinput => 'anyrange_in', typoutput => 'anyrange_out',
   typreceive => '-', typsend => '-', typalign => 'd', typstorage => 'x' },
+{ oid => '4142',
+  descr => 'boolean wrapper, \'true\'/\'false\'',
+  typname => 'ignorenulls', typlen => '1', typbyval => 't', typtype => 'b',
+  typcategory => 'B', typinput => 'boolin', typoutput => 'boolout',
+  typreceive => 'boolrecv', typsend => 'boolsend', typalign => 'c' },
 
 ]
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index da0706add5..0899bddd6e 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -532,6 +532,11 @@ typedef struct WindowDef
 	(FRAMEOPTION_RANGE | FRAMEOPTION_START_UNBOUNDED_PRECEDING | \
 	 FRAMEOPTION_END_CURRENT_ROW)
 
+/*
+ * Null Treatment option
+ */
+#define WINFUNC_OPT_IGNORE_NULLS			0x00001 /* IGNORE NULLS */
+
 /*
  * RangeSubselect - subquery appearing in a FROM clause
  */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index b1184c2d15..a33ce1d0fd 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -164,6 +164,7 @@ PG_KEYWORD("family", FAMILY, UNRESERVED_KEYWORD)
 PG_KEYWORD("fetch", FETCH, RESERVED_KEYWORD)
 PG_KEYWORD("filter", FILTER, UNRESERVED_KEYWORD)
 PG_KEYWORD("first", FIRST_P, UNRESERVED_KEYWORD)
+PG_KEYWORD("first_value", FIRST_VALUE, UNRESERVED_KEYWORD)
 PG_KEYWORD("float", FLOAT_P, COL_NAME_KEYWORD)
 PG_KEYWORD("following", FOLLOWING, UNRESERVED_KEYWORD)
 PG_KEYWORD("for", FOR, RESERVED_KEYWORD)
@@ -190,6 +191,7 @@ PG_KEYWORD("hold", HOLD, UNRESERVED_KEYWORD)
 PG_KEYWORD("hour", HOUR_P, UNRESERVED_KEYWORD)
 PG_KEYWORD("identity", IDENTITY_P, UNRESERVED_KEYWORD)
 PG_KEYWORD("if", IF_P, UNRESERVED_KEYWORD)
+PG_KEYWORD("ignore", IGNORE_P, UNRESERVED_KEYWORD)
 PG_KEYWORD("ilike", ILIKE, TYPE_FUNC_NAME_KEYWORD)
 PG_KEYWORD("immediate", IMMEDIATE, UNRESERVED_KEYWORD)
 PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD)
@@ -223,10 +225,13 @@ PG_KEYWORD("isolation", ISOLATION, UNRESERVED_KEYWORD)
 PG_KEYWORD("join", JOIN, TYPE_FUNC_NAME_KEYWORD)
 PG_KEYWORD("key", KEY, UNRESERVED_KEYWORD)
 PG_KEYWORD("label", LABEL, UNRESERVED_KEYWORD)
+PG_KEYWORD("lag", LAG, UNRESERVED_KEYWORD)
 PG_KEYWORD("language", LANGUAGE, UNRESERVED_KEYWORD)
 PG_KEYWORD("large", LARGE_P, UNRESERVED_KEYWORD)
 PG_KEYWORD("last", LAST_P, UNRESERVED_KEYWORD)
+PG_KEYWORD("last_value", LAST_VALUE, UNRESERVED_KEYWORD)
 PG_KEYWORD("lateral", LATERAL_P, RESERVED_KEYWORD)
+PG_KEYWORD("lead", LEAD, UNRESERVED_KEYWORD)
 PG_KEYWORD("leading", LEADING, RESERVED_KEYWORD)
 PG_KEYWORD("leakproof", LEAKPROOF, UNRESERVED_KEYWORD)
 PG_KEYWORD("least", LEAST, COL_NAME_KEYWORD)
@@ -267,6 +272,7 @@ PG_KEYWORD("nothing", NOTHING, UNRESERVED_KEYWORD)
 PG_KEYWORD("notify", NOTIFY, UNRESERVED_KEYWORD)
 PG_KEYWORD("notnull", NOTNULL, TYPE_FUNC_NAME_KEYWORD)
 PG_KEYWORD("nowait", NOWAIT, UNRESERVED_KEYWORD)
+PG_KEYWORD("nth_value", NTH_VALUE, UNRESERVED_KEYWORD)
 PG_KEYWORD("null", NULL_P, RESERVED_KEYWORD)
 PG_KEYWORD("nullif", NULLIF, COL_NAME_KEYWORD)
 PG_KEYWORD("nulls", NULLS_P, UNRESERVED_KEYWORD)
@@ -336,6 +342,7 @@ PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD)
 PG_KEYWORD("replace", REPLACE, UNRESERVED_KEYWORD)
 PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD)
 PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD)
+PG_KEYWORD("respect", RESPECT, UNRESERVED_KEYWORD)
 PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD)
 PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD)
 PG_KEYWORD("returning", RETURNING, RESERVED_KEYWORD)
diff --git a/src/test/regress/expected/type_sanity.out b/src/test/regress/expected/type_sanity.out
index cd7fc03b04..0ac49263ec 100644
--- a/src/test/regress/expected/type_sanity.out
+++ b/src/test/regress/expected/type_sanity.out
@@ -73,7 +73,8 @@ WHERE p1.typtype not in ('c','d','p') AND p1.typname NOT LIKE E'\\_%'
  3361 | pg_ndistinct
  3402 | pg_dependencies
  5017 | pg_mcv_list
-(4 rows)
+ 4142 | ignorenulls
+(5 rows)
 
 -- Make sure typarray points to a varlena array type of our own base
 SELECT p1.oid, p1.typname as basetype, p2.typname as arraytype,
@@ -166,10 +167,11 @@ WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
     (p1.typelem != 0 AND p1.typlen < 0) AND NOT
     (p2.prorettype = p1.oid AND NOT p2.proretset)
 ORDER BY 1;
- oid  |  typname  | oid | proname 
-------+-----------+-----+---------
- 1790 | refcursor |  46 | textin
-(1 row)
+ oid  |   typname   | oid  | proname 
+------+-------------+------+---------
+ 1790 | refcursor   |   46 | textin
+ 4142 | ignorenulls | 1242 | boolin
+(2 rows)
 
 -- Varlena array types will point to array_in
 -- Exception as of 8.1: int2vector and oidvector have their own I/O routines
@@ -217,10 +219,11 @@ WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT
       (p2.oid = 'array_out'::regproc AND
        p1.typelem != 0 AND p1.typlen = -1)))
 ORDER BY 1;
- oid  |  typname  | oid | proname 
-------+-----------+-----+---------
- 1790 | refcursor |  47 | textout
-(1 row)
+ oid  |   typname   | oid  | proname 
+------+-------------+------+---------
+ 1790 | refcursor   |   47 | textout
+ 4142 | ignorenulls | 1243 | boolout
+(2 rows)
 
 SELECT p1.oid, p1.typname, p2.oid, p2.proname
 FROM pg_type AS p1, pg_proc AS p2
@@ -280,10 +283,11 @@ WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT
     (p1.typelem != 0 AND p1.typlen < 0) AND NOT
     (p2.prorettype = p1.oid AND NOT p2.proretset)
 ORDER BY 1;
- oid  |  typname  | oid  | proname  
-------+-----------+------+----------
- 1790 | refcursor | 2414 | textrecv
-(1 row)
+ oid  |   typname   | oid  | proname  
+------+-------------+------+----------
+ 1790 | refcursor   | 2414 | textrecv
+ 4142 | ignorenulls | 2436 | boolrecv
+(2 rows)
 
 -- Varlena array types will point to array_recv
 -- Exception as of 8.1: int2vector and oidvector have their own I/O routines
@@ -340,10 +344,11 @@ WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT
       (p2.oid = 'array_send'::regproc AND
        p1.typelem != 0 AND p1.typlen = -1)))
 ORDER BY 1;
- oid  |  typname  | oid  | proname  
-------+-----------+------+----------
- 1790 | refcursor | 2415 | textsend
-(1 row)
+ oid  |   typname   | oid  | proname  
+------+-------------+------+----------
+ 1790 | refcursor   | 2415 | textsend
+ 4142 | ignorenulls | 2437 | boolsend
+(2 rows)
 
 SELECT p1.oid, p1.typname, p2.oid, p2.proname
 FROM pg_type AS p1, pg_proc AS p2
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index d5fd4045f9..54ded65906 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -2985,7 +2985,7 @@ LINE 1: SELECT generate_series(1, 100) OVER () FROM empsalary;
 SELECT ntile(0) OVER (ORDER BY ten), ten, four FROM tenk1;
 ERROR:  argument of ntile must be greater than zero
 SELECT nth_value(four, 0) OVER (ORDER BY ten), ten, four FROM tenk1;
-ERROR:  argument of nth_value must be greater than zero
+ERROR:  argument of nth_value must be greater or less than zero
 -- filter
 SELECT sum(salary), row_number() OVER (ORDER BY depname), sum(
     sum(salary) FILTER (WHERE enroll_date > '2007-01-01')
@@ -3863,3 +3863,369 @@ SELECT * FROM pg_temp.f(2);
  {5}
 (5 rows)
 
+-- RESPECT NULLS and IGNORE NULLS tests
+CREATE TEMPORARY TABLE planets (
+    name text,
+    orbit int
+);
+INSERT INTO planets VALUES
+  ('mercury', 88),
+  ('venus', 224),
+  ('earth', NULL),
+  ('mars', NULL),
+  ('jupiter', 4332),
+  ('saturn', 24491),
+  ('uranus', NULL),
+  ('neptune', 60182),
+  ('pluto', 90560);
+  -- test view definitions are preserved
+CREATE TEMP VIEW v_planets AS
+    SELECT
+      name,
+      sum(orbit) OVER (order by orbit) as sum_rows,
+      lag(orbit, 1) RESPECT NULLS OVER (ORDER BY name DESC) AS lagged_by_1,
+      lag(orbit, 2) IGNORE NULLS OVER w AS lagged_by_2,
+      first_value(orbit) IGNORE NULLS OVER w AS first_value_ignore,
+      nth_value(orbit,2) IGNORE NULLS OVER w AS nth_first_ignore,
+      nth_value(orbit,-2) IGNORE NULLS OVER w AS nth_last_ignore
+    FROM planets
+    WINDOW w as (ORDER BY name ASC);
+SELECT pg_get_viewdef('v_planets');
+                                   pg_get_viewdef                                   
+------------------------------------------------------------------------------------
+  SELECT planets.name,                                                             +
+     sum(planets.orbit) OVER (ORDER BY planets.orbit) AS sum_rows,                 +
+     lag(planets.orbit, 1) OVER (ORDER BY planets.name DESC) AS lagged_by_1,       +
+     lag(planets.orbit, 2) IGNORE NULLS OVER w AS lagged_by_2,                     +
+     first_value(planets.orbit) IGNORE NULLS OVER w AS first_value_ignore,         +
+     nth_value(planets.orbit, 2) IGNORE NULLS OVER w AS nth_first_ignore,          +
+     nth_value(planets.orbit, '-2'::integer) IGNORE NULLS OVER w AS nth_last_ignore+
+    FROM planets                                                                   +
+   WINDOW w AS (ORDER BY planets.name);
+(1 row)
+
+SELECT name, lag(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   |  lag  
+---------+-------
+ earth   |      
+ jupiter |      
+ mars    |  4332
+ mercury |      
+ neptune |    88
+ pluto   | 60182
+ saturn  | 90560
+ uranus  | 24491
+ venus   |      
+(9 rows)
+
+SELECT name, lag(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   |  lag  
+---------+-------
+ earth   |      
+ jupiter |      
+ mars    |  4332
+ mercury |      
+ neptune |    88
+ pluto   | 60182
+ saturn  | 90560
+ uranus  | 24491
+ venus   |      
+(9 rows)
+
+SELECT name, lag(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   |  lag  
+---------+-------
+ earth   |      
+ jupiter |      
+ mars    |  4332
+ mercury |  4332
+ neptune |    88
+ pluto   | 60182
+ saturn  | 90560
+ uranus  | 24491
+ venus   | 24491
+(9 rows)
+
+SELECT name, lead(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | lead  
+---------+-------
+ earth   |  4332
+ jupiter |      
+ mars    |    88
+ mercury | 60182
+ neptune | 90560
+ pluto   | 24491
+ saturn  |      
+ uranus  |   224
+ venus   |      
+(9 rows)
+
+SELECT name, lead(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | lead  
+---------+-------
+ earth   |  4332
+ jupiter |      
+ mars    |    88
+ mercury | 60182
+ neptune | 90560
+ pluto   | 24491
+ saturn  |      
+ uranus  |   224
+ venus   |      
+(9 rows)
+
+SELECT name, lead(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | lead  
+---------+-------
+ earth   |  4332
+ jupiter |    88
+ mars    |    88
+ mercury | 60182
+ neptune | 90560
+ pluto   | 24491
+ saturn  |   224
+ uranus  |   224
+ venus   |      
+(9 rows)
+
+SELECT name, lag(orbit, -1) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   |  lag  
+---------+-------
+ earth   |  4332
+ jupiter |    88
+ mars    |    88
+ mercury | 60182
+ neptune | 90560
+ pluto   | 24491
+ saturn  |   224
+ uranus  |   224
+ venus   |      
+(9 rows)
+
+SELECT name, lead(orbit, -1) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | lead  
+---------+-------
+ earth   |      
+ jupiter |      
+ mars    |  4332
+ mercury |  4332
+ neptune |    88
+ pluto   | 60182
+ saturn  | 90560
+ uranus  | 24491
+ venus   | 24491
+(9 rows)
+
+SELECT name, first_value(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | first_value 
+---------+-------------
+ earth   |            
+ jupiter |            
+ mars    |            
+ mercury |            
+ neptune |            
+ pluto   |            
+ saturn  |            
+ uranus  |            
+ venus   |            
+(9 rows)
+
+SELECT name, first_value(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | first_value 
+---------+-------------
+ earth   |            
+ jupiter |            
+ mars    |            
+ mercury |            
+ neptune |            
+ pluto   |            
+ saturn  |            
+ uranus  |            
+ venus   |            
+(9 rows)
+
+SELECT name, first_value(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | first_value 
+---------+-------------
+ earth   |        4332
+ jupiter |        4332
+ mars    |        4332
+ mercury |        4332
+ neptune |        4332
+ pluto   |        4332
+ saturn  |        4332
+ uranus  |        4332
+ venus   |        4332
+(9 rows)
+
+SELECT name, last_value(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | last_value 
+---------+------------
+ earth   |        224
+ jupiter |        224
+ mars    |        224
+ mercury |        224
+ neptune |        224
+ pluto   |        224
+ saturn  |        224
+ uranus  |        224
+ venus   |        224
+(9 rows)
+
+SELECT name, last_value(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | last_value 
+---------+------------
+ earth   |        224
+ jupiter |        224
+ mars    |        224
+ mercury |        224
+ neptune |        224
+ pluto   |        224
+ saturn  |        224
+ uranus  |        224
+ venus   |        224
+(9 rows)
+
+SELECT name, last_value(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | last_value 
+---------+------------
+ earth   |        224
+ jupiter |        224
+ mars    |        224
+ mercury |        224
+ neptune |        224
+ pluto   |        224
+ saturn  |        224
+ uranus  |        224
+ venus   |        224
+(9 rows)
+
+SELECT name, nth_value(orbit, 2) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | nth_value 
+---------+-----------
+ earth   |      4332
+ jupiter |      4332
+ mars    |      4332
+ mercury |      4332
+ neptune |      4332
+ pluto   |      4332
+ saturn  |      4332
+ uranus  |      4332
+ venus   |      4332
+(9 rows)
+
+SELECT name, nth_value(orbit, 2) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | nth_value 
+---------+-----------
+ earth   |        88
+ jupiter |        88
+ mars    |        88
+ mercury |        88
+ neptune |        88
+ pluto   |        88
+ saturn  |        88
+ uranus  |        88
+ venus   |        88
+(9 rows)
+
+SELECT name, nth_value(orbit, 2) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | nth_value 
+---------+-----------
+ earth   |      4332
+ jupiter |      4332
+ mars    |      4332
+ mercury |      4332
+ neptune |      4332
+ pluto   |      4332
+ saturn  |      4332
+ uranus  |      4332
+ venus   |      4332
+(9 rows)
+
+SELECT name, nth_value(orbit, 2) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | nth_value 
+---------+-----------
+ earth   |      4332
+ jupiter |      4332
+ mars    |      4332
+ mercury |      4332
+ neptune |      4332
+ pluto   |      4332
+ saturn  |      4332
+ uranus  |      4332
+ venus   |      4332
+(9 rows)
+
+SELECT name, nth_value(orbit, 2) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | nth_value 
+---------+-----------
+ earth   |        88
+ jupiter |        88
+ mars    |        88
+ mercury |        88
+ neptune |        88
+ pluto   |        88
+ saturn  |        88
+ uranus  |        88
+ venus   |        88
+(9 rows)
+
+SELECT name, nth_value(orbit, 2) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | nth_value 
+---------+-----------
+ earth   |      4332
+ jupiter |      4332
+ mars    |      4332
+ mercury |      4332
+ neptune |      4332
+ pluto   |      4332
+ saturn  |      4332
+ uranus  |      4332
+ venus   |      4332
+(9 rows)
+
+SELECT name, nth_value(orbit, -2) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | nth_value 
+---------+-----------
+ earth   |          
+ jupiter |          
+ mars    |          
+ mercury |          
+ neptune |          
+ pluto   |          
+ saturn  |          
+ uranus  |          
+ venus   |          
+(9 rows)
+
+SELECT name, nth_value(orbit, -2) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | nth_value 
+---------+-----------
+ earth   |     24491
+ jupiter |     24491
+ mars    |     24491
+ mercury |     24491
+ neptune |     24491
+ pluto   |     24491
+ saturn  |     24491
+ uranus  |     24491
+ venus   |     24491
+(9 rows)
+
+SELECT name, nth_value(orbit, -2) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+  name   | nth_value 
+---------+-----------
+ earth   |          
+ jupiter |          
+ mars    |          
+ mercury |          
+ neptune |          
+ pluto   |          
+ saturn  |          
+ uranus  |          
+ venus   |          
+(9 rows)
+
+--cleanup
+DROP TABLE planets CASCADE;
+NOTICE:  drop cascades to view v_planets
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index fe273aa31e..548902d482 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1276,3 +1276,67 @@ $$ LANGUAGE SQL STABLE;
 
 EXPLAIN (costs off) SELECT * FROM pg_temp.f(2);
 SELECT * FROM pg_temp.f(2);
+
+-- RESPECT NULLS and IGNORE NULLS tests
+CREATE TEMPORARY TABLE planets (
+    name text,
+    orbit int
+);
+
+INSERT INTO planets VALUES
+  ('mercury', 88),
+  ('venus', 224),
+  ('earth', NULL),
+  ('mars', NULL),
+  ('jupiter', 4332),
+  ('saturn', 24491),
+  ('uranus', NULL),
+  ('neptune', 60182),
+  ('pluto', 90560);
+
+  -- test view definitions are preserved
+CREATE TEMP VIEW v_planets AS
+    SELECT
+      name,
+      sum(orbit) OVER (order by orbit) as sum_rows,
+      lag(orbit, 1) RESPECT NULLS OVER (ORDER BY name DESC) AS lagged_by_1,
+      lag(orbit, 2) IGNORE NULLS OVER w AS lagged_by_2,
+      first_value(orbit) IGNORE NULLS OVER w AS first_value_ignore,
+      nth_value(orbit,2) IGNORE NULLS OVER w AS nth_first_ignore,
+      nth_value(orbit,-2) IGNORE NULLS OVER w AS nth_last_ignore
+    FROM planets
+    WINDOW w as (ORDER BY name ASC);
+SELECT pg_get_viewdef('v_planets');
+
+SELECT name, lag(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, lag(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, lag(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+
+SELECT name, lead(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, lead(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, lead(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+
+SELECT name, lag(orbit, -1) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, lead(orbit, -1) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+
+SELECT name, first_value(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, first_value(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, first_value(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+
+SELECT name, last_value(orbit) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, last_value(orbit) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, last_value(orbit) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+
+SELECT name, nth_value(orbit, 2) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, nth_value(orbit, 2) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, nth_value(orbit, 2) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+
+SELECT name, nth_value(orbit, 2) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, nth_value(orbit, 2) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, nth_value(orbit, 2) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, nth_value(orbit, -2) OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, nth_value(orbit, -2) IGNORE NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+SELECT name, nth_value(orbit, -2) RESPECT NULLS OVER (ORDER BY name range between unbounded preceding and unbounded following) FROM planets ORDER BY name;
+
+--cleanup
+DROP TABLE planets CASCADE;