Thread: select unnest(), unnest()
Hi, we have some queries that use unnest() function.
create table configuration (
id int,
advisor text,
branch text,
primary key(id)
);
insert into configuration values
(1,'a','00,01'),
(2,'a','00'),
(3,'b','02,03'),
(4,'c','05'),
(5,'d,e','00,01');
select id, unnest(string_to_array(advisor,',')), unnest(string_to_array(branch,','))
from configuration;
PG9.6:
id | unnest | unnest
----+--------+--------
1 | a | 00
1 | a | 01
2 | a | 00
3 | b | 02
3 | b | 03
4 | c | 05
5 | d | 00
5 | e | 01
PG10:
id | unnest | unnest
----+--------+--------
1 | a | 00
1 | | 01
2 | a | 00
3 | b | 02
3 | | 03
4 | c | 05
5 | d | 00
5 | e | 01
- Is this wanted in PG10 or a bug?
- Is there a way to change the behaviour of pgsql to produce output like 9.6?
- A smooth sql-workaround?
Thank you very much!
Regards, Jasmin
Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com> writes:
> select id, unnest(string_to_array(advisor,',')), unnest
> (string_to_array(branch,','))
> from configuration;
Yes, the behavior for cases like this changed in PG 10. Read the
release notes:
* Change the implementation of set-returning functions appearing in a
query's SELECT list (Andres Freund)
Set-returning functions are now evaluated before evaluation of scalar
expressions in the SELECT list, much as though they had been placed in
a LATERAL FROM-clause item. This allows saner semantics for cases
where multiple set-returning functions are present. If they return
different numbers of rows, the shorter results are extended to match
the longest result by adding nulls. Previously the results were cycled
until they all terminated at the same time, producing a number of rows
equal to the least common multiple of the functions' periods. In
addition, set-returning functions are now disallowed within CASE and
COALESCE constructs. For more information see Section 37.4.8.
> - Is there a way to change the behaviour of pgsql to produce output like
> 9.6?
> - A smooth sql-workaround?
LATERAL would probably help you; see the examples in 37.4.8.
https://www.postgresql.org/docs/10/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET
regards, tom lane
Thank you, Tom. Lateral works.
happy easter!
2018-04-01 21:49 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com> writes:
> select id, unnest(string_to_array(advisor,',')), unnest
> (string_to_array(branch,','))
> from configuration;
Yes, the behavior for cases like this changed in PG 10. Read the
release notes:
* Change the implementation of set-returning functions appearing in a
query's SELECT list (Andres Freund)
Set-returning functions are now evaluated before evaluation of scalar
expressions in the SELECT list, much as though they had been placed in
a LATERAL FROM-clause item. This allows saner semantics for cases
where multiple set-returning functions are present. If they return
different numbers of rows, the shorter results are extended to match
the longest result by adding nulls. Previously the results were cycled
until they all terminated at the same time, producing a number of rows
equal to the least common multiple of the functions' periods. In
addition, set-returning functions are now disallowed within CASE and
COALESCE constructs. For more information see Section 37.4.8.
> - Is there a way to change the behaviour of pgsql to produce output like
> 9.6?
> - A smooth sql-workaround?
LATERAL would probably help you; see the examples in 37.4.8.
https://www.postgresql.org/docs/10/static/xfunc-sql.html# XFUNC-SQL-FUNCTIONS-RETURNING- SET
regards, tom lane