Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to force Postgresql "cartesian product" behavior when unnest'ing multiple arrays in select?

Postgresql behaves strangely when unnesting multiple arrays in the select list:

select unnest('{1,2}'::int[]), unnest('{3,4}'::int[]);
 unnest | unnest
--------+--------
      1 |      3
      2 |      4

vs when arrays are of different lengths:

select unnest('{1,2}'::int[]), unnest('{3,4,5}'::int[]);
 unnest | unnest
--------+--------
      1 |      3
      2 |      4
      1 |      5
      2 |      3
      1 |      4
      2 |      5

Is there any way to force the latter behaviour without moving stuff to the from clause?

The SQL is generated by a mapping layer and it will be very much easier for me to implement the new feature I am adding if I can keep everything in the select.

like image 273
David Tinker Avatar asked Oct 17 '25 16:10

David Tinker


1 Answers

https://www.postgresql.org/docs/10/static/release-10.html

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.

(emphasis mine)

like image 108
Vao Tsun Avatar answered Oct 20 '25 06:10

Vao Tsun



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!