sql – select子句中多个set-returns函数的预期行为是什么？

我正在尝试使用两个set-returns函数的结果进行“交叉连接”,但在某些情况下我没有得到“交叉连接”,请参阅示例

行为1：当设置的长度相同时,它会逐个匹配每个集合中的项目

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)

行为2：当设置的长度不同时,它会“交叉连接”这些集合

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

我想我在这里不了解一些事情,有人可以解释一下这个被驱逐的行为吗？

编辑：另一个例子,比以前更怪异

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)

(注意：我会接受回答标题问题的答案以及文档链接.)

解决方法

为较小的集添加空值.演示：

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

dbfiddle here

The manual for Postgres 10：

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.

这结束了传统上奇怪的行为.

Postgres 9.6或更高版本

结果行的数量(有点令人惊讶！)是同一SELECT列表中所有集合的最低公倍数. (如果所有设置大小都没有公约数,则只有CROSS JOIN！)演示：

SELECT generate_series( 1,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

dbfiddle here

记录于manual for Postgres 9.6 the chapter SQL Functions Returning Sets,以及避免它的建议：

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.

大胆强调我的.

单个设置返回功能正常(但在FROM列表中仍然更清晰),但现在不鼓励在同一个SELECT列表中使用多个.在我们进行LATERAL连接之前,这是一个有用的功能.现在它只是历史上的压载物.

有关：

> Parallel unnest() and sort order in PostgreSQL
> Unnest multiple arrays in parallel
> What is the difference between LATERAL and a subquery in PostgreSQL?

（编辑：徐州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!