在SELECT子句中使用多个返回集函数的预期行为是什么？

Question

我想要通过两个返回集的函数获得"cross join"，但在某些情况下，我无法得到"cross join"，请参见以下示例

行为1：当集合长度相同时，它会从每个集合中逐个匹配项。

行为2：当集合长度不同时，它会进行"cross join"操作。

我觉得我在这里没有理解到什么，有人可以解释一下预期的行为吗？

另一个更奇怪的例子：

我正在寻找标题中的问题的答案，最好附带链接到相关文档。

- jperelli

---

我的建议是：别这么做。把它们放在“from”子句中。 - Gordon Linoff

我已经在做了，但我正在寻找优化路径（将两个子查询的WHERE合并为一个）。在调查中，我遇到了这个问题。你是说没有预期的行为吗？ - jperelli

Answer 1

Postgres 10或更新版本

对于较小的集合，使用空值填充。通过 generate_series() 演示：

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

以下是翻译的结果：

第二行 | 第三行 | 第四行
-----+------+-----
   1 |   11 |   21
   2 |   12 |   22
null |   13 |   23
null | null |   24

dbfiddle 这里

Postgres 10 的手册:

如果查询的选择列表中有多个返回集函数，则行为类似于将这些函数放入单个 LATERAL ROWS FROM( ... ) FROM-子句项中。对于基础查询结果集中的每一行，首先使用第一个函数返回的结果生成一行输出数据，接着使用第二个函数返回的结果生成另一行输出数据，如此类推。如果某些返回集函数产生的输出结果比其他函数少，则会用 null 值替换缺失的数据，以便对于一个基础行，发出的总行数与产生最多输出结果的返回集函数的行数相同。因此，这些返回集函数“同步执行”，直到它们全部耗尽，然后继续下一行基础行的处理。

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

此重写还更改了一些其他细节。发布说明： 更改查询的SELECT列表中出现的集合返回函数的实现方式，现在在评估标量表达式之前评估集合返回函数，就像将它们放置在一个LATERAL FROM子句项中一样。这允许更合理的语义，适用于存在多个集合返回函数的情况。如果它们返回不同行数，则通过添加nulls将较短的结果扩展到与最长结果匹配。以前的结果会循环直到它们同时终止，产生与函数周期的最小公倍数相等的行数。此外，现在禁止在CASE和COALESCE结构中使用集合返回函数。有关更多信息，请参见第37.4.8节。

结果行数（有点出乎意料！）是相同SELECT列表中所有集合的最小公倍数。（只有当所有集合大小没有公共除数时才像CROSS JOIN！）示例：

SELECT generate_series( 1,  2) AS row2
     , generate_series(11, 13) AS row3
     , generate_series(21, 24) AS 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 这里

在Postgres 9.6手册的SQL Functions Returning Sets章节中有记录，建议避免在选择列表中使用返回集函数：

注意：将多个返回集函数放入同一选择列表中而不是FROM子句的关键问题在于，这样做得到的实际输出行数等于每个返回集函数产生的行数的最小公倍数。调用多个返回集函数时，LATERAL语法会产生更少令人惊讶的结果，并且通常应该使用它。

加粗强调是我的。

单个返回集函数是可以的（但在FROM列表中更清晰），但是现在不鼓励在同一SELECT列表中使用多个。在我们拥有LATERAL连接之前，这是一个有用的功能。现在它只是历史负担。

相关：

• 在PostgreSQL中并行展开()和排序顺序
• 并行展开多个数组
• LATERAL JOIN和子查询在PostgreSQL中的区别是什么？

- Erwin Brandstetter

Answer 2

我找不到任何相关文档。但是，我可以描述我观察到的行为。

每个集合生成函数返回一个有限数量的行。Postgres似乎运行这些集合生成函数，直到它们全部到达最后一行，或者更可能在它们都回到第一行时停止。严格来说，这将是系列长度的最小公倍数（LCM）。

我不确定为什么会这样。正如我在评论中所说，我认为通常最好将这些函数放在from子句中。

- Gordon Linoff

Answer 3

关于此问题，文档中只有一条说明。我不确定这是否解释了所描述的行为。也许更重要的是，这种函数用法已被弃用：

目前，返回集合的函数也可以在查询的选择列表中调用。对于查询本身生成的每一行，都会调用返回集合的函数，并为函数结果集的每个元素生成一个输出行。但请注意，此功能已被弃用，可能会在未来的版本中删除。

- klin