Why Do Multiple Set-Returning Functions in a SELECT Clause Not Always Produce a Cross Join in PostgreSQL?

PostgreSQL's Unexpected Behavior with Multiple Set-Returning Functions in SELECT Statements

The Problem:

Using multiple set-returning functions within a SELECT statement's clause can yield unexpected results, particularly when the sets have unequal lengths. While generate_series(1, 3) and generate_series(5, 7) produce a cross join, generate_series(1, 2) and generate_series(1, 4) do not. This inconsistency is puzzling.

The Explanation:

The key lies in PostgreSQL version differences. PostgreSQL 10 and later versions handle this differently than earlier versions (9.6 and prior).

PostgreSQL 10 and Later:

PostgreSQL 10 and subsequent releases treat multiple set-returning functions in the SELECT list similarly to a LATERAL ROWS FROM(...) clause. The functions execute synchronously, and the shorter sets are padded with NULL values to match the longest set's length. This ensures a complete cross join. For example:

<code>row2 | row3 | row4
-----+------+-----
1 | 11 | 21
2 | 12 | 22
NULL | 13 | 23
NULL | NULL | 24</code>

PostgreSQL 9.6 and Earlier:

In older versions (9.6 and before), the result set's row count equaled the least common multiple (LCM) of the individual function's row counts. A cross join only occurred if the set sizes shared no common divisors. Using the same example, the output would be:

<code>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</code>

Best Practices:

To avoid unexpected results, it's best to use LATERAL joins or subqueries instead of directly placing multiple set-returning functions in the SELECT list. This provides clearer control and predictable behavior.

For further details and related information, consult the official PostgreSQL documentation:

• PostgreSQL 10 Documentation: Set-returning Functions in SELECT Lists
• PostgreSQL 9.6 Documentation: SQL Functions Returning Sets

The above is the detailed content of Why Do Multiple Set-Returning Functions in a SELECT Clause Not Always Produce a Cross Join in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!