What is the expected behaviour for multiple set-returning functions in SELECT clause?

Question

I'm trying to get a "cross join" with the result of two set-returning functions, but in some cases I don't get the "cross join", see example

Behaviour 1 : When set lenghts are the same, it matches item by item from each set

postgres=# SELECT generate_series(1,3), generate_series(5,7) order by 1,2;
 generate_series | generate_series 
-----------------+-----------------
               1 |               5
               2 |               6
               3 |               7
(3 rows)

Behaviour 2 : When set lenghts are different, it "cross join"s the sets

postgres=# SELECT generate_series(1,2), generate_series(5,7) order by 1,2;
 generate_series | generate_series 
-----------------+-----------------
               1 |               5
               1 |               6
               1 |               7
               2 |               5
               2 |               6
               2 |               7
(6 rows)

I think I'm not understanding something here, can someone explain the expeted behaviour?

Another example, even weirder:

postgres=# SELECT generate_series(1,2) x, generate_series(1,4) y order by x,y;
 x | y 
---+---
 1 | 1
 1 | 3
 2 | 2
 2 | 4
(4 rows)

I am looking for an answer to the question in the title, ideally with link(s) to documentation.

Answer

Postgres 10 or newer

pads with null values for smaller set(s). Demo with [generate_series()](https://www.postgresql.org/docs/current/functions- srf.html):

SELECT generate_series( 1,  2) AS row2
     , generate_series(11, 13) AS row3
     , generate_series(21, 24) AS row4;



row2 | row3 | row4
-----+------+-----
   1 |   11 |   21
   2 |   12 |   22
_null_ |   13 |   23
_null_ | _null_ |   24

dbfiddlehere

[The manual for Postgres 10](https://www.postgresql.org/docs/10/xfunc- sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET):

If there is more than one set-returning function in the query's select list, the behavior is similar to what you get from putting the functions into a single LATERAL ROWS FROM( ... ) FROM-clause item. For each row from the underlying query, there is an output row using the first result from each function, then an output row using the second result, and so on. If some of the set-returning functions produce fewer outputs than others, null values are substituted for the missing data, so that the total number of rows emitted for one underlying row is the same as for the set-returning function that produced the most outputs. Thus the set-returning functions run “in lockstep” until they are all exhausted, and then execution continues with the next underlying row.

This ends the traditionally odd behavior.

Some other details changed with this rewrite. 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 withinCASE and COALESCE constructs. For more information see [Section 37.4.8](https://www.postgresql.org/docs/10/xfunc-sql.html#XFUNC-SQL- FUNCTIONS-RETURNING-SET).

Bold emphasis mine.

Postgres 9.6 or older

The number of result rows (somewhat surprisingly!) is the lowest common multiple of all sets in the same SELECT list. (Only acts like a CROSS JOIN if there is no common divisor to all set-sizes!) Demo:

SELECT generate_series( 1,  2) AS row2
     , generate_series(11, 13) AS row3
     , generate_series(21, 24) AS row4;



row2 | row3 | row4
-----+------+-----
   1 |   11 |   21
   2 |   12 |   22
   1 |   13 |   23
   2 |   11 |   24
   1 |   12 |   21
   2 |   13 |   22
   1 |   11 |   23
   2 |   12 |   24
   1 |   13 |   21
   2 |   11 |   22
   1 |   12 |   23
   2 |   13 |   24