jperelli jperelli - 1 year ago 68
SQL Question

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

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?

EDIT: another example, weirder than previous

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)

(NOTE: I'll accept the answer that answers title's question with link to documentation.)

Answer Source

It's documented in the chapter SQL Functions Returning Sets, along with the recommendation to avoid it:

Note: The key problem with using set-returning functions in the select list, rather than the FROM clause, is that putting more than one set-returning function in the same select list does not behave very sensibly. (What you actually get if you do so is a number of output rows equal to the least common multiple of the numbers of rows produced by each set-returning function.) The LATERAL syntax produces less surprising results when calling multiple set-returning functions, and should usually be used instead.

Bold emphasis mine.

A single set-returning function is OK (but still cleaner in the FROM list), but multiple in the same SELECT list is discouraged now. This was a useful feature before we had LATERAL joins. Now it's merely historical ballast.


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download