Oracle UNION 和 ORDER BY 的奇怪问题

2021-12-30 00:00:00 sql oracle11g oracle sql-order-by union-all

以下查询在几乎所有数据库中完全有效(给出或采用dual 虚拟表)，包括 Oracle:

The following query is perfectly valid in pretty much every database (give or take a dual dummy table), including Oracle:

select 'A' as x from dual union all
select 'B'      from dual
order by x asc

返回:

| X |
|---|
| A |
| B |

现在这个查询仍然是相当标准的 SQL，但是 在 Oracle 上不起作用

Now this query is still quite standard SQL, but doesn't work on Oracle

select 'A' as x from dual union all
select 'B'      from dual union all
select 'C'      from dual
order by x asc

我要了

ORA-00904: "X": invalid identifier

然而，这有效:

select 'A' as x from dual union all
select 'B' as x from dual union all
select 'C'      from dual
order by x asc

我一直在解决这个问题并发现显然至少第一个子选择和第二个最后 (??) 子选择需要有一个名为 x 的列.在第一个示例中，两个子选择似乎只是重合.工作示例:

I've been playing around with this issue and figured out that apparently, at least the first subselect and the second-last (??) subselect need to have a column called x. In the first example, the two subselects seemed to simply coincide. Working example:

select 'A' as x from dual union all
select 'B'      from dual union all
select 'C'      from dual union all
select 'D'      from dual union all
select 'E'      from dual union all
select 'F' as x from dual union all
select 'G'      from dual
order by x asc

您可能已经猜到了，这行不通:

As you may have guessed, this wouldn't work:

select 'A' as x from dual union all
select 'B'      from dual union all
select 'C'      from dual union all
select 'D'      from dual union all
select 'E' as x from dual union all
select 'F'      from dual union all
select 'G'      from dual
order by x asc

有趣的旁注:

派生表似乎不受此限制.这个有效:

select * from (
  select 'A' as x from dual union all
  select 'B'      from dual union all
  select 'C'      from dual
)
order by x asc

问题:

这是 Oracle SQL 解析器中的(已知的?)错误，还是语言语法中有任何非常微妙的细节，绝对需要第一个和倒数第二个子选择来保存从ORDER BY 子句?

推荐答案

这并没有真正回答问题，但它似乎是解析器错误(或功能")而不是语言要求.

This doesn't really answer the question, but it seems to be a parser bug (or 'feature') rather than a language requirement.

根据 My Oracle Support，这似乎是作为错误 14196463 提出的，但没有解决就关闭了.社区主题 3561546 中也提到了这一点.您需要一个 MOS 帐户，或者至少是一个 Oracle 帐户，才能查看其中任何一个.

According to My Oracle Support, this seems to have been raised as bug 14196463 but closed with no resolution. It's also mentioned in community thread 3561546. You need a MOS account, or at least an Oracle account, to see either of those though.

还讨论了在 OTN 线程中，这需要基本的 Oracle 登录而不是 MOS帐户，据我所知.这也没有太多信息，但重复了您的发现，并且还表明该行为至少可以追溯到 9.2.0.8 甚至更早.

It's also been discussed in an OTN thread which requires a basic Oracle login rather than a MOS account, as far as I can tell. That also doesn't have much information but repeats your findings, and also suggests the behaviour has existed back at least to 9.2.0.8 and perhaps much earlier.

文档有点含糊但并不表示这预计会出现问题:

The documentation is a bit vague but doesn't indicate this is expected to be a problem:

对于包含集合运算符 UNION、INTERSECT、MINUS 或 UNION ALL 的复合查询，ORDER BY 子句必须指定位置或别名而不是显式表达式.此外，ORDER BY 子句只能出现在最后一个组件查询中.ORDER BY 子句对整个复合查询返回的所有行进行排序.

For compound queries containing set operators UNION, INTERSECT, MINUS, or UNION ALL, the ORDER BY clause must specify positions or aliases rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query.

您为表达式添加别名并使用它，并没有说您必须为特定组件添加别名(当然，它也没有说您不必要么).

You are aliasing your expression and using that, and it doesn't say you have to alias particular components (although of course it doesn't say you don't have to either).

这种行为似乎与对最终投影有效的别名不一致，并且关于别名的通常规则仅在 order by 子句中有效 - 这似乎介于两者之间.

The behaviour seems inconsistent with the alias being valid for the final projection, and the usual rule about the alias only being valid in the order by clause - this seems to be falling down somewhere in between.

相关文章