From d8411a6c8b6e5f74b362ef2496723f7f88593737 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 26 Jul 2016 21:33:49 -0400 Subject: Allow functions that return sets of tuples to return simple NULLs. ExecMakeTableFunctionResult(), which is used in SELECT FROM function(...) cases, formerly treated a simple NULL output from a function that both returnsSet and returnsTuple as a violation of the SRF protocol. What seems better is to treat a NULL output as equivalent to ROW(NULL,NULL,...). Without this, cases such as SELECT FROM unnest(...) on an array of composite are vulnerable to unexpected and not-very-helpful failures. Old code comments here suggested an alternative of just ignoring simple-NULL outputs, but that doesn't seem very principled. This change had been hung up for a long time due to uncertainty about how much we wanted to buy into the equivalence of simple NULL and ROW(NULL,NULL,...). I think that's been mostly resolved by the discussion around bug #14235, so let's go ahead and do it. Per bug #7808 from Joe Van Dyk. Although this is a pretty old report, fixing it smells a bit more like a new feature than a bug fix, and the lack of other similar complaints suggests that we shouldn't take much risk of destabilization by back-patching. (Maybe that could be revisited once this patch has withstood some field usage.) Andrew Gierth and Tom Lane Report: --- src/test/regress/expected/rangefuncs.out | 30 ++++++++++++++++++++++++++++++ src/test/regress/sql/rangefuncs.sql | 11 +++++++++++ 2 files changed, 41 insertions(+) (limited to 'src/test') diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out index 00ef4210549..f06cfa4b21d 100644 --- a/src/test/regress/expected/rangefuncs.out +++ b/src/test/regress/expected/rangefuncs.out @@ -2076,3 +2076,33 @@ select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x); -4567890123456789 (5 rows) +-- check handling of nulls in SRF results (bug #7808) +create type foo2 as (a integer, b text); +select *, row_to_json(u) from unnest(array[(1,'foo')::foo2, null::foo2]) u; + a | b | row_to_json +---+-----+--------------------- + 1 | foo | {"a":1,"b":"foo"} + | | {"a":null,"b":null} +(2 rows) + +select *, row_to_json(u) from unnest(array[null::foo2, null::foo2]) u; + a | b | row_to_json +---+---+--------------------- + | | {"a":null,"b":null} + | | {"a":null,"b":null} +(2 rows) + +select *, row_to_json(u) from unnest(array[null::foo2, (1,'foo')::foo2, null::foo2]) u; + a | b | row_to_json +---+-----+--------------------- + | | {"a":null,"b":null} + 1 | foo | {"a":1,"b":"foo"} + | | {"a":null,"b":null} +(3 rows) + +select *, row_to_json(u) from unnest(array[]::foo2[]) u; + a | b | row_to_json +---+---+------------- +(0 rows) + +drop type foo2; diff --git a/src/test/regress/sql/rangefuncs.sql b/src/test/regress/sql/rangefuncs.sql index 9484023f97b..c8edc553bc3 100644 --- a/src/test/regress/sql/rangefuncs.sql +++ b/src/test/regress/sql/rangefuncs.sql @@ -639,3 +639,14 @@ explain (verbose, costs off) select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x); select x from int8_tbl, extractq2_2_opt(int8_tbl) f(x); + +-- check handling of nulls in SRF results (bug #7808) + +create type foo2 as (a integer, b text); + +select *, row_to_json(u) from unnest(array[(1,'foo')::foo2, null::foo2]) u; +select *, row_to_json(u) from unnest(array[null::foo2, null::foo2]) u; +select *, row_to_json(u) from unnest(array[null::foo2, (1,'foo')::foo2, null::foo2]) u; +select *, row_to_json(u) from unnest(array[]::foo2[]) u; + +drop type foo2; -- cgit v1.2.3