SQL多个UNNEST在单一选择列表。

I was implementing a Query system. I implemented unnest function. Now user was asking about using multiple unnest in a single select statement. I was using PostgreSQL as kind of guideline since most users was using it before our query system.

我实现了一个查询系统。我实现了unnest运算功能。现在用户问的是在一个select语句中使用多个unnest。我使用PostgreSQL作为指导原则，因为大多数用户在查询系统之前使用它。

PostgreSQL has such strange behavior:

PostgreSQL有这样奇怪的行为:

postgres=# select unnest(array[1,2]), unnest(array[1,2]);
 unnest | unnest
--------+--------
      1 |      1
      2 |      2
(2 rows)

postgres=# select unnest(array[1,2]), unnest(array[1,2,3]);
 unnest | unnest
--------+--------
      1 |      1
      2 |      2
      1 |      3
      2 |      1
      1 |      2
      2 |      3
(6 rows)

My implementation was always generate as Cartesian product. I'm wondering, what's the correct logic behind this? Is PostgreSQL doing right thing or just a bug? I didn't find clear description in ANSI document or PostgreSQL document.

我的实现总是生成笛卡尔积。我在想，这背后的逻辑是什么?PostgreSQL做对了还是只是一个错误?我在ANSI文档或PostgreSQL文档中找不到清晰的描述。

1 个解决方案

#1

This isn't about unnest as such, but about PostgreSQL's very weird handling of multiple set-returning functions in the SELECT list. Set-returning functions in SELECT aren't part of the ANSI SQL standard.

这不是关于unnest，而是关于PostgreSQL在选择列表中处理多个集返回函数的非常怪异的处理。SELECT中的set返回函数不是ANSI SQL标准的一部分。

You will find behaviour much saner with LATERAL queries, which should be preferred over using a a set-returning function in FROM as much as possible:

你会发现横向查询的行为要比使用集合返回函数更明智:

select a, b FROM unnest(array[1,2]) a, LATERAL unnest(array[1,2,3]) b;

e.g.

如。

regress=> select a, b FROM unnest(array[1,2]) a, LATERAL unnest(array[1,2,3]) b;
 a | b 
---+---
 1 | 1
 1 | 2
 1 | 3
 2 | 1
 2 | 2
 2 | 3
(6 rows)

The only time I still use multiple set-returning functions in SELECT is when I want to pair up values from functions that both return the same number of rows. The need for that will go away in 9.4, with multi-argument unnest and with support for WITH ORDINALITY.

在SELECT中，我唯一一次仍然使用多个返回集的函数，是当我想对返回相同行数的函数的值进行配对时。对于它的需求将在9.4中消失，多参数不嵌套并支持序数。

#1