public inbox for [email protected]
help / color / mirror / Atom feed[PATCH 6/8] Add default_toast_compression GUC
5+ messages / 4 participants
[nested] [flat]
* [PATCH 6/8] Add default_toast_compression GUC
@ 2021-03-10 08:35 Dilip Kumar <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Dilip Kumar @ 2021-03-10 08:35 UTC (permalink / raw)
Justin Pryzby and Dilip Kumar
---
src/backend/access/common/toast_compression.c | 46 +++++++++++++++++++
src/backend/access/common/tupdesc.c | 2 +-
src/backend/bootstrap/bootstrap.c | 2 +-
src/backend/commands/tablecmds.c | 8 ++--
src/backend/utils/misc/guc.c | 12 +++++
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/access/toast_compression.h | 22 ++++++++-
src/test/regress/expected/compression.out | 16 +++++++
src/test/regress/expected/compression_1.out | 19 ++++++++
src/test/regress/sql/compression.sql | 8 ++++
10 files changed, 128 insertions(+), 8 deletions(-)
diff --git a/src/backend/access/common/toast_compression.c b/src/backend/access/common/toast_compression.c
index db4911ce43..27fe47b201 100644
--- a/src/backend/access/common/toast_compression.c
+++ b/src/backend/access/common/toast_compression.c
@@ -55,6 +55,9 @@ const CompressionRoutine toast_compression[] =
}
};
+/* Compile-time default */
+char *default_toast_compression = DEFAULT_TOAST_COMPRESSION;
+
/*
* pglz_cmcompress - compression routine for pglz compression method
*
@@ -306,3 +309,46 @@ GetCompressionRoutines(char method)
{
return &toast_compression[CompressionMethodToId(method)];
}
+
+/* check_hook: validate new default_toast_compression */
+bool
+check_default_toast_compression(char **newval, void **extra, GucSource source)
+{
+ if (**newval == '\0')
+ {
+ GUC_check_errdetail("%s cannot be empty.",
+ "default_toast_compression");
+ return false;
+ }
+
+ if (strlen(*newval) >= NAMEDATALEN)
+ {
+ GUC_check_errdetail("%s is too long (maximum %d characters).",
+ "default_toast_compression", NAMEDATALEN - 1);
+ return false;
+ }
+
+ if (!CompressionMethodIsValid(CompressionNameToMethod(*newval)))
+ {
+ /*
+ * When source == PGC_S_TEST, don't throw a hard error for a
+ * nonexistent compression method, only a NOTICE. See comments in
+ * guc.h.
+ */
+ if (source == PGC_S_TEST)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("compression method \"%s\" does not exist",
+ *newval)));
+ }
+ else
+ {
+ GUC_check_errdetail("Compression method \"%s\" does not exist.",
+ *newval);
+ return false;
+ }
+ }
+
+ return true;
+}
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index a66d1113db..362a371a6c 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -668,7 +668,7 @@ TupleDescInitEntry(TupleDesc desc,
att->attcollation = typeForm->typcollation;
if (IsStorageCompressible(typeForm->typstorage))
- att->attcompression = DefaultCompressionMethod;
+ att->attcompression = GetDefaultToastCompression();
else
att->attcompression = InvalidCompressionMethod;
diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c
index af0c0aa77e..9be0841d08 100644
--- a/src/backend/bootstrap/bootstrap.c
+++ b/src/backend/bootstrap/bootstrap.c
@@ -733,7 +733,7 @@ DefineAttr(char *name, char *type, int attnum, int nullness)
attrtypes[attnum]->atttypmod = -1;
attrtypes[attnum]->attislocal = true;
if (IsStorageCompressible(attrtypes[attnum]->attstorage))
- attrtypes[attnum]->attcompression = DefaultCompressionMethod;
+ attrtypes[attnum]->attcompression = GetDefaultToastCompression();
else
attrtypes[attnum]->attcompression = InvalidCompressionMethod;
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index e20dd5f63c..a0d2af0cde 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -11971,7 +11971,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
if (!IsStorageCompressible(tform->typstorage))
attTup->attcompression = InvalidCompressionMethod;
else if (!CompressionMethodIsValid(attTup->attcompression))
- attTup->attcompression = DefaultCompressionMethod;
+ attTup->attcompression = GetDefaultToastCompression();
}
else
attTup->attcompression = InvalidCompressionMethod;
@@ -17847,9 +17847,9 @@ GetAttributeCompression(Form_pg_attribute att, char *compression)
/* fallback to default compression if it's not specified */
if (compression == NULL)
- return DefaultCompressionMethod;
-
- cmethod = CompressionNameToMethod(compression);
+ cmethod = GetDefaultToastCompression();
+ else
+ cmethod = CompressionNameToMethod(compression);
return cmethod;
}
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 953837085f..e59ed5b214 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -33,6 +33,7 @@
#include "access/gin.h"
#include "access/rmgr.h"
#include "access/tableam.h"
+#include "access/toast_compression.h"
#include "access/transam.h"
#include "access/twophase.h"
#include "access/xact.h"
@@ -3915,6 +3916,17 @@ static struct config_string ConfigureNamesString[] =
check_default_table_access_method, NULL, NULL
},
+ {
+ {"default_toast_compression", PGC_USERSET, CLIENT_CONN_STATEMENT,
+ gettext_noop("Sets the default compression for new columns."),
+ NULL,
+ GUC_IS_NAME
+ },
+ &default_toast_compression,
+ DEFAULT_TOAST_COMPRESSION,
+ check_default_toast_compression, NULL, NULL
+ },
+
{
{"default_tablespace", PGC_USERSET, CLIENT_CONN_STATEMENT,
gettext_noop("Sets the default tablespace to create tables and indexes in."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index f46c2dd7a8..7d7a433dcc 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -659,6 +659,7 @@
#temp_tablespaces = '' # a list of tablespace names, '' uses
# only default tablespace
#default_table_access_method = 'heap'
+#default_toast_compression = 'pglz' # 'pglz' or 'lz4'
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
diff --git a/src/include/access/toast_compression.h b/src/include/access/toast_compression.h
index 38800cb97a..e9d9ce5634 100644
--- a/src/include/access/toast_compression.h
+++ b/src/include/access/toast_compression.h
@@ -15,6 +15,14 @@
#include "postgres.h"
+#include "utils/guc.h"
+
+/* default compression method if not specified. */
+#define DEFAULT_TOAST_COMPRESSION "pglz"
+
+/* GUCs */
+extern char *default_toast_compression;
+
/*
* Built-in compression methods. pg_attribute will store this in the
* attcompression column.
@@ -36,8 +44,6 @@ typedef enum CompressionId
LZ4_COMPRESSION_ID = 1
} CompressionId;
-/* use default compression method if it is not specified. */
-#define DefaultCompressionMethod PGLZ_COMPRESSION
#define IsValidCompression(cm) ((cm) != InvalidCompressionMethod)
#define IsStorageCompressible(storage) ((storage) != TYPSTORAGE_PLAIN && \
@@ -67,6 +73,8 @@ typedef struct CompressionRoutine
extern char CompressionNameToMethod(char *compression);
extern const CompressionRoutine *GetCompressionRoutines(char method);
+extern bool check_default_toast_compression(char **newval, void **extra,
+ GucSource source);
/*
* CompressionMethodToId - Convert compression method to compression id.
@@ -115,5 +123,15 @@ GetCompressionMethodName(char method)
return GetCompressionRoutines(method)->cmname;
}
+/*
+ * GetDefaultToastCompression -- get the current toast compression
+ *
+ * This exists to hide the use of the default_toast_compression GUC variable.
+ */
+static inline char
+GetDefaultToastCompression(void)
+{
+ return CompressionNameToMethod(default_toast_compression);
+}
#endif /* TOAST_COMPRESSION_H */
diff --git a/src/test/regress/expected/compression.out b/src/test/regress/expected/compression.out
index 6981d580dc..9ddffc8b18 100644
--- a/src/test/regress/expected/compression.out
+++ b/src/test/regress/expected/compression.out
@@ -231,6 +231,22 @@ CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata);
NOTICE: merging column "f1" with inherited definition
ERROR: column "f1" has a compression method conflict
DETAIL: pglz versus lz4
+-- test default_toast_compression GUC
+SET default_toast_compression = '';
+ERROR: invalid value for parameter "default_toast_compression": ""
+DETAIL: default_toast_compression cannot be empty.
+SET default_toast_compression = 'I do not exist compression';
+ERROR: invalid value for parameter "default_toast_compression": "I do not exist compression"
+DETAIL: Compression method "I do not exist compression" does not exist.
+SET default_toast_compression = 'lz4';
+DROP TABLE cmdata2;
+CREATE TABLE cmdata2 (f1 text);
+\d+ cmdata2
+ Table "public.cmdata2"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ f1 | text | | | | extended | lz4 | |
+
-- check data is ok
SELECT length(f1) FROM cmdata;
length
diff --git a/src/test/regress/expected/compression_1.out b/src/test/regress/expected/compression_1.out
index 893c18c7fa..03a7f46554 100644
--- a/src/test/regress/expected/compression_1.out
+++ b/src/test/regress/expected/compression_1.out
@@ -237,6 +237,25 @@ CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata);
NOTICE: merging column "f1" with inherited definition
ERROR: column "f1" has a compression method conflict
DETAIL: pglz versus lz4
+-- test default_toast_compression GUC
+SET default_toast_compression = '';
+ERROR: invalid value for parameter "default_toast_compression": ""
+DETAIL: default_toast_compression cannot be empty.
+SET default_toast_compression = 'I do not exist compression';
+ERROR: invalid value for parameter "default_toast_compression": "I do not exist compression"
+DETAIL: Compression method "I do not exist compression" does not exist.
+SET default_toast_compression = 'lz4';
+ERROR: unsupported LZ4 compression method
+DETAIL: This functionality requires the server to be built with lz4 support.
+HINT: You need to rebuild PostgreSQL using --with-lz4.
+DROP TABLE cmdata2;
+CREATE TABLE cmdata2 (f1 text);
+\d+ cmdata2
+ Table "public.cmdata2"
+ Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
+--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
+ f1 | text | | | | extended | pglz | |
+
-- check data is ok
SELECT length(f1) FROM cmdata;
length
diff --git a/src/test/regress/sql/compression.sql b/src/test/regress/sql/compression.sql
index b085c9fbd9..6c9b24f1f7 100644
--- a/src/test/regress/sql/compression.sql
+++ b/src/test/regress/sql/compression.sql
@@ -103,6 +103,14 @@ SELECT pg_column_compression(f1) FROM cmpart;
CREATE TABLE cminh() INHERITS(cmdata, cmdata1);
CREATE TABLE cminh(f1 TEXT COMPRESSION lz4) INHERITS(cmdata);
+-- test default_toast_compression GUC
+SET default_toast_compression = '';
+SET default_toast_compression = 'I do not exist compression';
+SET default_toast_compression = 'lz4';
+DROP TABLE cmdata2;
+CREATE TABLE cmdata2 (f1 text);
+\d+ cmdata2
+
-- check data is ok
SELECT length(f1) FROM cmdata;
SELECT length(f1) FROM cmdata1;
--
2.17.0
--C94crkcyjafcjHxo
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="0007-Alter-table-set-compression.patch"
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options
@ 2025-01-23 04:25 Tatsuo Ishii <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Tatsuo Ishii @ 2025-01-23 04:25 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; [email protected]
> Hello,
> I also played with the v4 patch and it produces correct result:
> test=# SELECT x,y,lead(y) IGNORE NULLS OVER (ORDER BY x) FROM
> (VALUES(1,NULL),(2,2),(3,NULL)) AS v(x,y);
> x | y | lead
> ---+---+------
> 1 | | 2
> 2 | 2 |
> 3 | |
> (3 rows)
>
> test=#
> It is from today's git, clean compile and install with only v4 patch
> applied, make check also passes without errors.
I guess you are just lucky. In my case I enabled --enable-cassert to
build PostgreSQL and it automatically turn on CLOBBER_FREED_MEMORY and
freed memory area is scrambled. If I look the patch closer, I found a
problem:
+void
+WinCheckAndInitializeNullTreatment(WindowObject winobj,
:
:
+ winobj->win_nonnulls = palloc_array(int64, 16);
WinCheckAndInitializeNullTreatment is called in each built-in window
function. Window functions are called in the per tuple memory context,
which means win_nonnulls disappears when next tuple is supplied to the
window function. If my understanding is correct, winobj->win_nonnulls
needs to survive across processing tuples.
Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options
@ 2025-01-23 06:08 Krasiyan Andreev <[email protected]>
parent: Tatsuo Ishii <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Krasiyan Andreev @ 2025-01-23 06:08 UTC (permalink / raw)
To: Tatsuo Ishii <[email protected]>; +Cc: [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; [email protected]
Hi,
I was able to reproduce exactly the problem, with clean compile
and --enable-cassert:
test=# SELECT x,y,lead(y) IGNORE NULLS OVER (ORDER BY x) FROM
(VALUES(1,NULL),(2,2),(3,NULL)) AS v(x,y);
x | y | lead
---+---+------
1 | | 2
2 | 2 | 2
3 | | 2
(3 rows)
test=#
Also, make check errors out at window test (without --enable-cassert it was
passed in previous compile):
krasiyan@fedora:~/pgsql-src/postgresql$ cat
/home/krasiyan/pgsql-src/postgresql/src/test/regress/regression.diffs
diff -U3
/home/krasiyan/pgsql-src/postgresql/src/test/regress/expected/window.out
/home/krasiyan/pgsql-src/postgresql/src/test/regress/results/window.out
---
/home/krasiyan/pgsql-src/postgresql/src/test/regress/expected/window.out
2025-01-22 21:25:47.114508215 +0200
+++ /home/krasiyan/pgsql-src/postgresql/src/test/regress/results/window.out
2025-01-23 07:58:26.784659592 +0200
@@ -5477,12 +5477,12 @@
name | orbit | lead | lead_respect | lead_ignore
---------+-------+-------+--------------+-------------
earth | | 4332 | 4332 | 4332
- jupiter | 4332 | | | 88
+ jupiter | 4332 | | |
mars | | 88 | 88 | 88
mercury | 88 | 60182 | 60182 | 60182
neptune | 60182 | 90560 | 90560 | 90560
pluto | 90560 | 24491 | 24491 | 24491
- saturn | 24491 | | | 224
+ saturn | 24491 | | |
uranus | | 224 | 224 | 224
venus | 224 | | |
xyzzy | | | |
@@ -5577,13 +5577,13 @@
name | orbit | first_value | last_value | nth_value | lead_ignore |
lag_ignore
---------+-------+-------------+------------+-----------+-------------+------------
earth | | 4332 | 4332 | | 4332 |
- jupiter | 4332 | 88 | 88 | | 88 |
- mars | | 4332 | 60182 | 88 | 88 |
4332
- mercury | 88 | 4332 | 90560 | 60182 | 60182 |
4332
+ jupiter | 4332 | 88 | 88 | | 60182 |
+ mars | | 88 | 60182 | 60182 | 60182 |
4332
+ mercury | 88 | 4332 | 90560 | 90560 | 90560 |
4332
neptune | 60182 | 88 | 24491 | 90560 | 90560 |
88
- pluto | 90560 | 88 | 24491 | 60182 | 24491 |
60182
- saturn | 24491 | 60182 | 224 | 90560 | 224 |
90560
- uranus | | 90560 | 224 | 24491 | 224 |
24491
+ pluto | 90560 | 88 | 24491 | 60182 | 60182 |
60182
+ saturn | 24491 | 60182 | 224 | 90560 | 90560 |
90560
+ uranus | | 90560 | 224 | 24491 | 24491 |
24491
venus | 224 | 24491 | 24491 | | |
24491
xyzzy | | 224 | 224 | | |
224
(10 rows)
@@ -5646,14 +5646,14 @@
name | orbit | first_value | last_value | nth_value | lead_ignore |
lag_ignore
---------+-------+-------------+------------+-----------+-------------+------------
earth | | | | | 88 |
- jupiter | | 88 | 88 | | 88 |
- mars | | 88 | 60182 | 60182 | 88 |
+ jupiter | | 88 | 88 | | 60182 |
+ mars | | 88 | 60182 | 60182 | 60182 |
mercury | 88 | 88 | 90560 | 60182 | 60182 |
- neptune | 60182 | 88 | 24491 | 60182 | 90560 |
88
- pluto | 90560 | 88 | 24491 | 60182 | 24491 |
60182
- saturn | 24491 | 60182 | 224 | 90560 | 224 |
90560
- uranus | | 90560 | 224 | 24491 | 224 |
24491
- venus | 224 | 24491 | 224 | 224 | |
24491
+ neptune | 60182 | 88 | 24491 | 60182 | 60182 |
88
+ pluto | 90560 | 88 | 24491 | 60182 | 60182 |
60182
+ saturn | 24491 | 60182 | 224 | 90560 | 90560 |
90560
+ uranus | | 90560 | 224 | 24491 | 24491 |
24491
+ venus | 224 | 24491 | 224 | 224 | 224 |
24491
xyzzy | | 224 | 224 | | |
224
(10 rows)
На чт, 23.01.2025 г. в 6:25 Tatsuo Ishii <[email protected]> написа:
> > Hello,
> > I also played with the v4 patch and it produces correct result:
> > test=# SELECT x,y,lead(y) IGNORE NULLS OVER (ORDER BY x) FROM
> > (VALUES(1,NULL),(2,2),(3,NULL)) AS v(x,y);
> > x | y | lead
> > ---+---+------
> > 1 | | 2
> > 2 | 2 |
> > 3 | |
> > (3 rows)
> >
> > test=#
> > It is from today's git, clean compile and install with only v4 patch
> > applied, make check also passes without errors.
>
> I guess you are just lucky. In my case I enabled --enable-cassert to
> build PostgreSQL and it automatically turn on CLOBBER_FREED_MEMORY and
> freed memory area is scrambled. If I look the patch closer, I found a
> problem:
>
> +void
> +WinCheckAndInitializeNullTreatment(WindowObject winobj,
> :
> :
> + winobj->win_nonnulls = palloc_array(int64, 16);
>
> WinCheckAndInitializeNullTreatment is called in each built-in window
> function. Window functions are called in the per tuple memory context,
> which means win_nonnulls disappears when next tuple is supplied to the
> window function. If my understanding is correct, winobj->win_nonnulls
> needs to survive across processing tuples.
>
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options
@ 2025-01-23 06:27 Tatsuo Ishii <[email protected]>
parent: Krasiyan Andreev <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Tatsuo Ishii @ 2025-01-23 06:27 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; [email protected]
> Hi,
> I was able to reproduce exactly the problem, with clean compile
> and --enable-cassert:
> test=# SELECT x,y,lead(y) IGNORE NULLS OVER (ORDER BY x) FROM
> (VALUES(1,NULL),(2,2),(3,NULL)) AS v(x,y);
> x | y | lead
> ---+---+------
> 1 | | 2
> 2 | 2 | 2
> 3 | | 2
> (3 rows)
>
> test=#
> Also, make check errors out at window test (without --enable-cassert it was
> passed in previous compile):
Yeah, same here.
Another possible problem is, probably the code does not work well if
there are multiple partitions. Since win_nonnulls stores currentpos in
a partition, when the partition ends, win_nonnulls needs to be
reset. Otherwise, it mistakenly represents currentpos in the previous
partition.
Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options
@ 2025-01-24 08:27 Oliver Ford <[email protected]>
parent: Tatsuo Ishii <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Oliver Ford @ 2025-01-24 08:27 UTC (permalink / raw)
To: Tatsuo Ishii <[email protected]>; +Cc: [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; [email protected]
On Thu, Jan 23, 2025 at 6:27 AM Tatsuo Ishii <[email protected]> wrote:
>
> Another possible problem is, probably the code does not work well if
> there are multiple partitions. Since win_nonnulls stores currentpos in
> a partition, when the partition ends, win_nonnulls needs to be
> reset. Otherwise, it mistakenly represents currentpos in the previous
> partition.
>
The attached patch should fix both of these. I've added extra tests
with a PARTITION BY in the window clause to test for multiple
partitions.
Attachments:
[application/octet-stream] 0005-ignore-nulls.patch (49.0K, ../../CAGMVOdsEcL8kVhP_gz5qAEDuBaH8ypqmF8do2shENjC+_38yfg@mail.gmail.com/2-0005-ignore-nulls.patch)
download | inline diff:
From 5268754b33103fefc511b57ec546103899f70dbe Mon Sep 17 00:00:00 2001
From: Oliver Ford <[email protected]>
Date: Thu, 23 Jan 2025 20:11:17 +0000
Subject: [PATCH] :ignore nulls
---
doc/src/sgml/func.sgml | 38 +--
doc/src/sgml/syntax.sgml | 10 +-
src/backend/catalog/sql_features.txt | 2 +-
src/backend/executor/nodeWindowAgg.c | 332 ++++++++++++++++++++++++++-
src/backend/optimizer/util/clauses.c | 1 +
src/backend/parser/gram.y | 19 +-
src/backend/parser/parse_func.c | 9 +
src/backend/utils/adt/ruleutils.c | 7 +-
src/backend/utils/adt/windowfuncs.c | 10 +
src/include/nodes/parsenodes.h | 1 +
src/include/nodes/primnodes.h | 14 ++
src/include/parser/kwlist.h | 2 +
src/include/windowapi.h | 4 +
src/test/regress/expected/window.out | 284 +++++++++++++++++++++++
src/test/regress/sql/window.sql | 137 +++++++++++
15 files changed, 842 insertions(+), 28 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5678e7621a..07018a5070 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -23265,7 +23265,7 @@ SELECT count(*) FROM sometable;
</indexterm>
<function>lag</function> ( <parameter>value</parameter> <type>anycompatible</type>
<optional>, <parameter>offset</parameter> <type>integer</type>
- <optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> )
+ <optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> ) <optional> <parameter>null treatment</parameter> </optional>
<returnvalue>anycompatible</returnvalue>
</para>
<para>
@@ -23290,7 +23290,7 @@ SELECT count(*) FROM sometable;
</indexterm>
<function>lead</function> ( <parameter>value</parameter> <type>anycompatible</type>
<optional>, <parameter>offset</parameter> <type>integer</type>
- <optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> )
+ <optional>, <parameter>default</parameter> <type>anycompatible</type> </optional></optional> ) <optional> <parameter>null treatment</parameter> </optional>
<returnvalue>anycompatible</returnvalue>
</para>
<para>
@@ -23313,7 +23313,7 @@ SELECT count(*) FROM sometable;
<indexterm>
<primary>first_value</primary>
</indexterm>
- <function>first_value</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <function>first_value</function> ( <parameter>value</parameter> <type>anyelement</type> ) <optional> <parameter>null treatment</parameter> </optional>
<returnvalue>anyelement</returnvalue>
</para>
<para>
@@ -23327,7 +23327,7 @@ SELECT count(*) FROM sometable;
<indexterm>
<primary>last_value</primary>
</indexterm>
- <function>last_value</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <function>last_value</function> ( <parameter>value</parameter> <type>anyelement</type> ) <optional> <parameter>null treatment</parameter> </optional>
<returnvalue>anyelement</returnvalue>
</para>
<para>
@@ -23341,7 +23341,7 @@ SELECT count(*) FROM sometable;
<indexterm>
<primary>nth_value</primary>
</indexterm>
- <function>nth_value</function> ( <parameter>value</parameter> <type>anyelement</type>, <parameter>n</parameter> <type>integer</type> )
+ <function>nth_value</function> ( <parameter>value</parameter> <type>anyelement</type>, <parameter>n</parameter> <type>integer</type> ) <optional> <parameter>null treatment</parameter> </optional>
<returnvalue>anyelement</returnvalue>
</para>
<para>
@@ -23390,18 +23390,26 @@ SELECT count(*) FROM sometable;
Other frame specifications can be used to obtain other effects.
</para>
+ <para>
+ The <literal>null treatment</literal> option must be one of:
+<synopsis>
+ RESPECT NULLS
+ IGNORE NULLS
+</synopsis>
+ If unspecified, the default is <literal>RESPECT NULLS</literal> which includes NULL
+ values in any result calculation. <literal>IGNORE NULLS</literal> ignores NULL values.
+ This option is only allowed for the following functions: <function>lag</function>,
+ <function>lead</function>, <function>first_value</function>, <function>last_value</function>,
+ <function>nth_value</function>.
+ </para>
+
<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>
+ 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
+ reversing the <literal>ORDER BY</literal>
ordering.)
</para>
</note>
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 916189a7d6..237d7306fe 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1834,8 +1834,8 @@ FROM generate_series(1,10) AS s(i);
The syntax of a window function call is one of the following:
<synopsis>
-<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
-<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
+<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) <optional>null treatment</optional> [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
+<replaceable>function_name</replaceable> (<optional><replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ... </optional></optional>) <optional>null treatment</optional> [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER <replaceable>window_name</replaceable>
<replaceable>function_name</replaceable> ( * ) [ FILTER ( WHERE <replaceable>filter_clause</replaceable> ) ] OVER ( <replaceable class="parameter">window_definition</replaceable> )
</synopsis>
@@ -1873,7 +1873,9 @@ EXCLUDE NO OTHERS
<para>
Here, <replaceable>expression</replaceable> represents any value
- expression that does not itself contain window function calls.
+ expression that does not itself contain window function calls. Some
+ non-aggregate functions allow a <literal>null treatment</literal> clause,
+ described in <xref linkend="functions-window"/>.
</para>
<para>
@@ -2048,7 +2050,7 @@ EXCLUDE NO OTHERS
<para>
The built-in window functions are described in <xref
- linkend="functions-window-table"/>. Other window functions can be added by
+ linkend="functions-window-table"/>. Other window functions can be added by
the user. Also, any built-in or user-defined general-purpose or
statistical aggregate can be used as a window function. (Ordered-set
and hypothetical-set aggregates cannot presently be used as window functions.)
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 2f250d2c57..46a8959cb2 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -518,7 +518,7 @@ 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 functions YES
T618 NTH_VALUE function NO function exists, but some options missing
T619 Nested window functions NO
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 9a1acce2b5..d93a44633e 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -69,6 +69,10 @@ typedef struct WindowObjectData
int readptr; /* tuplestore read pointer for this fn */
int64 markpos; /* row that markptr is positioned on */
int64 seekpos; /* row that readptr is positioned on */
+ int ignore_nulls; /* ignore nulls */
+ int64 *win_nonnulls; /* tracks non-nulls in ignore nulls mode */
+ int nonnulls_size; /* track size of the win_nonnulls array */
+ int nonnulls_len; /* track length of the win_nonnulls array */
} WindowObjectData;
/*
@@ -96,6 +100,7 @@ typedef struct WindowStatePerFuncData
bool plain_agg; /* is it just a plain aggregate function? */
int aggno; /* if so, index of its WindowStatePerAggData */
+ int ignore_nulls; /* ignore nulls */
WindowObject winobj; /* object used in window function API */
} WindowStatePerFuncData;
@@ -1263,6 +1268,15 @@ begin_partition(WindowAggState *winstate)
winobj->markpos = -1;
winobj->seekpos = -1;
+
+
+ /* reallocate null check */
+ if (perfuncstate->winobj->ignore_nulls == IGNORE_NULLS)
+ {
+ perfuncstate->winobj->win_nonnulls = palloc_array(int64, 16);
+ perfuncstate->winobj->nonnulls_size = 16;
+ perfuncstate->winobj->nonnulls_len = 0;
+ }
}
}
@@ -1383,7 +1397,9 @@ release_partition(WindowAggState *winstate)
/* Release any partition-local state of this window function */
if (perfuncstate->winobj)
+ {
perfuncstate->winobj->localmem = NULL;
+ }
}
/*
@@ -2619,14 +2635,17 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
elog(ERROR, "WindowFunc with winref %u assigned to WindowAgg with winref %u",
wfunc->winref, node->winref);
- /* Look for a previous duplicate window function */
+ /*
+ * Look for a previous duplicate window function, which needs the same
+ * ignore_nulls value
+ */
for (i = 0; i <= wfuncno; i++)
{
if (equal(wfunc, perfunc[i].wfunc) &&
!contain_volatile_functions((Node *) wfunc))
break;
}
- if (i <= wfuncno)
+ if (i <= wfuncno && wfunc->ignore_nulls == perfunc[i].ignore_nulls)
{
/* Found a match to an existing entry, so just mark it */
wfuncstate->wfuncno = i;
@@ -2679,6 +2698,13 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
winobj->argstates = wfuncstate->args;
winobj->localmem = NULL;
perfuncstate->winobj = winobj;
+ winobj->ignore_nulls = wfunc->ignore_nulls;
+ if (winobj->ignore_nulls == PARSER_IGNORE_NULLS)
+ {
+ winobj->win_nonnulls = palloc_array(int64, 16);
+ winobj->nonnulls_size = 16;
+ winobj->nonnulls_len = 0;
+ }
/* It's a real window function, so set up to call it. */
fmgr_info_cxt(wfunc->winfnoid, &perfuncstate->flinfo,
@@ -3220,6 +3246,38 @@ window_gettupleslot(WindowObject winobj, int64 pos, TupleTableSlot *slot)
***********************************************************************/
+/*
+ * WinCheckAndInitializeNullTreatment
+ * Check null treatment clause and sets ignore_nulls
+ *
+ * Window functions should call this to check if they are being called with
+ * a null treatment clause when they don't allow it, or to set ignore_nulls.
+ */
+void
+WinCheckAndInitializeNullTreatment(WindowObject winobj,
+ bool allowNullTreatment,
+ FunctionCallInfo fcinfo)
+{
+ if (winobj->ignore_nulls != NO_NULLTREATMENT && !allowNullTreatment)
+ {
+ HeapTuple proctup;
+ Form_pg_proc procform;
+ Oid funcid;
+
+ funcid = fcinfo->flinfo->fn_oid;
+ proctup = SearchSysCache1(PROCOID,
+ ObjectIdGetDatum(funcid));
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, "cache lookup failed for function %u", funcid);
+ procform = (Form_pg_proc) GETSTRUCT(proctup);
+ elog(ERROR, "function %s does not allow RESPECT/IGNORE NULLS",
+ NameStr(procform->proname));
+ }
+ else if (winobj->ignore_nulls == PARSER_IGNORE_NULLS)
+ winobj->ignore_nulls = IGNORE_NULLS;
+
+}
+
/*
* WinGetPartitionLocalMemory
* Get working memory that lives till end of partition processing
@@ -3354,6 +3412,268 @@ WinRowsArePeers(WindowObject winobj, int64 pos1, int64 pos2)
return res;
}
+/*
+ * increment_nonnulls
+ * For IGNORE NULLS, add the current position to the nonnulls array,
+ * doubling the array capacity if needed.
+ */
+static void
+increment_nonnulls(WindowObject winobj, int64 pos)
+{
+ if (winobj->nonnulls_len == winobj->nonnulls_size)
+ {
+ winobj->nonnulls_size *= 2;
+ winobj->win_nonnulls =
+ repalloc_array(winobj->win_nonnulls,
+ int64,
+ winobj->nonnulls_size);
+ }
+ winobj->win_nonnulls[winobj->nonnulls_len] = pos;
+ winobj->nonnulls_len++;
+}
+
+/*
+ * ignorenulls_getfuncarginpartition
+ * For IGNORE NULLS, get the next nonnull value in the partition, moving forward or backward
+ * until we find a value or reach the partition's end.
+ */
+static Datum
+ignorenulls_getfuncarginpartition(WindowObject winobj, int argno,
+ int relpos, int seektype, bool set_mark,
+ bool *isnull, bool *isout)
+{
+ WindowAggState *winstate;
+ ExprContext *econtext;
+ TupleTableSlot *slot;
+ Datum datum;
+ bool gottuple;
+ int64 abs_pos;
+ int notnull_offset;
+ int notnull_relpos;
+ int forward;
+ int i;
+
+ Assert(WindowObjectIsValid(winobj));
+ winstate = winobj->winstate;
+ econtext = winstate->ss.ps.ps_ExprContext;
+ slot = winstate->temp_slot_1;
+ notnull_offset = 0;
+ notnull_relpos = abs(relpos);
+ forward = relpos > 0 ? 1 : -1;
+
+ switch (seektype)
+ {
+ case WINDOW_SEEK_CURRENT:
+ abs_pos = winstate->currentpos;
+ break;
+ case WINDOW_SEEK_HEAD:
+ abs_pos = 0;
+ break;
+ case WINDOW_SEEK_TAIL:
+ spool_tuples(winstate, -1);
+ abs_pos = winstate->spooled_rows - 1;
+ break;
+ default:
+ elog(ERROR, "unrecognized window seek type: %d", seektype);
+ abs_pos = 0; /* keep compiler quiet */
+ break;
+ }
+
+ if (forward == -1)
+ goto check_partition;
+
+ /* if we're moving forward, store previous rows */
+ for (i = 0; i < winobj->nonnulls_len; ++i)
+ {
+ if (winobj->win_nonnulls[i] > abs_pos)
+ {
+ abs_pos = winobj->win_nonnulls[i];
+ ++notnull_offset;
+ if (notnull_offset == notnull_relpos)
+ {
+ if (isout)
+ *isout = false;
+ window_gettupleslot(winobj, abs_pos, slot);
+ econtext->ecxt_outertuple = slot;
+ return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
+ econtext, isnull);
+ }
+ }
+ }
+
+check_partition:
+ do
+ {
+ abs_pos += forward;
+ gottuple = window_gettupleslot(winobj, abs_pos, slot);
+
+ if (!gottuple)
+ {
+ if (isout)
+ *isout = true;
+ *isnull = true;
+ return (Datum) 0;
+ }
+
+ if (isout)
+ *isout = false;
+ econtext->ecxt_outertuple = slot;
+ datum = ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
+ econtext, isnull);
+
+ if (!*isnull)
+ {
+ ++notnull_offset;
+ increment_nonnulls(winobj, abs_pos);
+ }
+ } while (notnull_offset < notnull_relpos);
+
+ if (set_mark)
+ WinSetMarkPosition(winobj, abs_pos);
+ return datum;
+}
+
+/*
+ * ignorenulls_getfuncarginframe
+ * For IGNORE NULLS, get the next nonnull value in the frame, moving forward or backward
+ * until we find a value or reach the frame's end.
+ */
+static Datum
+ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
+ int relpos, int seektype, bool set_mark,
+ bool *isnull, bool *isout)
+{
+ WindowAggState *winstate;
+ ExprContext *econtext;
+ TupleTableSlot *slot;
+ Datum datum;
+ bool gottuple;
+ int64 abs_pos;
+ int64 mark_pos;
+ int notnull_offset;
+ int notnull_relpos;
+ int forward;
+ int i;
+
+ Assert(WindowObjectIsValid(winobj));
+ winstate = winobj->winstate;
+ econtext = winstate->ss.ps.ps_ExprContext;
+ slot = winstate->temp_slot_1;
+ datum = (Datum) 0;
+ notnull_offset = 0;
+ notnull_relpos = abs(relpos);
+
+ switch (seektype)
+ {
+ case WINDOW_SEEK_CURRENT:
+ elog(ERROR, "WINDOW_SEEK_CURRENT is not supported for WinGetFuncArgInFrame");
+ abs_pos = mark_pos = 0; /* keep compiler quiet */
+ break;
+ case WINDOW_SEEK_HEAD:
+ /* rejecting relpos < 0 is easy and simplifies code below */
+ if (relpos < 0)
+ goto out_of_frame;
+ update_frameheadpos(winstate);
+ abs_pos = winstate->frameheadpos;
+ forward = 1;
+ break;
+ case WINDOW_SEEK_TAIL:
+ /* rejecting relpos > 0 is easy and simplifies code below */
+ if (relpos > 0)
+ goto out_of_frame;
+ update_frametailpos(winstate);
+ abs_pos = winstate->frametailpos - 1;
+ forward = -1;
+ goto check_frame;
+ break;
+ default:
+ elog(ERROR, "unrecognized window seek type: %d", seektype);
+ abs_pos = mark_pos = 0; /* keep compiler quiet */
+ break;
+ }
+
+ /*
+ * Store previous rows. Only possible in SEEK_HEAD mode
+ */
+ for (i = 0; i < winobj->nonnulls_len; ++i)
+ {
+ int inframe;
+
+ if (winobj->win_nonnulls[i] < winobj->markpos)
+ continue;
+ if (!window_gettupleslot(winobj, winobj->win_nonnulls[i], slot))
+ continue;
+
+ inframe = row_is_in_frame(winstate, winobj->win_nonnulls[i], slot);
+ if (inframe <= 0)
+ {
+ if (inframe == -1 && set_mark)
+ WinSetMarkPosition(winobj, winobj->win_nonnulls[i]);
+ continue;
+ }
+
+ abs_pos = winobj->win_nonnulls[i] + 1;
+ ++notnull_offset;
+
+ if (notnull_offset > notnull_relpos)
+ {
+ if (isout)
+ *isout = false;
+ econtext->ecxt_outertuple = slot;
+ return ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
+ econtext, isnull);
+ }
+ }
+
+check_frame:
+ do
+ {
+ int inframe;
+
+ if (!window_gettupleslot(winobj, abs_pos, slot))
+ goto out_of_frame;
+
+ inframe = row_is_in_frame(winstate, abs_pos, slot);
+ if (inframe == -1)
+ goto out_of_frame;
+ else if (inframe == 0)
+ goto advance;
+
+ gottuple = window_gettupleslot(winobj, abs_pos, slot);
+
+ if (!gottuple)
+ {
+ if (isout)
+ *isout = true;
+ *isnull = true;
+ return (Datum) 0;
+ }
+
+ if (isout)
+ *isout = false;
+ econtext->ecxt_outertuple = slot;
+ datum = ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
+ econtext, isnull);
+
+ if (!*isnull)
+ {
+ ++notnull_offset;
+ increment_nonnulls(winobj, abs_pos);
+ }
+
+advance:
+ abs_pos += forward;
+ } while (notnull_offset <= notnull_relpos);
+
+ return datum;
+
+out_of_frame:
+ if (isout)
+ *isout = true;
+ *isnull = true;
+ return (Datum) 0;
+}
+
/*
* WinGetFuncArgInPartition
* Evaluate a window function's argument expression on a specified
@@ -3388,6 +3708,10 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno,
econtext = winstate->ss.ps.ps_ExprContext;
slot = winstate->temp_slot_1;
+ if (winobj->ignore_nulls == IGNORE_NULLS && relpos != 0)
+ return ignorenulls_getfuncarginpartition(winobj, argno, relpos, seektype,
+ set_mark, isnull, isout);
+
switch (seektype)
{
case WINDOW_SEEK_CURRENT:
@@ -3476,6 +3800,10 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
econtext = winstate->ss.ps.ps_ExprContext;
slot = winstate->temp_slot_1;
+ if (winobj->ignore_nulls == IGNORE_NULLS)
+ return ignorenulls_getfuncarginframe(winobj, argno, relpos, seektype,
+ set_mark, isnull, isout);
+
switch (seektype)
{
case WINDOW_SEEK_CURRENT:
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 43dfecfb47..e7091d7468 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2570,6 +2570,7 @@ eval_const_expressions_mutator(Node *node,
newexpr->winref = expr->winref;
newexpr->winstar = expr->winstar;
newexpr->winagg = expr->winagg;
+ newexpr->ignore_nulls = expr->ignore_nulls;
newexpr->location = expr->location;
return (Node *) newexpr;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index d7f9c00c40..7ca616e8f7 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -632,7 +632,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%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
-%type <ival> opt_window_exclusion_clause
+%type <ival> null_treatment opt_window_exclusion_clause
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
%type <boolean> opt_unique_null_treatment
@@ -730,7 +730,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
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 INDENT 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
@@ -765,7 +765,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
RANGE READ REAL REASSIGN RECURSIVE REF_P REFERENCES REFERENCING
REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
- RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
+ RESET RESPECT_P RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP
ROUTINE ROUTINES ROW ROWS RULE
SAVEPOINT SCALAR SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT
@@ -15723,7 +15723,7 @@ func_application: func_name '(' ')'
* (Note that many of the special SQL functions wouldn't actually make any
* sense as functional index entries, but we ignore that consideration here.)
*/
-func_expr: func_application within_group_clause filter_clause over_clause
+func_expr: func_application within_group_clause filter_clause null_treatment over_clause
{
FuncCall *n = (FuncCall *) $1;
@@ -15756,7 +15756,8 @@ func_expr: func_application within_group_clause filter_clause over_clause
n->agg_within_group = true;
}
n->agg_filter = $3;
- n->over = $4;
+ n->ignore_nulls = $4;
+ n->over = $5;
$$ = (Node *) n;
}
| json_aggregate_func filter_clause over_clause
@@ -16352,6 +16353,12 @@ filter_clause:
/*
* Window Definitions
*/
+null_treatment:
+ IGNORE_P NULLS_P { $$ = PARSER_IGNORE_NULLS; }
+ | RESPECT_P NULLS_P { $$ = PARSER_RESPECT_NULLS; }
+ | /*EMPTY*/ { $$ = NO_NULLTREATMENT; }
+ ;
+
window_clause:
WINDOW window_definition_list { $$ = $2; }
| /*EMPTY*/ { $$ = NIL; }
@@ -17789,6 +17796,7 @@ unreserved_keyword:
| HOUR_P
| IDENTITY_P
| IF_P
+ | IGNORE_P
| IMMEDIATE
| IMMUTABLE
| IMPLICIT_P
@@ -17906,6 +17914,7 @@ unreserved_keyword:
| REPLACE
| REPLICA
| RESET
+ | RESPECT_P
| RESTART
| RESTRICT
| RETURN
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 583bbbf232..3772c514b1 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -98,6 +98,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
bool agg_star = (fn ? fn->agg_star : false);
bool agg_distinct = (fn ? fn->agg_distinct : false);
bool func_variadic = (fn ? fn->func_variadic : false);
+ int ignore_nulls = (fn ? fn->ignore_nulls : 0);
CoercionForm funcformat = (fn ? fn->funcformat : COERCE_EXPLICIT_CALL);
bool could_be_projection;
Oid rettype;
@@ -514,6 +515,13 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
errmsg("%s is not an ordered-set aggregate, so it cannot have WITHIN GROUP",
NameListToString(funcname)),
parser_errposition(pstate, location)));
+
+ /* It also can't treat nulls as a window function */
+ if (ignore_nulls != NO_NULLTREATMENT)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("aggregate functions do not accept RESPECT/IGNORE NULLS"),
+ parser_errposition(pstate, location)));
}
}
else if (fdresult == FUNCDETAIL_WINDOWFUNC)
@@ -834,6 +842,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
wfunc->winstar = agg_star;
wfunc->winagg = (fdresult == FUNCDETAIL_AGGREGATE);
wfunc->aggfilter = agg_filter;
+ wfunc->ignore_nulls = ignore_nulls;
wfunc->runCondition = NIL;
wfunc->location = location;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 54dad97555..4c0837cb2a 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -11021,7 +11021,12 @@ get_windowfunc_expr_helper(WindowFunc *wfunc, deparse_context *context,
get_rule_expr((Node *) wfunc->aggfilter, context, false);
}
- appendStringInfoString(buf, ") OVER ");
+ appendStringInfoString(buf, ") ");
+
+ if (wfunc->ignore_nulls == PARSER_IGNORE_NULLS)
+ appendStringInfoString(buf, "IGNORE NULLS ");
+
+ appendStringInfoString(buf, "OVER ");
foreach(l, context->windowClause)
{
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index bb35f3bc4a..969f02aa59 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -86,6 +86,7 @@ window_row_number(PG_FUNCTION_ARGS)
WindowObject winobj = PG_WINDOW_OBJECT();
int64 curpos = WinGetCurrentPosition(winobj);
+ WinCheckAndInitializeNullTreatment(winobj, false, fcinfo);
WinSetMarkPosition(winobj, curpos);
PG_RETURN_INT64(curpos + 1);
}
@@ -141,6 +142,7 @@ window_rank(PG_FUNCTION_ARGS)
rank_context *context;
bool up;
+ WinCheckAndInitializeNullTreatment(winobj, false, fcinfo);
up = rank_up(winobj);
context = (rank_context *)
WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
@@ -203,6 +205,7 @@ window_dense_rank(PG_FUNCTION_ARGS)
rank_context *context;
bool up;
+ WinCheckAndInitializeNullTreatment(winobj, false, fcinfo);
up = rank_up(winobj);
context = (rank_context *)
WinGetPartitionLocalMemory(winobj, sizeof(rank_context));
@@ -266,6 +269,7 @@ window_percent_rank(PG_FUNCTION_ARGS)
int64 totalrows = WinGetPartitionRowCount(winobj);
Assert(totalrows > 0);
+ WinCheckAndInitializeNullTreatment(winobj, false, fcinfo);
up = rank_up(winobj);
context = (rank_context *)
@@ -335,6 +339,7 @@ window_cume_dist(PG_FUNCTION_ARGS)
int64 totalrows = WinGetPartitionRowCount(winobj);
Assert(totalrows > 0);
+ WinCheckAndInitializeNullTreatment(winobj, false, fcinfo);
up = rank_up(winobj);
context = (rank_context *)
@@ -413,6 +418,7 @@ window_ntile(PG_FUNCTION_ARGS)
WindowObject winobj = PG_WINDOW_OBJECT();
ntile_context *context;
+ WinCheckAndInitializeNullTreatment(winobj, false, fcinfo);
context = (ntile_context *)
WinGetPartitionLocalMemory(winobj, sizeof(ntile_context));
@@ -535,6 +541,7 @@ leadlag_common(FunctionCallInfo fcinfo,
bool isnull;
bool isout;
+ WinCheckAndInitializeNullTreatment(winobj, true, fcinfo);
if (withoffset)
{
offset = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
@@ -652,6 +659,7 @@ window_first_value(PG_FUNCTION_ARGS)
Datum result;
bool isnull;
+ WinCheckAndInitializeNullTreatment(winobj, true, fcinfo);
result = WinGetFuncArgInFrame(winobj, 0,
0, WINDOW_SEEK_HEAD, true,
&isnull, NULL);
@@ -673,6 +681,7 @@ window_last_value(PG_FUNCTION_ARGS)
Datum result;
bool isnull;
+ WinCheckAndInitializeNullTreatment(winobj, true, fcinfo);
result = WinGetFuncArgInFrame(winobj, 0,
0, WINDOW_SEEK_TAIL, true,
&isnull, NULL);
@@ -696,6 +705,7 @@ window_nth_value(PG_FUNCTION_ARGS)
bool isnull;
int32 nth;
+ WinCheckAndInitializeNullTreatment(winobj, true, fcinfo);
nth = DatumGetInt32(WinGetFuncArgCurrent(winobj, 1, &isnull));
if (isnull)
PG_RETURN_NULL();
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ffe155ee20..51e2ea19c5 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -439,6 +439,7 @@ typedef struct FuncCall
List *agg_order; /* ORDER BY (list of SortBy) */
Node *agg_filter; /* FILTER clause, if any */
struct WindowDef *over; /* OVER clause, if any */
+ int ignore_nulls; /* ignore nulls for window function */
bool agg_within_group; /* ORDER BY appeared in WITHIN GROUP */
bool agg_star; /* argument was really '*' */
bool agg_distinct; /* arguments were labeled DISTINCT */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 59e7bb26bb..cc4221ab0d 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -576,6 +576,18 @@ typedef struct GroupingFunc
* Collation information is irrelevant for the query jumbling, as is the
* internal state information of the node like "winstar" and "winagg".
*/
+
+/*
+ * Null Treatment options. If specified, initially set to PARSER_IGNORE
+ * or PARSER_RESPECT. PARSER_IGNORE_NULLS is then converted to IGNORE_NULLS
+ * if the window function allows the null treatment clause.
+ */
+#define IGNORE_NULLS 4
+#define RESPECT_NULLS 3
+#define PARSER_IGNORE_NULLS 2
+#define PARSER_RESPECT_NULLS 1
+#define NO_NULLTREATMENT 0
+
typedef struct WindowFunc
{
Expr xpr;
@@ -599,6 +611,8 @@ typedef struct WindowFunc
bool winstar pg_node_attr(query_jumble_ignore);
/* is function a simple aggregate? */
bool winagg pg_node_attr(query_jumble_ignore);
+ /* ignore nulls. one of the Null Treatment options */
+ int ignore_nulls;
/* token location, or -1 if unknown */
ParseLoc location;
} WindowFunc;
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index cf2917ad07..4d662b5276 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -202,6 +202,7 @@ PG_KEYWORD("hold", HOLD, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("hour", HOUR_P, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("identity", IDENTITY_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("if", IF_P, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("ignore", IGNORE_P, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("ilike", ILIKE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("immediate", IMMEDIATE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("immutable", IMMUTABLE, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -377,6 +378,7 @@ PG_KEYWORD("repeatable", REPEATABLE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("replace", REPLACE, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("respect", RESPECT_P, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("return", RETURN, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/windowapi.h b/src/include/windowapi.h
index cb2ece166b..b1595d308d 100644
--- a/src/include/windowapi.h
+++ b/src/include/windowapi.h
@@ -41,6 +41,10 @@ typedef struct WindowObjectData *WindowObject;
#define WindowObjectIsValid(winobj) \
((winobj) != NULL && IsA(winobj, WindowObjectData))
+extern void WinCheckAndInitializeNullTreatment(WindowObject winobj,
+ bool allowNullTreatment,
+ FunctionCallInfo fcinfo);
+
extern void *WinGetPartitionLocalMemory(WindowObject winobj, Size sz);
extern int64 WinGetCurrentPosition(WindowObject winobj);
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 23d1463df2..361596a526 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -5403,3 +5403,287 @@ SELECT * FROM pg_temp.f(2);
{5}
(5 rows)
+-- IGNORE NULLS tests
+CREATE TEMPORARY TABLE planets (
+ name text,
+ distance text,
+ orbit integer
+);
+INSERT INTO planets VALUES
+ ('mercury', 'close', 88),
+ ('venus', 'close', 224),
+ ('earth', 'close', NULL),
+ ('mars', 'close', NULL),
+ ('jupiter', 'close', 4332),
+ ('saturn', 'far', 24491),
+ ('uranus', 'far', NULL),
+ ('neptune', 'far', 60182),
+ ('pluto', 'far', 90560),
+ ('xyzzy', 'far', NULL);
+-- test ruleutils
+CREATE VIEW planets_view AS
+SELECT name,
+ orbit,
+ lag(orbit) OVER w AS lag,
+ lag(orbit) RESPECT NULLS OVER w AS lag_respect,
+ lag(orbit) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+NOTICE: view "planets_view" will be a temporary view
+SELECT pg_get_viewdef('planets_view');
+ pg_get_viewdef
+--------------------------------------------------
+ SELECT name, +
+ orbit, +
+ lag(orbit) OVER w AS lag, +
+ lag(orbit) OVER w AS lag_respect, +
+ lag(orbit) IGNORE NULLS OVER w AS lag_ignore+
+ FROM planets +
+ WINDOW w AS (ORDER BY name);
+(1 row)
+
+-- lag
+SELECT name,
+ orbit,
+ lag(orbit) OVER w AS lag,
+ lag(orbit) RESPECT NULLS OVER w AS lag_respect,
+ lag(orbit) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+ name | orbit | lag | lag_respect | lag_ignore
+---------+-------+-------+-------------+------------
+ earth | | | |
+ jupiter | 4332 | | |
+ mars | | 4332 | 4332 | 4332
+ mercury | 88 | | | 4332
+ neptune | 60182 | 88 | 88 | 88
+ pluto | 90560 | 60182 | 60182 | 60182
+ saturn | 24491 | 90560 | 90560 | 90560
+ uranus | | 24491 | 24491 | 24491
+ venus | 224 | | | 24491
+ xyzzy | | 224 | 224 | 224
+(10 rows)
+
+-- lead
+SELECT name,
+ orbit,
+ lead(orbit) OVER w AS lead,
+ lead(orbit) RESPECT NULLS OVER w AS lead_respect,
+ lead(orbit) IGNORE NULLS OVER w AS lead_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+ name | orbit | lead | lead_respect | lead_ignore
+---------+-------+-------+--------------+-------------
+ earth | | 4332 | 4332 | 4332
+ jupiter | 4332 | | | 88
+ mars | | 88 | 88 | 88
+ mercury | 88 | 60182 | 60182 | 60182
+ neptune | 60182 | 90560 | 90560 | 90560
+ pluto | 90560 | 24491 | 24491 | 24491
+ saturn | 24491 | | | 224
+ uranus | | 224 | 224 | 224
+ venus | 224 | | |
+ xyzzy | | | |
+(10 rows)
+
+-- first_value
+SELECT name,
+ orbit,
+ first_value(orbit) RESPECT NULLS OVER w1,
+ first_value(orbit) IGNORE NULLS OVER w1,
+ first_value(orbit) RESPECT NULLS OVER w2,
+ first_value(orbit) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+ name | orbit | first_value | first_value | first_value | first_value
+---------+-------+-------------+-------------+-------------+-------------
+ earth | | | 4332 | | 4332
+ jupiter | 4332 | | 4332 | | 4332
+ mars | | | 4332 | | 4332
+ mercury | 88 | | 4332 | 4332 | 4332
+ neptune | 60182 | | 4332 | | 88
+ pluto | 90560 | | 4332 | 88 | 88
+ saturn | 24491 | | 4332 | 60182 | 60182
+ uranus | | | 4332 | 90560 | 90560
+ venus | 224 | | 4332 | 24491 | 24491
+ xyzzy | | | 4332 | | 224
+(10 rows)
+
+-- nth_value
+SELECT name,
+ orbit,
+ nth_value(orbit, 2) RESPECT NULLS OVER w1,
+ nth_value(orbit, 2) IGNORE NULLS OVER w1,
+ nth_value(orbit, 2) RESPECT NULLS OVER w2,
+ nth_value(orbit, 2) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+ name | orbit | nth_value | nth_value | nth_value | nth_value
+---------+-------+-----------+-----------+-----------+-----------
+ earth | | 4332 | 88 | 4332 |
+ jupiter | 4332 | 4332 | 88 | 4332 | 88
+ mars | | 4332 | 88 | 4332 | 88
+ mercury | 88 | 4332 | 88 | | 88
+ neptune | 60182 | 4332 | 88 | 88 | 60182
+ pluto | 90560 | 4332 | 88 | 60182 | 60182
+ saturn | 24491 | 4332 | 88 | 90560 | 90560
+ uranus | | 4332 | 88 | 24491 | 24491
+ venus | 224 | 4332 | 88 | | 224
+ xyzzy | | 4332 | 88 | 224 |
+(10 rows)
+
+-- last_value
+SELECT name,
+ orbit,
+ last_value(orbit) RESPECT NULLS OVER w1,
+ last_value(orbit) IGNORE NULLS OVER w1,
+ last_value(orbit) RESPECT NULLS OVER w2,
+ last_value(orbit) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+ name | orbit | last_value | last_value | last_value | last_value
+---------+-------+------------+------------+------------+------------
+ earth | | | 224 | | 4332
+ jupiter | 4332 | | 224 | 88 | 88
+ mars | | | 224 | 60182 | 60182
+ mercury | 88 | | 224 | 90560 | 90560
+ neptune | 60182 | | 224 | 24491 | 24491
+ pluto | 90560 | | 224 | | 24491
+ saturn | 24491 | | 224 | 224 | 224
+ uranus | | | 224 | | 224
+ venus | 224 | | 224 | | 224
+ xyzzy | | | 224 | | 224
+(10 rows)
+
+-- exclude current row
+SELECT name,
+ orbit,
+ first_value(orbit) IGNORE NULLS OVER w,
+ last_value(orbit) IGNORE NULLS OVER w,
+ nth_value(orbit, 2) IGNORE NULLS OVER w,
+ lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+ lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW)
+;
+ name | orbit | first_value | last_value | nth_value | lead_ignore | lag_ignore
+---------+-------+-------------+------------+-----------+-------------+------------
+ earth | | 4332 | 4332 | | 4332 |
+ jupiter | 4332 | 88 | 88 | | 88 |
+ mars | | 4332 | 60182 | 88 | 88 | 4332
+ mercury | 88 | 4332 | 90560 | 60182 | 60182 | 4332
+ neptune | 60182 | 88 | 24491 | 90560 | 90560 | 88
+ pluto | 90560 | 88 | 24491 | 60182 | 24491 | 60182
+ saturn | 24491 | 60182 | 224 | 90560 | 224 | 90560
+ uranus | | 90560 | 224 | 24491 | 224 | 24491
+ venus | 224 | 24491 | 24491 | | | 24491
+ xyzzy | | 224 | 224 | | | 224
+(10 rows)
+
+-- valid and invalid functions
+SELECT sum(orbit) OVER () FROM planets; -- succeeds
+ sum
+--------
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+ 179877
+(10 rows)
+
+SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- fails
+ERROR: aggregate functions do not accept RESPECT/IGNORE NULLS
+LINE 1: SELECT sum(orbit) RESPECT NULLS OVER () FROM planets;
+ ^
+SELECT sum(orbit) IGNORE NULLS OVER () FROM planets; -- fails
+ERROR: aggregate functions do not accept RESPECT/IGNORE NULLS
+LINE 1: SELECT sum(orbit) IGNORE NULLS OVER () FROM planets;
+ ^
+SELECT row_number() OVER () FROM planets; -- succeeds
+ row_number
+------------
+ 1
+ 2
+ 3
+ 4
+ 5
+ 6
+ 7
+ 8
+ 9
+ 10
+(10 rows)
+
+SELECT row_number() RESPECT NULLS OVER () FROM planets; -- fails
+ERROR: function row_number does not allow RESPECT/IGNORE NULLS
+SELECT row_number() IGNORE NULLS OVER () FROM planets; -- fails
+ERROR: function row_number does not allow RESPECT/IGNORE NULLS
+-- test two consecutive nulls
+update planets set orbit=null where name='jupiter';
+SELECT name,
+ orbit,
+ first_value(orbit) IGNORE NULLS OVER w,
+ last_value(orbit) IGNORE NULLS OVER w,
+ nth_value(orbit, 2) IGNORE NULLS OVER w,
+ lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+ lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+ name | orbit | first_value | last_value | nth_value | lead_ignore | lag_ignore
+---------+-------+-------------+------------+-----------+-------------+------------
+ earth | | | | | 88 |
+ jupiter | | 88 | 88 | | 88 |
+ mars | | 88 | 60182 | 60182 | 88 |
+ mercury | 88 | 88 | 90560 | 60182 | 60182 |
+ neptune | 60182 | 88 | 24491 | 60182 | 90560 | 88
+ pluto | 90560 | 88 | 24491 | 60182 | 24491 | 60182
+ saturn | 24491 | 60182 | 224 | 90560 | 224 | 90560
+ uranus | | 90560 | 224 | 24491 | 224 | 24491
+ venus | 224 | 24491 | 224 | 224 | | 24491
+ xyzzy | | 224 | 224 | | | 224
+(10 rows)
+
+-- test partitions
+SELECT name,
+ distance,
+ orbit,
+ first_value(orbit) IGNORE NULLS OVER w,
+ last_value(orbit) IGNORE NULLS OVER w,
+ nth_value(orbit, 2) IGNORE NULLS OVER w,
+ lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+ lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (PARTITION BY distance ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+ name | distance | orbit | first_value | last_value | nth_value | lead_ignore | lag_ignore
+---------+----------+-------+-------------+------------+-----------+-------------+------------
+ earth | close | | | | | 88 |
+ jupiter | close | | 88 | 88 | | 88 |
+ mars | close | | 88 | 224 | 224 | 88 |
+ mercury | close | 88 | 88 | 224 | 224 | 224 |
+ venus | close | 224 | 88 | 224 | 224 | | 88
+ neptune | far | 60182 | 60182 | 24491 | 90560 | 90560 |
+ pluto | far | 90560 | 60182 | 24491 | 90560 | 24491 | 60182
+ saturn | far | 24491 | 60182 | 24491 | 90560 | | 90560
+ uranus | far | | 90560 | 24491 | 24491 | | 24491
+ xyzzy | far | | 24491 | 24491 | | | 24491
+(10 rows)
+
+--cleanup
+DROP TABLE planets CASCADE;
+NOTICE: drop cascades to view planets_view
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 02f105f070..3bdd2d66e2 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1958,3 +1958,140 @@ $$ LANGUAGE SQL STABLE;
EXPLAIN (costs off) SELECT * FROM pg_temp.f(2);
SELECT * FROM pg_temp.f(2);
+
+-- IGNORE NULLS tests
+
+CREATE TEMPORARY TABLE planets (
+ name text,
+ distance text,
+ orbit integer
+);
+
+INSERT INTO planets VALUES
+ ('mercury', 'close', 88),
+ ('venus', 'close', 224),
+ ('earth', 'close', NULL),
+ ('mars', 'close', NULL),
+ ('jupiter', 'close', 4332),
+ ('saturn', 'far', 24491),
+ ('uranus', 'far', NULL),
+ ('neptune', 'far', 60182),
+ ('pluto', 'far', 90560),
+ ('xyzzy', 'far', NULL);
+
+-- test ruleutils
+CREATE VIEW planets_view AS
+SELECT name,
+ orbit,
+ lag(orbit) OVER w AS lag,
+ lag(orbit) RESPECT NULLS OVER w AS lag_respect,
+ lag(orbit) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+SELECT pg_get_viewdef('planets_view');
+
+-- lag
+SELECT name,
+ orbit,
+ lag(orbit) OVER w AS lag,
+ lag(orbit) RESPECT NULLS OVER w AS lag_respect,
+ lag(orbit) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+
+-- lead
+SELECT name,
+ orbit,
+ lead(orbit) OVER w AS lead,
+ lead(orbit) RESPECT NULLS OVER w AS lead_respect,
+ lead(orbit) IGNORE NULLS OVER w AS lead_ignore
+FROM planets
+WINDOW w AS (ORDER BY name)
+;
+
+-- first_value
+SELECT name,
+ orbit,
+ first_value(orbit) RESPECT NULLS OVER w1,
+ first_value(orbit) IGNORE NULLS OVER w1,
+ first_value(orbit) RESPECT NULLS OVER w2,
+ first_value(orbit) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+
+-- nth_value
+SELECT name,
+ orbit,
+ nth_value(orbit, 2) RESPECT NULLS OVER w1,
+ nth_value(orbit, 2) IGNORE NULLS OVER w1,
+ nth_value(orbit, 2) RESPECT NULLS OVER w2,
+ nth_value(orbit, 2) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+
+-- last_value
+SELECT name,
+ orbit,
+ last_value(orbit) RESPECT NULLS OVER w1,
+ last_value(orbit) IGNORE NULLS OVER w1,
+ last_value(orbit) RESPECT NULLS OVER w2,
+ last_value(orbit) IGNORE NULLS OVER w2
+FROM planets
+WINDOW w1 AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
+ w2 AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+
+-- exclude current row
+SELECT name,
+ orbit,
+ first_value(orbit) IGNORE NULLS OVER w,
+ last_value(orbit) IGNORE NULLS OVER w,
+ nth_value(orbit, 2) IGNORE NULLS OVER w,
+ lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+ lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW)
+;
+
+-- valid and invalid functions
+SELECT sum(orbit) OVER () FROM planets; -- succeeds
+SELECT sum(orbit) RESPECT NULLS OVER () FROM planets; -- fails
+SELECT sum(orbit) IGNORE NULLS OVER () FROM planets; -- fails
+SELECT row_number() OVER () FROM planets; -- succeeds
+SELECT row_number() RESPECT NULLS OVER () FROM planets; -- fails
+SELECT row_number() IGNORE NULLS OVER () FROM planets; -- fails
+
+-- test two consecutive nulls
+update planets set orbit=null where name='jupiter';
+SELECT name,
+ orbit,
+ first_value(orbit) IGNORE NULLS OVER w,
+ last_value(orbit) IGNORE NULLS OVER w,
+ nth_value(orbit, 2) IGNORE NULLS OVER w,
+ lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+ lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+
+-- test partitions
+SELECT name,
+ distance,
+ orbit,
+ first_value(orbit) IGNORE NULLS OVER w,
+ last_value(orbit) IGNORE NULLS OVER w,
+ nth_value(orbit, 2) IGNORE NULLS OVER w,
+ lead(orbit, 1) IGNORE NULLS OVER w AS lead_ignore,
+ lag(orbit, 1) IGNORE NULLS OVER w AS lag_ignore
+FROM planets
+WINDOW w AS (PARTITION BY distance ORDER BY name ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
+;
+
+--cleanup
+DROP TABLE planets CASCADE;
--
2.43.0
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-01-24 08:27 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-10 08:35 [PATCH 6/8] Add default_toast_compression GUC Dilip Kumar <[email protected]>
2025-01-23 04:25 Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options Tatsuo Ishii <[email protected]>
2025-01-23 06:08 ` Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options Krasiyan Andreev <[email protected]>
2025-01-23 06:27 ` Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options Tatsuo Ishii <[email protected]>
2025-01-24 08:27 ` Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options Oliver Ford <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox